0

I am trying to use bulk insert for a .txt file, which is separated using a comma, but a few columns also have a double quotes, because of which when bulk insert is used, some rows are not inserted properly.

Also, I have to use bulk insert and not import/export functionality since I am automating my process of inserting the values in the table.

Here is the sample data: test.txt

   ID, Date, Phone, Name
   1,12/31/2017,"7415236541","Name1"
   2,12/31/2017,"8524123652","Name2"
   3,12/31/2017,"9853214536","Name2"

I use the following code, but it does not help

BULK INSERT xImportTable 
FROM 'C:\Files\CSV\test.csv'
WITH 
( FIRSTROW = 2,
FIELDTERMINATOR = ',', 
ROWTERMINATOR = '\n' 
)

But this code does not remove the double quotes.

NancyJoe
  • 41
  • 1
  • 2
  • 5
  • What are the data types of your columns? – Josh Withee Feb 14 '18 at 17:26
  • Possible duplicate of [Bulk Insert Correctly Quoted CSV File in SQL Server](https://stackoverflow.com/questions/12902110/bulk-insert-correctly-quoted-csv-file-in-sql-server) – Jacob H Feb 14 '18 at 17:28
  • 2
    Possible duplicate of [SQL Bulk import from CSV](https://stackoverflow.com/questions/96448/sql-bulk-import-from-csv). Worth noting is that the sample you posted actually contains smart quotes (`”`) in addition to regular double quotes. Removing these may require a post-processing step if you can't fix up your data to begin with. – Jeroen Mostert Feb 14 '18 at 17:28
  • Indeed, the rich-text-format quotes may be the issue, in which case I wouldn't consider it a duplicate question. – Josh Withee Feb 14 '18 at 17:38
  • @JeroenMostert : I just edited my post, The text does have double quotes and not smart quotes. It was an error on my side to post. – NancyJoe Feb 14 '18 at 17:38

0 Answers0