Load script : CSV file with single quote in numeric field

[pattyccia]

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

[PrashantSangle]

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

[pattyccia]

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

[PrashantSangle]

is it possible you to share data file?

[marcus_sommer]

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

[pattyccia]

Hi Prashant,

I send file in attached.

Thank you very much.

Patrizia

[PrashantSangle]

I tried with Replace()

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

it is working

[PrashantSangle · Accepted solution]

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);

[pattyccia]

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

[PrashantSangle]

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