0

I'm attempting to BULK INSERT a tab-separated text file into a database only containing VARCHAR data. For some reason, some of the data is getting double quotation marks placed around it randomly, while other rows do not:

domain      sku         type    product
amazon.com  b0071n529i  laptop  hp_4535s_a7k08ut#aba_15.6-inch_laptop
amazon.com  b00715sj82  laptop  "dell_64gb_mini_pcie_ssd_pata,_f462n"

The statement I'm using looks like this:

BULK INSERT database
FROM 'file.txt' WITH (FIRSTROW = 1, FIELDTERMINATOR = '\t', ROWTERMINATOR = '0x0a');
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tsb8m
  • 75
  • 7
  • If you use a terminator (in your case the `tab`), which might occur **within** a text as well, the usual approach is to use *text qualifiers*. In most cases this is the *double quote*. Just imagine a CSV with the semi-colon as delimitier and a row like `1;2;This is a text;and it continues`. The engine would not know, that there is a semi-colon as part of the text and would cut the string. The solution `1;2;"This is a text;and it continues"`. Check if the quoted strings include a tab... – Shnugo Nov 11 '18 at 11:14

1 Answers1

0

If your issue is those double quotes then you can do this after insertion that would be the better solution,

UPDATE TABLE A
SET A.Product=Replace(A.Product,'"','')
Where Left(A.Product,1)='"' or Right(A.Product,1)='"'
Ajan Balakumaran
  • 1,639
  • 1
  • 8
  • 16
  • My issue is the double quotes. And yes, a replace would fix the issue... but why are they being added in the first place? They aren't in my txt file. – tsb8m Nov 11 '18 at 03:05
  • could you check this, – Ajan Balakumaran Nov 11 '18 at 03:08
  • https://stackoverflow.com/questions/12902110/bulk-insert-correctly-quoted-csv-file-in-sql-server – Ajan Balakumaran Nov 11 '18 at 03:08
  • from what i see the double quotation you are getting is a text qualifier, so if you can set that as text qualifier you won't get that. – Ajan Balakumaran Nov 11 '18 at 03:10
  • My text file doesn't have any double quotes though... tabs are my text qualifier. – tsb8m Nov 11 '18 at 04:49
  • Tab is your field terminator, within the value if exists a field terminator inorder to considere that as a part of the value instead as a field terminator we need to use a text qualifier, in this case the text qualifier double quote does that and it seems like it’s auto generating to serve that purpose. – Ajan Balakumaran Nov 11 '18 at 05:00