SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP comma delimited file into a table


BCP comma delimited file into a table

Author
Message
dv_peeking
dv_peeking
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 15
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
1 SQLCHAR 0 4 "," 1 tax_year
2 SQLCHAR 0 5 "\r" 2 fips_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.
Ian Scarlett
Ian Scarlett
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5170 Visits: 7136
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

2   SQLCHAR    0   5   "/r/n"   2   fips_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.



dv_peeking
dv_peeking
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 15
Ian, thanks for the reply.

I tried \r on the last line of the format file. I get the same error message. Sad
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70186 Visits: 40923
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
dv_peeking
dv_peeking
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 15
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...
dv_peeking
dv_peeking
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 15
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
1 SQLCHAR 0 4 "," 1 tax_year ""
2 SQLCHAR 0 5 "\r" 2 fips_code ""
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search