Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
pattyccia
Contributor III
Contributor III

Load script : CSV file with single quote in numeric field

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:

pattyccia_0-1701420872485.png

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

Labels (1)
1 Solution

Accepted Solutions
PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

11 Replies
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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
pattyccia
Contributor III
Contributor III
Author

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:

pattyccia_0-1701426236921.png

Thank you very much.

Patrizia

PrashantSangle

is it possible you to share data file?

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
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
Contributor III
Contributor III
Author

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
pattyccia
Contributor III
Contributor III
Author

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:

pattyccia_0-1701438142774.png

 


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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂