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