BULK INSERT with FIELDQUOTE

Question

Can someone tell me why this doesn't work?

Using SQL Server 2019. SSMS v18.1. FieldQuote option does not seem to be recognised by SSMS as it remains black rather than changing to blue for keywords.

My data is as follows:-

12345, 124, "this works", "some other text"

34568, 321, "this, does not work", "some more text"

It seems that the comma between double-quotes in the 2nd line is not liked at all.

I'm looking specifically for why this is not working using BULK INSERT.

Thanks for any help,

James

BULK INSERT dbo.myTable
FROM 'E:\myFile.csv'
    WITH
    (
        FIELDTERMINATOR = ',',
		CODEPAGE = '65001',
		DATAFILETYPE = 'char',
		FIELDQUOTE = '"',
        ROWTERMINATOR = '0x0a',
        FORMAT = 'CSV'
    );

Answer 1

Hi @JamesProsser,

You can ignore that the SSMS is not changing color of the FieldQuote option.

More important is to add a FORMAT='CSV' option.

It will process your input file properly, with the commas in the actual data, and will remove surrounding single quotes.

Input file ('e:\Temp\JamesProsser.csv')

1264482,53,278919,'20800','Sales | volume','text'
1264483,9,278920,'1700','FV | AP Amount','text'
1370379,49,287061,'Guaranteed, but investigating','Sales | Quote','text'

USE tempdb;
GO
DROP TABLE IF EXISTS dbo.tbl;
GO
CREATE TABLE dbo.tbl (ID INT, col2 INT, col3 INT, col4 VARCHAR(50),col5 VARCHAR(50),col6 VARCHAR(50));

BULK INSERT dbo.tbl
FROM 'e:\Temp\JamesProsser.csv'
WITH (
   FORMAT='CSV' -- starting from SQL Server 2017 onwards
   , FIRSTROW = 1
   , FIELDQUOTE = ''''
   , FIELDTERMINATOR = ','
   , ROWTERMINATOR = '\n');

-- test
SELECT * FROM dbo.tbl;

Output

Answer 2

I was able to load your file with the statement in your post. However, I had to change the file. In your post, there are spaces after the commas. That did not work out well, so I removed the spaces, assuming that they are an artefact of the composition of the message.

If your file indeed has spaces, it is still possible to load the file, but you need to modify the field terminator accordingly:

BULK INSERT dbo.MyTable
FROM 'C:\temp\slask.bcp'
    WITH
    (
        FIELDTERMINATOR = ', ',
		CODEPAGE = '65001',
		DATAFILETYPE = 'char',
		FIELDQUOTE = '"',
        ROWTERMINATOR = '0x0a',
        FORMAT = 'CSV'
    );

Answer 3

Thank you for the responses unfortunately, they do not help. To those that suggested I use FORMAT='CSV', I am already doing that as indicated in my original post.

Erland - I thought you might be on to something with the space in the text but the space was only in my sample text and is not present in my real text. Apologies, I should have posted real data.

My real data is as follows:-

1264482,53,278919,'20800','Sales | volume','text'

1264483,9,278920,'1700','FV | AP Amount','text'

1370379,49,287061,'Guaranteed, but investigating','Sales | Quote','text'

Not this actually has single quotes to determine text fields. I cannot find a way to specify a single quote in the FIELDQUOTE = ''' . I've tried escaping the quote, FIELDQUOTE = ''' but that does not work either.

Therefore, I replaced all double quotes with single quotes so I now have the data.

1264482,53,278919,"20800","Sales | volume","text"

1264483,9,278920,"1700","FV | AP Amount","text"

1370379,49,287061,"Guaranteed, but investigating","Sales | Quote","text"

I'm not sure if in reality I will be able to receive the file in this format but let's hope so.

Running this....

BULK INSERT dbo.MyTable
FROM 'C:\DoubleQuoteTest.csv'
    WITH
    (
		CODEPAGE = '65001',
		KEEPNULLS,		
		ROWTERMINATOR = '0x0a',
	    FORMAT = 'CSV',
		FIELDQUOTE = '"',
        FIELDTERMINATOR = ','
    );

produces the error....

Msg 4879, Level 16, State 1, Line 3

Bulk load failed due to invalid column value in CSV data file C:\DoubleQuoteTest.csv in row 1, column 6.

Msg 7399, Level 16, State 1, Line 3

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 3

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Any further suggestions welcome.

Thanks,

James

Answer 4

Hi JamesProsser,

Use FORMAT='CSV' could solve this problem. Please refer to this link.

FORMAT = 'CSV' specifies a comma-separated values file compliant to the RFC 4180 standard.

FIELDQUOTE = 'field_quote' specifies a character that will be used as the quote character in the CSV file. If not specified, the quote character (") will be used as the quote character as defined in the RFC 4180 standard.

First, I would like to say that your sample data has spaces after the comma, you need to add spaces in the code as well, otherwise I will encounter an error. Erland also mentioned this point.

Then I did tests.

Scenario 1: without FIELDQUOTE = '"' and without FORMAT = 'CSV' BULK INSERT Test1 FROM 'C:\xxxxx\t1.csv' with ( FIELDTERMINATOR = ', ', ROWTERMINATOR = '\n', CODEPAGE = '65001',DATAFILETYPE = 'char')

Scenario 2: with FIELDQUOTE = '"' and without FORMAT = 'CSV' BULK INSERT Test1 FROM 'C:\xxxxx.csv' with ( FIELDQUOTE = '"', FIELDTERMINATOR = ', ', ROWTERMINATOR = '\n', CODEPAGE = '65001',DATAFILETYPE = 'char')

The result is the same as above.

Scenario 3: without FIELDQUOTE = '"' and with FORMAT = 'CSV' BULK INSERT Test1 FROM 'C:\xxxxx\t1.csv'

with ( FIELDTERMINATOR = ', ', ROWTERMINATOR = '\n', CODEPAGE = '65001',DATAFILETYPE = 'char', FORMAT='CSV' )

Got the desired result and FIELDQUOTE = '"' has no effect on the result.

Scenario 4: with FIELDQUOTE = '"' and with FORMAT = 'CSV'

1. BULK INSERT Test1 FROM 'C:\xxxxx\t1.csv'

with ( FIELDQUOTE = '"', FIELDTERMINATOR = ', ', ROWTERMINATOR = '\n', CODEPAGE = '65001',DATAFILETYPE = 'char',FORMAT='CSV' )

You can see that the one that can remove the quotation marks around the comma is FORMAT = 'CSV'.

Hope my answer could give you some help.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".