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.