2

Here is a peculiar scenario: I am trying to import a tab-delimited file into MySQL which has some rows with prices above 1,000. When I finish the import, the values after the comma are excluded and I end up with just 1 in my table. Tried in both Linux and Windows environments with the same results.

For example, if row 1, column 1 in a tab-delimited file contains the value 1,564.50 I end up with 1 in my row 1 column 1 in my MySQL table. This is the statement I am using.

LOAD DATA LOCAL INFILE 'blah.txt'
 INTO TABLE `table1`
 FIELDS TERMINATED BY '\t'
 OPTIONALLY ENCLOSED BY  ''''
 LINES TERMINATED BY '\n';

So my question is:

  1. can someone confirm this or how I can fix this behavior if I am missing something on my end.
  2. If this is a MySQL bug what are my options for a workaround to importing these values properly till MySQL fixes it.
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
user1715559
  • 79
  • 2
  • 9

1 Answers1

2

The default delimiter of Windows is comma. Change it with another character and match it with your source data.

To change default delimiter:

Go to the Control Panel --> Regional and Language Options (or Date, Time and Regional Options --> Regional and Language Option on some machines) --> click the Customize button. In the dialog box that opens you will see and option for "List separator" in which you can change the comma to a different character --> click Apply then Ok and then Apply again. Now when you go into Excel and choose Export as CSV the new character will be used as the delimiter.

abc
  • 157
  • 3
  • 14
  • even though you used FIELDS TERMINATED BY '\t' the data source will still be delimited by comma that's why you got 1 on row1 column 1, because the value has a comma (1,000) – abc Oct 03 '12 at 03:23
  • I tried this in both linux and windows env and same result. so changing regional settings wont make any sense right? anyways i have backtick as regional settings and still same result. – user1715559 Oct 03 '12 at 12:39
  • not sure what you mean there when u say it is still going to take comma as i clearly say the file is tab delimited so mysql should take everything as it it when it sees a comma. – user1715559 Oct 03 '12 at 12:40
  • your not getting my point sir, what im trying to say is that even though you used **FIELD TERMINATED BY '\t'** in mysql it will still follow the comma delimiter since it is the default separator on Windows. What you need to do is change the separator list to tab instead. Please see the image on this link. [Change list separator in Windows](http://www.treeplan.com/images/customize-format-german.jpg) – abc Oct 03 '12 at 14:58
  • as you can see on the image, the separator list is set to semicolon **(;)** But the default is comma **(,)** so you have to change comma **(,)** i am just not sure if \t will work. – abc Oct 03 '12 at 15:09
  • ok how about in linux why am i seeing the same result in linux box? – user1715559 Oct 03 '12 at 18:33