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.
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.
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 .
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
To get the above script to work, I made the following changes:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With