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

Posted on 2012-09-18
Last Modified: 2012-10-03
Using the bcp utility, I need to import a csv file into my database table in MS SQL Server 2008.  

My table:

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:

My format file
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,


Cooper IS
Question by:CoopIS
    LVL 83

    Expert Comment

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

    Author Comment

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

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

    Author Comment

    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.
    LVL 83

    Assisted Solution

    LVL 21

    Accepted Solution

    Legth of data is wider than the database field size. Increase the field size of just trucate the value manually and import using BCP.

    Author Comment

    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

    Author Comment

    Thanks for the help.  

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

    Cooper IS

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now