Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

script to convert mysql dump sql file into format that can be imported into sqlite3 db

Tags:

I have an export SQL file containing tables and data from MySQL and I want to import it into a Sqlite 3 DB.

What is the best way to do that?

Just importing the file via the sqlite3 tool doesn't work.

like image 694
DEzra Avatar asked Jan 28 '09 20:01

DEzra


People also ask

How do I import a file into SQLite?

First, from the menu choose tool menu item. Second, choose the database and table that you want to import data then click the Next button. Third, choose CSV as the data source type, choose the CSV file in the Input file field, and choose the ,(comma) option as the Field separator as shown in the picture below.

What is SQLite dump?

The . dump command converts the entire structure and data of an SQLite database into a single text file. By default, the . dump command outputs the SQL statements on screen. To issue the output to a file, you use the .


2 Answers

This shell script help you

#!/bin/sh
if [ "x$1" == "x" ]; then
   echo "Usage: $0 <dumpname>"
   exit
fi
cat $1 |
grep -v ' KEY "' |
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |
sed 's/ unsigned / /g' |
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' |
sed 's/ tinyint([0-9]*) / integer /g' |
sed 's/ int([0-9]*) / integer /g' |
sed 's/ character set [^ ]* / /g' |
sed 's/ enum([^)]*) / varchar(255) /g' |
sed 's/ on update [^,]*//g' |
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -pe '
  if (/^(INSERT.+?)\(/) {
     $a=$1;
     s/\\'\''/'\'\''/g;
     s/\\n/\n/g;
     s/\),\(/\);\n$a\(/g;
  }
  ' > $1.sql
cat $1.sql | sqlite3 $1.db > $1.err
ERRORS=`cat $1.err | wc -l`
if [ $ERRORS == 0 ]; then
  echo "Conversion completed without error. Output file: $1.db"
  rm $1.sql
  rm $1.err
    rm tmp
else
   echo "There were errors during conversion.  Please review $1.err and $1.sql for details."
fi
like image 134
Igor Avatar answered Oct 23 '22 19:10

Igor


To get the above script to work, I made the following changes:

  1. run it with #!/bin/bash
  2. add two seds to the list of pipelined seds:
    • sed 's/\\r\\n/\\n/g'
    • sed 's/\\"/"/g'
  3. the 'rm tmp' line is a no-op (unless you have a file named 'tmp' lying around :O )
  4. my mysqldump command looked like this:

    $ mysqldump -u usernmae -h host --compatible=ansi --skip-opt -p database_name > dump_file

Then it worked nicely... thanks for the script.

like image 30
Mike Fogel Avatar answered Oct 23 '22 17:10

Mike Fogel