Convert Excel file with unicode characters to MySQL

Scenario: It takes you the whole morning to try to get data in an Excel file into MySQL table. You found out there were special characters such as “5µm” inside it.

Solution: Save the file as .txt (Tab delimited or MS-DOS or Machintosh) and make sure it’s encoded in utf-8, etc.

Steps:

  1. Create table in MySQL db using the sample (with only headers trimming off the content) csv file saved from the .xls file;
  2. Save the .xls as Tab delimited .txt;
  3. Load data into MySQL using the following statement (as produced on Windows for tab delimited, double quote inclosing fields.) See here for details:
  4. LOAD DATA LOCAL INFILE '/STUFF/Projects/_tmp/reference.txt' INTO TABLE reference
    FIELDS TERMINATED BY '\t' ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

  5. Clean up the table: You may need to delete the some rows if the Excel table/sheet headers appear as data rows, etc.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: