27

I have a CSV file which I am directly importing to a SQL server table. In the CSV file each column is separated by a comma. But my problem is that I have a column "address", and the data in this column contains commas. So what is happening is that some of the data of the address column is going to the other columns will importing to SQL server.

What should I do?

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
Sidharth
  • 1,251
  • 1
  • 25
  • 40

8 Answers8

65

For this problem the solution is very simple. first select => flat file source => browse your file => then go to the "Text qualifier" by default its none write here double quote like (") and follow the instruction of wizard.

Steps are - first select => flat file source => browse your file => Text qualifier (write only ") and follow the instruction of wizard.

Good Luck

Ajeet Malviya
  • 784
  • 1
  • 5
  • 9
  • 2
    This is the correct response. The previous answer may work, but it involves editing the source data. That does not always work. – Timothy Dooling Mar 13 '17 at 18:33
  • 4
    This is the perfect answer. Sometimes you have no control on how the CSV is formatted. Not sure why the one marked as the answer has been marked so. – Tshsmith Jun 22 '17 at 08:50
  • If you have spaces between your csv entries, all Microsoft products (excel, sql server) fail badly. You need to remove the space first. – Neil Walker Aug 23 '19 at 12:36
  • Woah! Simplest approach. – Anish K May 09 '20 at 20:06
20

If there is a comma in a column then that column should be surrounded by a single quote or double quote. Then if inside that column there is a single or double quote it should have an escape charter before it, usually a \

Example format of CSV

ID - address - name
1, "Some Address, Some Street, 10452", 'David O\'Brian'
Scott
  • 3,967
  • 9
  • 38
  • 56
  • 1
    +1 this is the right way to do it. But "CSV" is so informal that people do all sorts of crazy stuff. – Gabriel Magana Nov 08 '10 at 12:55
  • @gmagana - agreed, I remeber when I first used it, had allsorts of headaches untill I understood the 3 main things. 1) you need a field terminator, usually a comma, 2) you need a field enclosed by usually a double quote, Best to enclose all fields in double quotes unless dealing with integers and 3) you need an escape caracter incase you use a field encloser inside a field, usually a \. Format your csv like this and you life will be so much easier :) – Scott Nov 08 '10 at 13:07
  • How can i check this when i am directly importing CSV to SQL Server. – Sidharth Nov 09 '10 at 05:19
  • @Sidharth - You can check how your CSV file is formatted by opening it in a basic text editor such as notepad. Check that your data in the CSV is formatted correctly. Then go ahead and import your CSV to your database. Dont know how you are importing and what tool you are using so cant help further there. But if you need to convert your CSV to SQL insert statements then you can use this http://csv2sql.com/ – Scott Nov 09 '10 at 09:51
  • I am trying to bulk insert the CSV to SQL server like this – Sidharth Nov 10 '10 at 05:19
  • BULK INSERT CSVTest FROM 'c:\csvtest.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO – Sidharth Nov 10 '10 at 05:21
  • I tried to put double quote around the column but csv is still messed up... – aj go Apr 08 '21 at 10:00
4

New version supports the CSV format fully, including mixed use of " and , .

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT='CSV');
Nilsson Hedge
  • 41
  • 1
1

The best, quickest and easiest way to resolve the comma in data issue is to use Excel to save a comma separated file after having set Windows' list separator setting to something other than a comma (such as a pipe). This will then generate a pipe (or whatever) separated file for you that you can then import. This is described here.

Sachin Kainth
  • 45,256
  • 81
  • 201
  • 304
1

I'd suggest to either use another format than CSV or try using other characters as field separator and/or text delimiter. Try looking for a character that isn't used in your data, e.g. |, #, ^ or @. The format of a single row would become

|foo|,|bar|,|baz, qux|

A well behave parser must not interpret 'baz' and 'qux' as two columns.

Alternatively, you could write your own import voodoo that fixes any problems. For the later, you might find this Groovy skeleton useful (not sure what languages you're fluent in though)

sfussenegger
  • 35,575
  • 15
  • 95
  • 119
1

Most systems, including Excel, will allow for the column data to be enclosed in single quotes...

col1,col2,col3 'test1','my test2, with comma',test3

Another alternative is to use the Macintosh version of CSV, which uses TAB's as delimiters.

RichO
  • 723
  • 4
  • 7
0

I don't think adding quote could help.The best way I suggest is replacing the comma in the content with other marks like space or something.

replace(COLUMN,',',' ') as COLUMN
hajimuz
  • 368
  • 3
  • 14
0

Appending a speech mark into the select column on both side works. You must also cast the column as a NVARCVHAR(MAX) to turn this into a string if the column is a TEXT.

SQLCMD -S DB-SERVER -E -Q "set nocount on; set ansi_warnings off; SELECT '""' + cast ([Column1] as nvarchar(max)) + '""' As TextHere, [Column2] As NormalColumn FROM [Database].[dbo].[Table]" /o output.tmp /s "," -W
mashtheweb
  • 167
  • 9