Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3188
  • Last Modified:

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

Hi
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.
0
qprjohn121
Asked:
qprjohn121
1 Solution
 
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.
0
 
AnuroopsunddCommented:
see queries from http://sqlusa.com/bestpractices2005/notepad/
one example from above link.

DECLARE  @DynamicSQL NVARCHAR(512)
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')
*/
EXEC SP_EXECUTESQL   @DynamicSQL
GO
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
0
 
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.
0
 
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'
WITH
(
FIRSTROW=2,
FORMATFILE = 'C:\myFolder\TestImportFormatFile.xml'
)

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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now