topic Re: CSV embedded in JSON in QlikView

CSV embedded in JSON

AlexOmetis — Thu, 03 Aug 2017 21:07:24 GMT

I'm extracting data from the Harvest API. I've got plenty of data out of it, but am struggling with the strange format they output Invoice Lines... it's CSV embedded within JSON or XML - which is a new one on me. This part of the API is documented here.

So how do you turn this value in a JSON response:

kind,description,quantity,unit_price,amount,taxed,taxed2,project_id\nProduct,A description,1.00,100.00,100.0,false,false,\ninto something that Qlik can analyse properly?

Initially I thought it would be fine - just subfield on newline (chr(10)) and then again on comma, but the usual things catch you out - newlines and commas within the data. If there's no new line or comma in a field, it doesn't wrap the contents in quotes, but if there is, it does.

Then I thought perhaps I could store it out to a file and read it back in as a CSV file, but of course Qlik adds its own quotes around things and additional header row when you save to CSV/TXT so that doesn't help.

Of course I could call an external program to do the processing, but I wondered if anyone had a way of doing this within Qlik.

Re: CSV embedded in JSON

Anil_Babu_Samineni — Fri, 04 Aug 2017 01:35:37 GMT

I got 90% which you explained. Still doubt what you are trying? And can i know simple way to do in Qlik where you are expecting

Re: CSV embedded in JSON

AlexOmetis — Fri, 04 Aug 2017 09:15:06 GMT

I get one of those CSV-formatted strings in the JSON record for each invoice, detailing the lines in the invoice. I'd like to end up with a table with Invoice ID (which I can get from the header just fine) and then kind, description, quantity etc as fields. It'd be easy if it was in files, but it's being embedded in JSON that makes it tricky.

Re: CSV embedded in JSON

petter — Sat, 12 Aug 2017 18:41:08 GMT

You can load the JSON with a regular LOAD and afterwards you can LOAD ..... FROMFIELD .... from the field that has the CSV.

Could you provide a sample JSON-file with some CSV embedded and I can give you a working example.

Re: CSV embedded in JSON

AlexOmetis — Mon, 14 Aug 2017 08:41:18 GMT

Fantastic - had never come across that before, but that's sorted it - thanks very much.

(Although worth noting it's FROM_FIELD not just FROMFIELD)

Re: CSV embedded in JSON

petter — Mon, 14 Aug 2017 09:03:03 GMT

It seems to be very slow though... Haven't used it on very large amounts of data but the experience so far is that it is *slow* - the FROM_FIELD loading not JSON.