topic Re: Delimiters Ignore Some and Keep others in Data Movement & Streaming

Delimiters Ignore Some and Keep others

crichter14 — Wed, 19 Mar 2025 21:46:28 GMT

Ok, I have searched and I need help.

My users are sending me survey data. The problem is the delimiter is a comma. When I split the data there are some survey comment fields that also have commas that shouldn't be split.

I need ideas on how to either remove commas from the comments. Note, comment fields are all surrounded by quotation marks ("") or to how to tell the load script to ignore the commas inside quotation marks.

Creative people....suggestions?

Thank you!

Below is a simplified example. Unfortunately our surveys have comments through out them so I can't update a certain field(s) consistently.

6,"2023-07-16 14:00:00",,1234568777,Customer Name, 111111, Status, Yes, 1111111111, Store Name, 111111, Person Entered, "We get requests for this frequently, but we don't like the added expense.", Yes, "1.0", 0,0,0

Re: Delimiters Ignore Some and Keep others

Vegar — Wed, 18 Dec 2024 06:10:38 GMT

Quotation marks should be respected as default in Qlik when reading text files. Make sure you don't use the format specification 'no quotes' when loading them.

Re: Delimiters Ignore Some and Keep others

crichter14 — Wed, 18 Dec 2024 19:59:25 GMT

Unfortunately, I'm not loading this. If it were a CSV file it would be fine. Here's the solution I used.

I did a pull and replaced any commas with a space with just a space.

REPLACE(rawData,', ',' ') as rawData;

Then I did a resident load with the subfields. It would have been easier if our Data Warehouse had pipes instead of commas, but this works too. Thanks Vegar!