Data import with SQLLite

[Heavy Johnson]

Hello folks,

I read in another thread (now lost to me), of someone who was able to import EDD's data into SQLLite for doing advanced queries on the system/body DB.

Would anyone who has done that comment, and give a description of how? I have database experience, I just need to know how the data was exported from EDD and into SQLLite.

Thanks!

 

[Genar-Hofoen]

edit: this isn't the real reply, the one next is

 

[Genar-Hofoen]

Hello folks,

I read in another thread (now lost to me), of someone who was able to import EDD's data into SQLLite for doing advanced queries on the system/body DB.

Would anyone who has done that comment, and give a description of how? I have database experience, I just need to know how the data was exported from EDD and into SQLLite.

Thanks!

Hi I'm presuming you meant EDDiscovery? Well I write my own application, version 1 of which was able to read EDD's database and was able to import system notes from that into my app's DB.

EDDiscovery's database(s) are already in sqlite format. Besides which, I believe EDD has a facility to export its data to CSV format.

If you're already familiar with sqlite, you should know how to load EDD's database into another application in order to make queries on it - for example, I use Sqlite Administrator : http://sqliteadmin.orbmu2k.de/

Regards.

EDIT: I have no idea how that partial post #2 happened above. Weird.

 

[Heavy Johnson]

Hi I'm presuming you meant EDDiscovery? Well I write my own application, version 1 of which was able to read EDD's database and was able to import system notes from that into my app's DB.

EDDiscovery's database(s) are already in sqlite format. Besides which, I believe EDD has a facility to export its data to CSV format.

If you're already familiar with sqlite, you should know how to load EDD's database into another application in order to make queries on it - for example, I use Sqlite Administrator : http://sqliteadmin.orbmu2k.de/

Regards.

EDIT: I have no idea how that partial post #2 happened above. Weird.

Thank you! This is helpful.

 

[Redfox]

I read in another thread (now lost to me), of someone who was able to import EDD's data into SQLLite for doing advanced queries on the system/body DB.

Depending on what data you're after EDD's DB might not be sufficient, as it's (naturally) missing interesting data on many attributes of bodies relevant for specific queries/analysis.

So what I do every month or so is to download EDDB's bodies-dump and convert that data into an SQLite DB. With a lot of help from Cruento Mucrone we built a simple C# Application that stream-reads EDDB's bodies.jsonl file (currently weighing in at over 10GB) and dumps the data into the DB-strcuture. The stuff that can be queried from that data (even with very limited and mostly googled SQL) is quite amazing.

I just run my local build, and am actually not quite clear on what/how I'd need to share if you'd be interested.

 

[iain666]

The EDD databases are in SQLite but they're not great for direct querying of a lot of things because we store a lot of things in the DB as the raw JSON from the journals which is grand if you're writing an app that's built around the objects the JSON represents but less amenable to direct SQL querying to pull useful data out.

The doodad I helped Redfox write might be more useful if you want to query body information in a database.

If you're happy building stuff in visual studio then just getting him to give you access to the source would suffice (to answer the last paragraph above)