BCP: Remove Quotes from CSV Import using BCP Format File

I’d like to start this post by saying that BCP is limited and ghe-toe, but it gets the job done to some degree. One of the guys over at SQLteam.com wrote a really informative post on Flat File Bulk Import methods speed comparison in SQL Server 2005 that you should check out if you are into that sort of thing. BCP ranks last on the list but I recently decided to use it anyway for an informal import project I worked on.

So what I found was that BCP doesn’t play well with INTs/BIGINTs and that it’s best to just use “SQLCHAR” and let SQL Server do it’s implicit conversions. I also learned that it’s a pain to remove quotes but I managed to do so with some hackery and want to post it here as I’ve seen others needing the same thing in newsgroups. Here are two different samples that I’ve worked with: the bolded fields are from a sample CSV file and the blue boxes are the corresponding bcp.fmt files.

locId,country,region,city,postalCode,latitude,longitude,dmaCode,areaCode
68954,”US”,”LA”,”Kaplan”,”70548″,29.9845,-92.3224,642,337

7.0
9
1       SQLCHAR    0       255       ",""    1     locId
2       SQLCHAR    0       255       "",""    2     country
3       SQLCHAR    0       255       "",""    3     region
4       SQLCHAR    0       255       "",""    4     city
5       SQLCHAR    0       255       "","     5     postalCode
6       SQLCHAR    0       255       ","    6     latitude
7       SQLCHAR    0       255       ","    7     longitude
8       SQLCHAR    0       255       ","    8     dmaCode
9       SQLCHAR    0       255       "\n"   9     areaCode

startIpNum,endIpNum,locId
“33996344″,”33996351″,”16552″

7.0
5
1       SQLCHAR    0       255       """     0     firstQuote
2       SQLCHAR    0       255       "",""     1     startIpNum
3       SQLCHAR    0       255       "",""     2     endIpNum
4       SQLCHAR    0       255       """    3     locId
5       SQLCHAR    0       255       "\n"    0     lastQuote

Then run: bcp dbName.dbo.tableName in mydata.csv -F 2 -S sqlSrvr -T -f bcp.fmt
(-F tells bcp which line to start on, -T is trusted connection, -S is the SQL Server name)

If you are wondering what all the fields are for, this is a really helpful diagram (as found in SQL 7 Books Online)

Again, SQLBIGINT never worked for me under SQL Server 2005 (even when I set the format file version to 9.0) so I’d just recommend using SQLCHAR (varchar equiv) and let SQL Server do the implicit conversion for you.

Posted in SQL Server
6 comments on “BCP: Remove Quotes from CSV Import using BCP Format File
  1. Rochana says:

    Thanks for the hint.
    Your format didn’t work but managed to tweak and get it work.
    Here’s my format file

    9.0
    6
    1 SQLCHAR 0 21 “,\”" 1 RefNo “”
    2 SQLCHAR 0 510 “\”,” 2 Product_Description Latin1_General_CI_AS
    3 SQLCHAR 0 510 “,\”" 3 No Latin1_General_CI_AS
    4 SQLCHAR 0 510 “\”,” 4 Prime_Flag Latin1_General_CI_AS
    5 SQLCHAR 0 510 “,\”" 5 Group_Code Latin1_General_CI_AS
    6 SQLCHAR 0 510 “\”\r\n” 6 Group_Name Latin1_General_CI_AS

  2. Steve P says:

    Great post. Thank you very much

  3. Dean says:

    yes but how can I get the format file to ignore a header record in a csv file? FIRSTROW=2 is not working.

  4. starsky51 says:

    Great post, Chrissy. Really helped me out.
    Dean, it’s been 15 months. You’ve probably worked it out, but you can add ‘-F 2′ to your bcp command line to skip the first row.

  5. Mark says:

    This works really well but for when there might be a source file with an empty field (eg some of the IPInfoDB stuff) – hence it contains sequences of ;;; without empty quoted strings and these do not match the pattern in the format file – bit of a bummer really so I’m going to use Talend ETL instead :(

  6. Sean says:

    Having the same issue I think the BCP file is doing this with fields that have no data , ,
    How can i just have ,,, instead of the , , ,

    Thanks, Sean

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">