Search code examples
mysqlfile-iotabsdelimited

MySQL includes comma on a Tab delimited file (LOAD DATA INFILE)

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.
Solution

  • 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.