View Full Version : Easiest way to extract the content of an SQLite2 database?
02-02-2010, 03:12 PM
I found an SQLite2 database that contains hard-to-find experimental data and want to get to the data. As long as I can get text, integers, doubles, and so forth, and the metadata (which I read SQLite embeds in its database files), I am happy.
But I don't want to learn how to program with SQLite. I've never heard of it before and now there is just one single 15 kB database from which I need content. It was posted to a web site about 5 years ago as part of somebody's thesis.
Using a text editor I can see material names inside, but the rest of it just looks garbled.
The file name has no extension, which I read is unimportant for SQLite. The custom, they say, is to give it an extension that describes its content, so for instance if you put February sales information inside you would name it February.sales.
02-02-2010, 03:29 PM
I've never done this but here are some links...
Try the sqlite2 engine. Download: http://www.sqlite.org/sqlite-2_8_17.zip
Then try the .dump command as mentioned here: http://www.sqlite.org/sqlite.html
That page is about sqlite3 but many of the commands are the same in sqlite2.
02-02-2010, 04:11 PM
why do you need to learn to program SQLite? It's an SQL database like any other. Maybe it has quirks, like maybe storing numbers as strings or something like that, but for the purposes of data extraction this shouldn't be an issue. It can be accessed from wrappers written in almost any environment, I have got ones for C# and possibly also for Java. Plus also from console although this may be not as convenient for data manipulation.
So basically you can run same old SQL commands on it. Or you can hire some guy from Eastern Europe to run those commands for you for $7 an hour based on your generic instructions.
02-02-2010, 06:04 PM
Ruminator, thanks, I will try the command line precompiled binary.
Code_grey, I don't know how to open ANY sql databases. Do I need a database program? Would any work? I can imagine writing a program to do so but it wouldn't be reasonable given my minor programming proficiency. Is there anything that is part of the typical WinXP bundle, or Microsoft Office, that can do it? I hoped maybe SAS could but "sqlite" doesn't appear in a help system search.
02-02-2010, 06:37 PM
Napier, you don't "open" SQL databases. You query them, using SQL language (which is the same for all SQL databases). First you a run a query to find out the structure ("schema") of the database - that is, the names of the tables and the names and types of the columns in these tables. Then, knowing this, you can do various things with this data. Probably in your case what you would want to do is extract the entire contents of those tables that you care about into one or more CSV file that you can then work with in Excel.
The hardest part is really getting the app shell connect to the database, i.e. getting through the "hello world" initial hurdle. Running the queries and making CSV files (or any other form of data export) is trivial.
Also, come to think of it, if you don't learn the schema, what good would it do for you to just look at the contents of the SQLite file archive in Wordpad? So you see a lot of numbers interspersed with a lot of weird garbage - how would you make sense of it?
Sure, you could write your own program to convert it to a form you recognize. Or you could just use a program that already exists (http://www.processtext.com/abcsqlite.html).
I'm pretty sure that's what Napier wants to do.
02-03-2010, 05:48 AM
BigT, you are pretty right, that's exactly what Napier wants to do! Thanks! When I get to work I will try ABC Amber.
Code_grey, you confuse me. Google "open the database" as a phrase and you see 53 million hits, and all of the dozen I checked used it in the way I thought - generally in computing to do something with a file, one of the initial steps is to access the file in a way that makes it the current default context for nonvolatile data reads and writes, which typically includes creating a handle for it, signalling that you are using the file to others if it is shared, perhaps buffering it or buffering some parts of it, and usually reading and perhaps checking some kinds of information that facilitate more detailed use (versioning, organization, headers, and the like). It certainly looks like database programs do that, from a quick tour. I understand that databases in particular facilitate querying them, supporting a structured query language, but a database could also be useful as a dataset for building a model, which is what I want to do with it. What am I not getting?
02-03-2010, 08:20 AM
BigT, good point, thanks for the pointer. Sounds like a nice app just for this purpose.
02-03-2010, 08:36 AM
For non-tech people, I recommend Crystal Reports. It supports ODBC and has drivers for nearly any database ever made.
Crystal can export text files. I do it all the time. You can extract your data and use a formula to create comma delimited text file(s). They can be imported into any database you want.
02-03-2010, 05:43 PM
Gee, ABC Amber isn't panning out yet. RapidShare keeps saying their servers are too busy to give me the file unless I become a Premium Member. Can these people be trusted with credit cards or whatever, or is this a can of worms?
02-03-2010, 05:46 PM
"Starting in Jan 2010, Rapidshare started to claim it servers were overloaded, blocking free access and funneling users to the pay version of the service."
So sez Wikipedia. Why aren't they just telling users it's a pay-only site? Their legal troubles?
BigT, have you used this program? Do you know it works?
02-03-2010, 05:48 PM
Have you tried the sqlite2 program on your file and run the .dump command yet?
02-04-2010, 12:48 PM
Ruminator, I have, but I don't think I understand the instructions. The cite says ".dump ?TABLE? ... Dump the database in a text format". What do I type? Typing ".dump ?TABLE?" answers "BEGIN TRANSACTION; COMMIT;". The instructions use TABLE without question marks, and FILENAME, elsewhere, as if these are not the same thing. I am guessing at different things to type but have not made sense of their instructions, and nothing so far has created a text file, though the .read command does write to the console the gibberish I think I saw already with a text editor.
My database file is named cocoe_db with no extension (which at sqlite.org I understand is common). What would I type to dump this into Dumpfile.txt?
Sorry to be so dense!
vBulletin® v3.7.3, Copyright ©2000-2013, Jelsoft Enterprises Ltd.