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.
  • t2sql 5432 bytes added by anonymous on 2008-Aug-16 17:59:56 UTC.
    unix script to convert text file to sqlite db (see t2sql above)
  • fox2lite.prg 6037 bytes added by anonymous on 2008-Oct-03 19:51:21 UTC.
    FOX2LITE A DOS foxpro prg file to convert foxpro .dbf files into sqlite tables. Older fx2lite modified to reflect current (3.6.3) sqlite version. (rafeek.mk@gmail.com)
  • MDB2SQLite_v2.vbs 23528 bytes added by anonymous on 2009-Jan-22 06:21:39 UTC.
    • Added function for exportIndexes - create Indexes (except Primary Key)
    • Added NOT NULL and DEFAULT values in CREATE TABLE (Now() = CURRENT_TIMESTAMP)
    • Fixed CREATE TABLE without Primary Key
    • Added creating TRIGGERs instead of FOREIGN KEY constraits.
    • Added Const dbExportData for genereting scripts without INSERT instructions
    • Added type INTEGER PRIMARY KEY for field type COUNTER