5 Replies Latest reply: Aug 14, 2017 5:04 AM by Petter Skjolden RSS

    CSV embedded in JSON

    Alex Walker

      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.