994Strategy for importing SQLite3 Database into MySQL

I am surprised that there is not a simple import function in mysql or phpMyAdmin, I did the following:

  • In SQLite3, export tables as CSV
  • In phpMyAdmin, create DB, and import table as CSV

Make sure you check the box that converst the first line into the table structure.

Table Size/Execution Time Limit

When importing a larger table (in my case ~300M), the phpMyAdmin was complaining about memory (which I fixed), but then the script time out.

Solution? Import directly from mysql

Open the XAMPP mysql binary:

cd /Applications/XAMPP/xamppfiles/bin 
./mysql -u root -p
LOAD DATA LOCAL INFILE '/path/to/table.csv' 
INTO TABLE mydb.mytable FIELDS TERMINATED 
BY ',' ENCLOSED BY '"' LINES TERMINATED 
BY '\n' IGNORE 1 LINES;

(Line breaks are added for legibility.)

  • mydb.mytable are mydb name and mytable table
  • IGNORE 1 LINES ignores the first line with the headers