Small. Fast. Reliable.
Choose any three.
About Sitemap Documentation Download License News Developers Support

Export the data from your database as tab delimited text. Create one text file for every table in your database: 

    $ pg_dump -a <dbname> -t <tablename> > /tmp/<dumpfile>
    ...and so on...

Trim off crap from header and footer from each file, eg: 

    $ nano -w /tmp/<dumpfile>

If required, create SQLite file and tables corresponding to tables in the original PostgreSQL database: 

    $ sqlite3 <dbname>
    sqlite> CREATE TABLE ...etc...

Finally, import the dump files into SQLite, remembering to change the default import separator to a <tab>: 

    $ sqlite3 <dbname>
    sqlite> .separator "\t"
    sqlite> .import <dumpfile> <tablename>


  #!/bin/sh


  if [ "x$1" == "x" ]; then
     echo "Usage: $0 <dbname>"
     exit
  fi


  if [ -e "$1.db" ]; then
     echo "$1.db already exists.  I will overwrite it in 15 seconds if you do not press CTRL-C."
     COUNT=15
     while [ $COUNT -gt 0 ]; do
        echo "$COUNT"
        sleep 1
        COUNT=$((COUNT - 1))
     done
     rm $1.db
  fi


  /opt/lampp/bin/mysqldump -u root -p --compact --compatible=ansi --default-character-set=binary $1 |
  grep -v ' KEY "' |
  grep -v ' UNIQUE KEY "' |
  grep -v ' PRIMARY KEY ' |
  sed 's/ unsigned / /g' |
  sed 's/ auto_increment/ primary key autoincrement/gi' |
  sed 's/ smallint([0-9]*) / integer /gi' |
  sed 's/ tinyint([0-9]*) / integer /gi' |
  sed 's/ int([0-9]*) / integer /gi' |
  sed 's/ character set [^ ]* / /gi' |
  sed 's/ enum([^)]*) / varchar(255) /gi' |
  sed 's/ on update [^,]*//gi' |
  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
  else
     echo "There were errors during conversion.  Please review $1.err and $1.sql for details."
  fi


  xplain2sql -sqlite3 input.ddl > output.sql

Attachments:

  • fx2lite.prg 5579 bytes added by anonymous on 2006-Sep-21 09:03:53 UTC.
    • A DOS foxpro program to transfer .dbf into an sqlite table is here

  • MDBtoSQLite.vbs 16259 bytes added by anonymous on 2008-Jan-18 11:07:20 UTC.
    Takes in an MDB and produces a text file that can be piped into the sqlite command line tool to create a database. 2008-01-16 <lokkju@lokkju.com> Fixed INTEGER field types to handle null data, now will insert 0, used to break it by inserting nothing. Also modified to accept database path from the command line as an argument.