topic Re: Load script : CSV file with single quote in numeric field in App Development

Load script : CSV file with single quote in numeric field

pattyccia — Fri, 01 Dec 2023 09:07:44 GMT

Hi all,

I have a load script of an csv file with a numeric field (price) where , if there isn't value, there is a single quote.

An example of field in csv:

How I can replace the single quote with nothing?

I tried with

LOAD
PurgeChar(PrezzoListino, chr(39)) as PrezzoListino,

.....

but doesn't work.

I tried also with:

LOAD
replace(PrezzoListino, chr(39),'') as PrezzoListino,

but doesn't work.

Thanks for your help.

Patrizia

Re: Load script : CSV file with single quote in numeric field

PrashantSangle — Fri, 01 Dec 2023 09:19:37 GMT

it is not working might be it is not single quote.

instead of purgechar() try using keepchar()

try below

KeepChar (PrezzoListino,'0123456789' )

Regards,

Prashant Sangle

Re: Load script : CSV file with single quote in numeric field

pattyccia — Fri, 01 Dec 2023 10:25:34 GMT

Thanks, it works only if there is one field with single quote.

If I have for example following field with 2 field (FieldB and FieldC) with single quote ,this is the situation:

example csv:

FieldA;FieldB;FieldC;PrezzoListino;DataListino;FieldD;FieldE
20191201;';';216732.00;20220615;4V14F9;IB

In Load script:

Load
FieldA,
KeepChar (FieldB,'0123456789' ) as FieldB,
KeepChar (FieldC,'0123456789' ) as FieldC,
KeepChar (PrezzoListino,'0123456789.' ) as PrezzoListino,
DataListino,
FieldD,
FieldC,

....

In data preview on Qlik appear in this way, the values in the subsequent fields are misaligned:

Thank you very much.

Patrizia

Re: Load script : CSV file with single quote in numeric field

PrashantSangle — Fri, 01 Dec 2023 11:16:14 GMT

is it possible you to share data file?

Re: Load script : CSV file with single quote in numeric field

marcus_sommer — Fri, 01 Dec 2023 12:23:30 GMT

Maybe there is no single quote as a value else it might be a kind of replacement for nothing respectively a meta-data control char. Means if you want in Excel to make a number to be a string you could change the cell-format or applying any string-function or just adding a single quote before the number. Within the normal Excel view you won't see this extra char - only if you goes directly in the cell it will be displayed.

Therefore you may try to load this field directly without any transformation or checking against a numeric content and adding a default-value if it's not, maybe something like this:

alt(PrezzoListino, null()) as PrezzoListino

Re: Load script : CSV file with single quote in numeric field

pattyccia — Fri, 01 Dec 2023 13:02:22 GMT

Hi Prashant,

I send file in attached.

Thank you very much.

Patrizia

Re: Load script : CSV file with single quote in numeric field

PrashantSangle — Fri, 01 Dec 2023 13:20:10 GMT

I tried with Replace()

Replace(PrezzoListino,chr(39),'') as PrezzoListino_new,

it is working

Re: Load script : CSV file with single quote in numeric field

PrashantSangle — Fri, 01 Dec 2023 13:27:06 GMT

LOAD
FieldA,
FieldB,
Replace(FieldB,chr(39),'') as FieldB_new,
FieldC,
Replace(FieldC,chr(39),'') as FieldC_new,
PrezzoListino,
Replace(PrezzoListino,chr(39),'') as PrezzoListino_new,
DataListino,
FieldD,
FieldE,
FieldF
FROM [lib://data/SmpleData.csv]
(txt, utf8, embedded labels, delimiter is ';', no quotes);

Re: Load script : CSV file with single quote in numeric field

pattyccia — Fri, 01 Dec 2023 13:44:40 GMT

I probably thought the problem was just the field "PrezzoListino" when I wrote this post.
Now the field "PrezzoListino" is ok but the problem seems to be the following:

In case there are two consecutive fields with single quote, I have for these records that the values in the subsequent fields are misaligned:

Are you also experiencing the same problem?

Tks

Re: Load script : CSV file with single quote in numeric field

PrashantSangle — Fri, 01 Dec 2023 14:19:37 GMT

@pattyccia , I already saw that check my second reply after. See highlighted text in bold will do the trick for you

Re: Load script : CSV file with single quote in numeric field

marcus_sommer — Fri, 01 Dec 2023 14:22:47 GMT

Make sure that msq is disabled within the file-format, like suggest from @PrashantSangle per:

(txt, utf8, embedded labels, delimiter is ';', no quotes);

Re: Load script : CSV file with single quote in numeric field

pattyccia — Fri, 01 Dec 2023 14:28:33 GMT

Sorry, I was probably replying to you when it arrived 😅

Thank you very much... it works fine!

Patrizia