.db file and MySQL

Question

Hi ,

I am having real issues with a .db file its around 20gb in size with three tables and the rest data.

I am on a mac so i am having to use some crappy apps but it wont open in Access.

Does any one know what software will produce a .db file and what software will allow me to open it and export it as a CSV or MySQL file ?

Also if the connection was interrupted during transit could this effect the file ?

Answer 1

Since mac is BSD-based now, try opening a terminal and executing the command file /path/to/large/db -- it should tell you at least what file type the DB is, and from there you can determine what program to use to open it. It might be MySQL, might be PostGreSQL, might be SQLite -- file will tell you.

Example:

$ file a.db
a.db: SQLite 3.x database

$ file ~/.kde/share/apps/amarok/mysqle/amarok/tracks.{frm,MYD,MYI}
~/.kde/share/apps/amarok/mysqle/amarok/tracks.frm: MySQL table definition file Version 10
~/.kde/share/apps/amarok/mysqle/amarok/tracks.MYD: data
~/.kde/share/apps/amarok/mysqle/amarok/tracks.MYI: MySQL MISAM compressed data file Version 1

So it's SQLite v3? Then try

sqlite3 /path/to/db

and you can perform pretty much standard SQL from the CLI. At the CLI, you can type .tables to list all the tables in that DB. -- Or if you prefer a GUI, there are a few options listed in this question. Accepted answer was SQLite manager for Firefox.

Then you could drop tables or delete as you see fit.

Here's an example of dumping a csv to stdout:

$ sqlite3 -separator ',' -list a.db "SELECT * FROM t"
3,4
3,5
100,200

And to store it to a file -- the > operator redirects output to a file you name:

$ sqlite3 -separator ',' -list a.db "SELECT * FROM t" > a.csv
$ cat a.csv # puts the contents of a.csv on stdout
3,4
3,5
100,200

-separator ',' indicates that fields should be delimited by a comma; -list means to put row data on the same line, using the delimiter; a.db indicates which db to use; and "SELECT * FROM t" is just the SQL command to execute.

Answer 2

I'm not a Mac user but if it's a SQLite file I've heard great things about Base.