• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1301
  • Last Modified:

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
0
CoopIS
Asked:
CoopIS
  • 4
  • 2
2 Solutions
 
CodeCruiserCommented:
You are trying to import CSV file to SQL Server. Is VB.NET involved at any stage?
0
 
CoopISAuthor Commented:
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?
0
 
CoopISAuthor Commented:
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.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Alpesh PatelAssistant ConsultantCommented:
Legth of data is wider than the database field size. Increase the field size of just trucate the value manually and import using BCP.
0
 
CoopISAuthor Commented:
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
0
 
CoopISAuthor Commented:
Thanks for the help.  

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

Cooper IS
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now