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.

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SQL Server