topic Carriage Return & Line Feed in CSV Source "Data error found: No delimiter found after a valid value on quotes mode" in Qlik Replicate

Carriage Return & Line Feed in CSV Source "Data error found: No delimiter found after a valid value on quotes mode"

jonna — Wed, 25 May 2022 07:58:39 GMT

hi folks

really grateful for any help on this one please.

There is an excellent Knowledge Base item here from @KellyHobson explaining how to cater for Carriage Return and Line Feed in the field contents. I have come across the same issue but can't seem to resolve in the same way.

The contents of my source CSV file are shown in a screen grab here:

And the configuration of my CSV Source Endpoint is here:

I am pretty sure I have missed something obvious but have been trying various combinations of the Source Endpoint configuration but can't find a solution.

All / any help would be fantastic.

Many thanks, jonna

ps - the CSV is attached in txt format to permit upload

pps - diagnostic pack attached too

Re: Carriage Return & Line Feed in CSV Source "Data error found: No delimiter found after a valid value on quotes mode"

jonna — Wed, 25 May 2022 08:39:35 GMT

sorry, omitted this earlier:

Re: Carriage Return & Line Feed in CSV Source "Data error found: No delimiter found after a valid value on quotes mode"

Heinvandenheuvel — Wed, 25 May 2022 12:34:25 GMT

I don't see attachments.

Was the csv line line with issue indicated? Is it sure to be part of the screenshot - which notepad++ line(s)?

Best guess is a data line with quote character (double quote ) followed by a field delimiter within that final text column. Such combination looks like the end of the quoted text and that quote character in the text would need to be escaped.

Hein.

Re: Carriage Return & Line Feed in CSV Source "Data error found: No delimiter found after a valid value on quotes mode"

jonna — Wed, 25 May 2022 13:03:33 GMT

hi @Heinvandenheuvel

Thanks very much for prompt reply, txt file and diagnostics attached.

I will take a look at your suggestions and see what changes I can apply - will keep you updated on here.

Thanks, Jonna

Re: Carriage Return & Line Feed in CSV Source "Data error found: No delimiter found after a valid value on quotes mode"

Heinvandenheuvel — Wed, 25 May 2022 14:10:47 GMT

Hmm, not immediately clear to me.

There is more info in the log: Record number: 17, Offset: 127

If one starts counting at 0, then that's the first line with quotes, and a column delimiter within the quotes and a linefeed within the quotes. Still, the 127 does match. I suspect it matches a Replicate internal maximum delimiter size of 128 or something like that.

For your test I would start simple, and with well spaces test records to avoid thinking about 0 or 1 to start counting.

record 5, just a word. KISS
record 10, quoted word
record 15, quoted word with comma
record 20, quoted word with comma and linefeed
....
and eventually the real rows.

No looking the task JSON in the diagnotsics I do NOT see the db_settings for the source which the pictures suggest. Are you running the right task? I think it should look something similar to:

"db_settings": { "$type": "FileSettings", "syntax": "FileSyntax", "csvDelimiter": ",", "csvRowDelimiter": "\\r\\n", "csvStringQuote": "\"",

I do see you are trying to replace CR and LF - maybe try without that action first?

"replacements": [{ "src_codepoint": 10, "trg_codepoint": 90 }, { "src_codepoint": 13, "trg_codepoint": 90 }]

Hein.

Re: Carriage Return & Line Feed in CSV Source "Data error found: No delimiter found after a valid value on quotes mode"

KellyHobson — Wed, 25 May 2022 14:29:08 GMT

Hey @jonna

I saved the attached txt as a .csv, set the record delimiter to \r\n and was able to get the data to replicate to a file target on a full load.

Looking at your diagnostic package I see the record delimiter is set to \n. Can you try again with \r\n?

I've attached my test task json to compare if you would like to import to review.

Let me know if you are still having trouble with running the task.

Best,

Kelly

Re: Carriage Return & Line Feed in CSV Source "Data error found: No delimiter found after a valid value on quotes mode"

jonna — Wed, 25 May 2022 16:17:13 GMT

thanks @Heinvandenheuvel and @KellyHobson - I will make a start on your ideas / suggestions / proposals first thing back at my desk tomorrow.

many thanks, jonna

Re: Carriage Return & Line Feed in CSV Source "Data error found: No delimiter found after a valid value on quotes mode"

lyka — Fri, 27 May 2022 16:08:39 GMT

Hi Jonna,

did any of the solutions worked for you?

Thanks

Lyka

Re: Carriage Return & Line Feed in CSV Source "Data error found: No delimiter found after a valid value on quotes mode"

jonna — Wed, 01 Jun 2022 07:41:17 GMT

hi folks

thanks @lyka and @Heinvandenheuvel and @KellyHobson

I have done a test file and it worked a treat. I haven't, however, tested the final solution just yet - the CSV has a couple of hundred columns and I need to squeeze some time in to complete it. For now I will gratefully give the answers the thumbs up and set the query to ACCEPTED.

Hopefully I wont need any more help, but'll refer back here if that is the case.

Cheers, Jonna