If you have an SQL schema that works for you and also sample data, I might be able to assist writing the conversion program. Dunno if you're interested since it sounds like you won't need to do this again for another year.
On Sat, Jan 19, 2019, 23:17 David Bicking <dbic...@yahoo.com wrote: > >> Is there a tool out there that will more or less automate the task for > > > >> me? Hopefully free, as no one is paying me to do this. (The other > >> volunteers have maybe a dozen records in total and are doing their > >> reports by hand. ) > > >The automation is at a lower level than you seem to realize. JSON I/O is > a solved problem, but actually >doing anything with that data, such as > transforming it into a relational database form, is up to you. > > I thought I got lucky. I found an web site called SQLify, that lets you > upload a JSON file, and it will examine it, figure out a schema, and > create CREAT TABLE and INSERT lines with the data. Kind of neat. > Unfortunately, it flattened the data in to one table, which didn't matter > for the venue and group data, but it flattened the hosts data by only > using the first one and discarding the rest. > >> A cursory look at the data: there is a top level "event" table. "Group" > >> and "venue" tables that are one-to-one with the event table, and a > >> "hosts" tables with many hosts to the event table. > > > >JSON doesn't have tables, nor does JSON pre-declare a schema. JSON > intermixes the schema with the >data, and the schema is allowed to vary > over the course of the document. > > Yeah, sloppy writing on my part. JSON has objects, which can be stored in > SQL tables. > > >> Oh, to complicate things, the source data is not valid JSON, as the>> > upstream source did not escape quote marks within the text. So is there > >> a tool that can clean up the quotes, hopefully there won't be many bad > >> quotes... maybe three or four in the 600 record.> > >Then it's not really JSON and can't be processed with true-blue JSON > tools. Everything you're saying is >calling for a custom tool. Custom > tools do not have to be hard or expensive though. > > > >But in the case of quotes not being escaped, that all by itself could be > difficult to automate, since heuristics >will need to be applied to figure > out whether any given quote mark is a string delimiter. Human > >intervention might be required. > > I kind of solved this by running the data through a parser. I would then > take the error message, and find the text in the source file, and escape > the quotes. Then parse again and fix the next error. Tedious but that works. > David > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
