When loading CSV file, issue with LF, CR, and/or commas within a field

[mfolmar]

Hello! I have an issue when loading data from a CSV file where the data is loading into incorrect fields. The issue is when my Comment field has a Line Feed/Carriage Return or a comma directly after the file places the double quote in the Comment field. 

Example of CSV data: 

Number, Comment, Room

1, "Size: 10T x 3"", moved to basement", 104

2, "spiral staircase

upstairs", 500

3, "Part Numbers are as follows: 

3x456t

45""

098432" , 750

Output: 

Number	Comment	Room
1	"Size: 10T x 3""	moved to basement
2	"spiral staircase upstairs"	500
3	"Part numbers are as follows: 3x456t 45""	098432

As you can see, its not every LF/CR or comma that causes issues, just the ones directly following those "" in the Comment that cause the data to move into the next field. 

Can anyone recommend how to fix this? I've tried omitting msq in my load script, but that doesn't fix it, it makes things much worse. I'm rather new to Qlik Sense and I'm not sure if I'm explaining this correctly. 

[BrunPierre]

Hi, as below.

LOAD Number,
If(SubStringCount(Comment,'""') > 0,TextBetween(Comment,'"','""') ,Replace(Comment,'"','')) as Comment,
If(SubStringCount(Comment,'""') > 0,Text(Trim(PurgeChar(SubField(Comment,'""',2),',"'))),Text(Room)) as Room

FROM SourceData;

[mfolmar]

This doesn't produce the desired output. 

The Room for Record #1 should not be 'moved to basement', it should be 104, and the Room for Record #3 should be 750. 

Is there a way I can modify what you've come up with to produce those results? 

[BrunPierre]

Not according to the output table above.

Anyways, that would mean loading the field as it is.

LOAD Number,
If(SubStringCount(Comment,'""') > 0,TextBetween(Comment,'"','""') ,Replace(Comment,'"','')) as Comment,
If(SubStringCount(Comment,'""') > 0,Text(Trim(PurgeChar(SubField(Comment,'""',2),',"'))),Text(Room)) as Room,
Room

FROM SourceData;

[mfolmar]

I may not be explaining it correctly, but the data that should all be in the field 'Comment' is getting broken up into the next field. I'd like to know if there is a way to force that data to load in the correct field ? 

[marcus_sommer]

AFAIK there are no ways to load such invalid data from a text-file within a single step just by specifying the file-format and/or in combination with data-manipulation with functions like subfield/textbetween.

If possible try to load this information from an origin Excel or a data-base source - maybe you could get an appropriate access.

If not you need to apply a multi-step approach. One way could be to load the file at first without any delimiter in the fixed mode. Now the entire record is within a single field - and on this you could do various data-cleaning (counting, detecting and removing the garbage). Afterwards the table might be stored as text-file again or you load from_field.

Another method would be to load the file with a delimiter and applying afterwards various checks on the field-content in regard to the previous records - using interrecord-functions like previous() and peek() to detect and repair the wrong ones.

Depending on the data it could become quite hard to correct the garbage data ...

[mfolmar · Accepted solution]

FYI in case anyone reads this in the future, Qlik development corrected the error and rolled the solution out yesterday.