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