asked on

Using BCP utility to import a csv file into MS SQL Server 2008 database table

Using the bcp utility, I need to import a csv file into my database table in MS SQL Server 2008.

My table:

GrowerTable
GrowNo varchar(10)
GrowLName varchar(15)
GrowFName varchar(15)
GrowMi varchar(5)
GrowProjectType varchar(5)
GrowNoMaster varchar(10)
GrowEmailAddress varchar(40)

I created a csv file from an excell spread sheet. GrowerMi and email can be null (which they are in most records)

Table data:
000025,ACHESON,MIKE,,8,000000,macheson@dishmail.net
004804,HEITKAMP,GARY,,8,000000,garyheitkamp@frontier.com
004802,HISER,BRIAN,A,3,000000,

My format file
10.0
7
1 SQLCHAR 0 10 "," 1 GrowNo SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 15 "," 2 GrowLname SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 15 "," 3 GrowFname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 5 "," 4 GrowMi SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 5 "," 5 GrowProjectType SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 10 "," 6 GrowNoMaster SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 40 "\r\n" 7 GrowEmailAddress SQL_Latin1_General_CP1_CI_AS

I used the bcp utility to create the format file. But it created a tab delimited file, which I changed to comma delimited

The command I use:
bcp cooperdatabase.growertable in P:\ASP\grower.csv -S .\SQLEXPRESS -T -f \P:\ASP\growerformat.fmt

I get an error message that says:
String data right truncation
Unexpected EOF

I've tried to use a tab delimited file as well, but I get the same errors.

My questions are:
What changes to my files do I need to make in order for the bcp utility to work?
How do I use the " text qualifier?
I tried to change "," to "","" but the bcp utility gave me an error saying there was something wrong with the format file.

Any help is appreciated,

Thanks,

Cooper IS

Nasir Razzaq

You are trying to import CSV file to SQL Server. Is VB.NET involved at any stage?

CoopIS

ASKER

The question refers to SQL Server, but the website I'm using this on is codded in vb.net.

Sorry for the confusion. Is there a way I can change the topics for my post to make it more clear?

CoopIS

ASKER

More info:

Sometimes GrowerLastname contains extra comma's in the field. Excell adds double quotes to these fields when converting the file to a csv format. I've tried a few ways to add the text qualifer to my format file, but with no success. I'm not sure how to add " to the delimitor field.

SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CoopIS

ASKER

I used the SqlBulkCopy class to initially import all of my test data.

Now I want to use the bcp utility so I can schedule a task to run a dos script that updates the table.

After adjusting the field sizes I no longer received the truncate errors, but I still need a way to add a text qualifer to my format file.

Thanks for all the help

CoopIS

ASKER

Thanks for the help.

We decided to use the SqlBulkCopy class instead of the bcp utility, to upload files.

Cooper IS