February 6, 2013 at 12:24 am
I must be overlooking something really simple, but I am stuck. keep getting the error:
Error = [Microsoft][SQL Server Native Client 11.0]BCP host-files must contain at least one column
Here is my bcp:
bcp mydatabase.dbo.stg_assess_value in AVALUE.TXT -S myserver -U myusername -P mypassword -f Assess_Value.fmt
Format file: Assess_Value.fmt
7.0
2
1SQLCHAR04","1tax_year
2SQLCHAR05"\r"2fips_code
Input file: AVALUE.TXT
2013,22067
2013,22068
2013,22069
2013,22060
2013,22061
SQL Server table: mydatabase.dbo.stg_assess_value
CREATE TABLE [dbo].[stg_assess_value](
[tax_year] [decimal](4, 0) NOT NULL,
[fips_code] [decimal](5, 0) NOT NULL
)
I am using SQL Server 2012, but I am not doing anything version specific.
Any help would be appreciated. Thanks.
February 6, 2013 at 1:28 am
At a guess, unless you are terminating rows with just a carriage return instead of a carriage return and line feed, the last line of your format file should read
2SQLCHAR 05"/r/n"2fips_code
Note... the forward slashes should really be backward slashes, but they keep getting translated to something else, so I presume I need to escape them somehow in my reply, but don't know how.
February 6, 2013 at 9:14 am
Ian, thanks for the reply.
I tried \r on the last line of the format file. I get the same error message. 🙁
February 6, 2013 at 9:40 am
i see two issues:
first, teh data is so simple, i don't see any need to use a format file; i only use format files if it's delimited with quotes or something.
second, there's no path information for the files, so unless you actually placed them in the same folder that bcp.exe exists in, i'd expect it to fail.
bcp mydatabase.dbo.stg_assess_value in c:\Data\AVALUE.TXT -S myserver -U myusername -P mypassword -f c:\Data\Assess_Value.fmt
i would just switch to bulk insert for this one; i think it would be faster, and it's more TSQL-ish to me anyway.:
BULK INSERT stg_assess_value FROM 'c:\Data\AVALUE.TXT'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 1
)
Lowell
February 6, 2013 at 5:54 pm
Lowell, thanks for the reply.
I actually simplified the data for the posting here. I actually have about 20 columns of data and some with quotes. So, I decided to use cbp with a format file.
I thought this would be simple and someone would have pointed out something that I missed...
Driving me crazy...
February 6, 2013 at 10:27 pm
I figured it out...
For SQL 2012:
1. The format file needed to start with 11.0.
2. I removed any tabs between the columns in the format file (not sure if this was necessary)
3. I had to add quotes at the end of each line in the format file
resulting format file looked like this:
11.0
2
1SQLCHAR 0 4 "," 1 tax_year ""
2 SQLCHAR 0 5 "\r" 2 fips_code ""
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply