Solved

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

Posted on 2012-04-07
7
2,790 Views
Last Modified: 2012-06-27
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
Comment
Question by:qprjohn121
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 37818812
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
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37818816
0
 
LVL 17

Accepted Solution

by:
Anuroopsundd earned 500 total points
ID: 37818820
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:qprjohn121
ID: 37818823
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37819416
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
 

Author Comment

by:qprjohn121
ID: 37819499
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
 

Author Closing Comment

by:qprjohn121
ID: 37819509
This didnt answer all points of my question but identified for me the need to use Format Files.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question