How to parse comma delimited file with json array columns

Edith1 — Wed, 30 Oct 2024 17:58:38 GMT

I have a comma delimited file (.CSV) and there's a few columns that have json array data. I need those columns to be split into 1 column but its splitting into several columns. How can I do that?

Here's an example of 1 data row:

mhsge@xx.com,John,Doe,"[{""FieldName"": ""Upload"", ""FileName"": ""CSWE2023_Ps.pdf"", ""FileName"": ""851171f2-9821c3.pdf""}]"

Here's the setting for the input:

1st column does result in the email
2nd column does result in first name
3rd column does result in last name
ISSUE: 4th column splits into several columns and I want the entire string data for the json array
- splits into 3 columns:
  - "[{""FieldName"": ""Upload""
  - ""FileName"": ""CSWE2023_Ps.pdf""
  - ""FileName"": ""851171f2-9821c3.pdf""}]"

Thank you in advance!

Re: How to parse comma delimited file with json array columns

ramazanerduran — Thu, 31 Oct 2024 13:31:32 GMT

Hi @Edith1 ,

Qlik or any framework will see the file comma seperated, so every comma will seperate a new column. You should change the delimiter, such as semicolon etc. Then you can take the 4th column as a one column instead of three column.

Changing the delimiter will be solve your issue.

Best regards,

Ramazan

topic Re: How to parse comma delimited file with json array columns in Talend Studio

How to parse comma delimited file with json array columns

Re: How to parse comma delimited file with json array columns