Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Partner
Partner

CSV embedded in JSON

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,\n
into 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.

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: CSV embedded in JSON

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.

5 Replies

Re: CSV embedded in JSON

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: CSV embedded in JSON

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.

MVP
MVP

Re: CSV embedded in JSON

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.

Partner
Partner

Re: CSV embedded in JSON

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)

Highlighted
MVP
MVP

Re: CSV embedded in JSON

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.