sqlite - Converter Tools

Not logged in

• Honeypot
• Browse
• Home
• Login
• Reports
• Search
• Ticket
• Timeline
• Wiki

• Contents
• Diff
• Text

• MDB Tools

  The MDB Tools project is a effort to document the MDB file format used in Microsoft's Access database package, and to provide a set of tools and applications to make that data available on other platforms.

  Specifically, MDB Tools includes programs to export schema and data to other databases such as MySQL, Oracle, Sybase, PostgreSQL, and others.

  Also, included is a SQL engine for performing simple SQL queries. The 0.5 release includes an updated GUI interface (screenshot is available here). A sparse but functional ODBC driver is included as well.

  MDB Tools currently has read-only support for Access 97 (Jet 3) and Access 2000/2002 (Jet 4) formats. Access 2000 support is a recent addition and may not be as complete as Jet 3 support.

  Write support is currently being worked on and the first cut is expected to be included in the 0.6 release.

  Note: I don't think it supports SQLite right now, but this project can be continued and used for that purpose.

• Java Library for MS Access

  Jackcess ia a pure Java library for reading from and writing to MS Access databases. It is not an application. There is no GUI. It's a library, intended for other developers to use to build Java applications. Jackcess is licensed under the GNU Lesser General Public License.

  This link was included in contrast to the MDB Tools link above. Similarly, it has no SQLite understanding at this time.

• Simple VB MDB to SQLite

  I coded up a small limited visual basic tool that takes in an MDB and produces a text file that can be piped into the sqlite command line tool to create a database. It's simple and very dirty code, but it does work for simple databases. It's attached to this document and you can do whatever you want with it.

  i did a few improvements to vb mdbtosqlite to convert northwind, orders, biblio, and gallery ms-access sample mdbs to sql code loadable in sqlite. everything was read fine, with the exception of binary picture fields, which are also been reported in the output file, as comments closely following each create table statement. the ofending fields are still being created with "BINARY" type and inserted as NULL. code is posted as mdbtosqlite2.zip, so that the original author might examine it and hopefully merge the two versions.

  <rapto@arrakis.es> I have modified this program into a vbs so you don't have to have Visual Basic.

  <rotoxl@jazzfree.com> Changed function getSQLiteFieldType according to dao360 docs to handle datatypes properly.

  <rotoxl@jazzfree.com> the link: http://burke.ath.cx/~ernesto/MDBtoSQLite.vbs

• SQLFairy or SQL::Translator

  From their website:

  SQL::Translator is a group of Perl modules that manipulate structure data definitions (mostly database schemas) in interesting ways, such as converting among different dialects of CREATE syntax (e.g., MySQL-to-Oracle), visualizations of schemas (pseudo-ER diagrams GraphViz or GD), automatic code generation (using Class::DBI), converting non-RDBMS files to SQL schemas (xSV text files, Excel spreadsheets), serializing parsed schemas (via Storable, YAML and XML), creating documentation (HTML and POD), and more. New to version 0.03 is the ability to talk directly to a database through DBI to query for the structures of several databases.

  Excellent tool which helped me migrate from MySQL to SQLite.

• Firebird <-> SQLite

  Windows executable with sources

• Kexi

  Kexi (Open Source MS Access competitor) is able to import/export database schemas between SQLite format (which is Kexi's native format) and server engines (MySQL, PostgreSQL) and MS Access (using MDB Tools). User-friendly wizards are prepared. Importing tables with CSV format is also available with a nice GUI.

• PostgreSQL to SQLite3

  After a little experimenting, I found a way to import data from PostgreSQL into SQLite, version 3, without having non-ASCII characters ruin things (something that getting pg_dump to export SQL statements did...). It could be enormously improved with a little scripting love, but as I only had one database with a couple of big tables to deal the below was fine, however... post improvements!

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>

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.