sql server Bulk insert csv with data having comma

Question

below is the sample line of csv

012,12/11/2013,"<555523051548>KRISHNA  KUMAR  ASHOKU,AR",<10-12-2013>,555523051548,12/11/2013,"13,012.55",

you can see KRISHNA KUMAR ASHOKU,AR as single field but it is treating KRISHNA KUMAR ASHOKU and AR as two different fields because of comma, though they are enclosed with " but still no luck

I tried

BULK
INSERT tbl
FROM 'd:\1.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW=2
)
GO

is there any solution for it?

This is an older question, but the answer to this newer question worked for me. CSV imported successfully in SQL 2019 using `format='csv',` instead of specifying `FIELDTERMINATOR and ROWTERMINATOR` https://stackoverflow.com/questions/55611219/how-to-specify-text-qualified-comma-delimiter-in-bulk-insert-format-file-for-col — Scott, May 02 '23 at 06:48

HelpfulH4cker · Answer 1 · 2019-12-04T04:03:19.950

They added support for this SQL Server 2017 (14.x) CTP 1.1. You need to use the FORMAT = 'CSV' Input File Option for the BULK INSERT command.

To be clear, here is what the csv looks like that was giving me problems, the first line is easy to parse, the second line contains the curve ball since there is a comma inside the quoted field:

jenkins-2019-09-25_cve-2019-10401,CVE-2019-10401,4,Jenkins Advisory 2019-09-25: CVE-2019-10401: 
jenkins-2019-09-25_cve-2019-10403_cve-2019-10404,"CVE-2019-10404,CVE-2019-10403",4,Jenkins Advisory 2019-09-25: CVE-2019-10403: CVE-2019-10404:

Broken Code

BULK INSERT temp
    FROM 'c:\test.csv'
    WITH
    (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '0x0a',
        FIRSTROW= 2
    );

Working Code

BULK INSERT temp
    FROM 'c:\test.csv'
    WITH
    (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '0x0a',
        FORMAT = 'CSV',
        FIRSTROW= 2
    );

david.pfx · Accepted Answer · 2014-01-21T01:26:26.390

11

The answer is: you can't do that. See http://technet.microsoft.com/en-us/library/ms188365.aspx.

"Importing Data from a CSV file

Comma-separated value (CSV) files are not supported by SQL Server bulk-import operations. However, in some cases, a CSV file can be used as the data file for a bulk import of data into SQL Server. For information about the requirements for importing data from a CSV data file, see Prepare Data for Bulk Export or Import (SQL Server)."

The general solution is that you must convert your CSV file into one that can be be successfully imported. You can do that in many ways, such as by creating the file with a different delimiter (such as TAB) or by importing your table using a tool that understands CSV files (such as Excel or many scripting languages) and exporting it with a unique delimiter (such as TAB), from which you can then BULK INSERT.

edited Jan 21 '14 at 01:26

answered Jan 20 '14 at 04:34

david.pfx

10,520
3
30
63

1

Please provide an alternate solution or remove your answer and leave a comment. – Kermit Jan 20 '14 at 22:40
6

In my opinion the answer given is correct. The SQL command cannot be modified to achieve the required result. The CSV file must pre-processed before being uploaded. The choice of specific tool or technique for modifying the file is outside the scope of the question. I have edited the answer slightly to make that clearer. – david.pfx Jan 21 '14 at 01:23

z atef · Answer 3 · 2014-01-20T04:39:31.923

5

Unfortunately , SQL Server Import methods( BCP && BULK INSERT) do not understand quoting " "

Source : http://msdn.microsoft.com/en-us/library/ms191485%28v=sql.100%29.aspx

edited Jan 20 '14 at 04:39

answered Jan 20 '14 at 04:33

z atef

7,138
3
55
50

score 4 · Answer 4 · answered Jul 23 '15 at 14:32

I have encountered this problem recently and had to switch to tab-delimited format. If you do that and use the SQL Server Management Studio to do the import (Right-click on database, then select Tasks, then Import) tab-delimited works just fine. The bulk insert option with tab-delimited should also work.

I must admit to being very surprised when finding out that Microsoft SQL Server had this comma-delimited issue. The CSV file format is a very old one, so finding out that this was an issue with a modern database was very disappointing.

You mean the most highly-upvoted answer? None of the answers have been accepted. — Tab Alleman, Jul 23 '15 at 17:58

Geoff Griswald · Answer 5 · 2019-08-12T13:12:28.213

4

MS have now addressed this issue and you can use FIELDQUOTE in your with clause to add quoted string support:

FIELDQUOTE = '"',

anywhere in your with clause should do the trick, if you have SQL Server 2017 or above.

edited Aug 12 '19 at 13:12

answered Aug 12 '19 at 13:05

Geoff Griswald

937
12
24

score -1 · Answer 6 · answered Oct 10 '17 at 00:59

Well, Bulk Insert is very fast but not very flexible. Can you load the data into a staging table and then push everything into a production table? Once in SQL Server, you will have a lot more control in how you move data from one table to another. So, basically.

1)    Load data into staging
2)    Clean/Convert by copying to a second staging table defined using the desired datatypes. Good data copied over, bad data left behind
3)    Copy data from the "clean" table to the "live" table

sql server Bulk insert csv with data having comma

6 Answers6

Linked

Related