String parsing in DataStage

Question

I have a problem with a DataStage Parallel Job that doesn't parse strings properly. I have a csv file, with two columns containing json-shaped strings. The job reads it with a Sequential File stage, where the delimiter is set as comma and the quotation mark is double quote. I don't produce the file and can't ask to modify it. The strings have this form:

"{""key1"":""value1"",""key2"":""value2""}"

Datastage reads "{" and interprets it as the string that have to be inserted in the correspondent field, and ignores the rest of the string - "key1"":""value1"",""key2"":""value2""}". I tried to run the job with a test file where those strings were replaced with strings of the following form:

'{"key1":"value1","key2":"value2"}'

and it worked. I know the reason is that the replacement of some of the double quotes with single quotes allows DataStage to understand the structure of the beginning and the end of the string. But I would like to know if there is any way to solve the problem while keeping those strings in the original form.

Thanks in advance to anyone who can help.

@MichaelTiefenbacher thanks for you answer. I tried, but I get the same problem. — ennezetaqu
– ennezetaqu, Commented Dec 19, 2024 at 8:31
You could try to read the file through a filter: sed -E -e "s/\"{2}/'/g", but this could cause problems, if a string within a json (like "value1") again contains single quotes. If you know your source data will not have such strings, it could work. — Justus Kenklies
– Justus Kenklies, Commented Mar 23 at 20:13

Ralf Martin · Accepted Answer · 2025-02-10 10:26:45Z

2

Either read the whole line as one string and then cut it in a transformer (with e.g. the field function), or use the "Hierarchical Data" Stage to interpret your JSON string.

answered Feb 10 at 10:26

Ralf Martin

313 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

String parsing in DataStage

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related