I'm using SQL Server Bulk Insert to import a .csv file but my .csv has double quotation marks enclosing text fields. Is it possible to remove the quote marks whilst importing the data?
Asked
Active
Viewed 1,483 times
0
marc_s
- 732,580
- 175
- 1,330
- 1,459
Dave Williams
- 1
- 2
-
Double quotes are used to enclose text that might contain commas that shouldn't be considered a separating value. If you do remove them, make sure the commas are still treated correctly. – Forklift Feb 14 '17 at 16:13
-
`FIELDTERMINATOR ='","'` is what you are looking for I think. However, you will be left with the first and last column having a quote in it. You can fix this with `REPLACE()` function after you bring the data into a `TEMP TABLE` prior to inserting it into your `DB Table`or use a `FORMAT FILE` with your `BULK INSERT` to avoid this all together. They can be annoying though. – S3S Feb 14 '17 at 16:13
-
http://stackoverflow.com/a/25732545/6167855 – S3S Feb 14 '17 at 16:34
-
Thanks for your answer. I think a Format File may be the answer but what parameters would I set in the file to exclude the quotes. – Dave Williams Feb 14 '17 at 16:35
-
[Create a Format File](https://msdn.microsoft.com/en-us/library/ms191516.aspx), or, [another article](https://msdn.microsoft.com/en-us/library/ms190393.aspx), and lastly, the answer I linked above was for a quoted field... just like yours – S3S Feb 14 '17 at 16:43
-
Yes, thanks, I didn't see the link! I'll give that a go. – Dave Williams Feb 14 '17 at 17:05
-
This answers it: https://stackoverflow.com/a/62803705/79485 – Marcel May 05 '22 at 12:13