>> 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
