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