Saturday, December 16, 2006

Importing SQL Server CSV to MySQL

I have another situation here. I want to import SQL Server data (SQL Server 2000 running onWindows 2003), a subset of it, into MySQL database (v5.0.22 running on Fedora Core 5). I have phpMyAdmin 2.9.1-rc2 up and running on my Linux box. This is how I get it done:
  1. Run the desired query using SQL Server Query Analyzer and wait until the result show up in the data grid.
  2. Right-click on the data grid, select "Select All", and then "Save As". A "Save Grid Results" dialog box appears.
  3. Choose the file name you want, the file type ("Export Files (.CSV)"), the file format ("ANSI"), and the column delimiter ("Comma Separated (CSV)") then click save. You have to save it anywhere accessible from your phpMyAdmin.
  4. Login to phpMyAdmin, create the target database if not exists yet, and create the target table within that database. Make sure the destination table has as many columns using the proper data types as the data being exported. Otherwise I do not guarantee it will work.
  5. From the phpMyAdmin SQL menu make sure you have replace the ";" in the Delimiter input text with "," as we use comma separated value for each fields, and type this query:
    LOAD DATA INFILE '/path/to/file_name.CSV' INTO TABLE table_name
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
  6. Don't forget to click "Go" when you're finished and you're done with it.

No comments: