SQL BULK INSERT - How to import a CSV file with Commas inside

I have to import a CSV file containing several million rows into SQL Server, in which one specific column contains a comma in some records. This comma is causing the import to split the field value into two pieces and move values into the wrong columns for those records.

When the file is opened in Notepad a record has this example form:

Value1,Value2,Value3,,Value5,Value6,"Flat1, High Street",Value8,,Value10

The problem is caused by the column containing "Flat1, High Street" due to the internal comma. I have no control over the format of the file as this comes from an external operating system and so have to deal with the file as it comes. I cannot import it first into Excel to perform manipulations as the file is several million rows.

Is there a way to perform the BULK INSERT into SQL, or to process the file first with some other tool? If it helps, I can rely on knowing that it is always the same column that may cause problems. Also, the problem values are always surrounded by "" as in the example above.
Who is Participating?
AnuroopsunddConnect With a Mentor Commented:
see queries from http://sqlusa.com/bestpractices2005/notepad/
one example from above link.

DECLARE  @csvFilePath VARCHAR(64),
         @bcpFormatPath VARCHAR(64)
SET @csvFilePath = 'f:\data\feed\inventory20151023.txt'
SET @bcpFormatPath = 'f:\data\format\inventory.fmt'
SET @DynamicSQL = 'BULK INSERT [PartsInventory] FROM ''' + @csvFilePath + ''' WITH (formatfile = ''' + @bcpFormatPath + ''')'
PRINT @DynamicSQL -- test & debug
BULK INSERT [PartsInventory] FROM 'e:\data\inventory20120201.txt'  
WITH (formatfile = 'e:\data\inventory.fmt')
Neil RussellTechnical Development LeadCommented:
You need to remember to set the text qualifier to be a double quote then the csv will import even with the comma inside the quotes.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

qprjohn121Author Commented:
Sorry Neilsr I'm a beginner at this, could you clarify the specifics - here's what I have tried so far:

BULK INSERT dbo.stagingTable
FROM 'C:\Users\user\Desktop\TestImport.CSV'
Anthony PerkinsCommented:
Unfortunately you will not be able to use BULK INSERT to import this file with first editing the file and even then it would be a nightmare.
You need to use some other tool to do this.  I suspect you may find that SSIS will also choke on it if that column does not have text delimiters on all the rows.  If that is the case and you cannot use any third party tool then you will have to resort to rolling your own import routine.
qprjohn121Author Commented:
Ok I have cracked this in principle now. The key learning was to create a Format File which allows you to specify a different terminator for each field, so that fields 1 to 5 in my example use ',' as the terminator, field 6 uses ',"' as the terminator, field 7 uses '",' as the terminator, and the rest use ',' as the terminator apart from the last field which uses '\r\n' to mark the end of the record.

I created an XML Format File called 'TestImportFormatFile.xml' and referenced this file in my SQL import:

BULK INSERT dbo.staging_TestImport
FROM 'C:\myFolder\TestSmallFile.CSV'
FORMATFILE = 'C:\myFolder\TestImportFormatFile.xml'

Hope this helps others.
qprjohn121Author Commented:
This didnt answer all points of my question but identified for me the need to use Format Files.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.