QlikSense script change content of csv file upon loading

[SebastienPreau]

Hi everyone

We have a very strange issue

We try to import in QlikSense a csv file (around 500.000 fields)

Here are the data as shown by Notepad++

Here is the script

LOAD
NFRN_ID_INT,
NFRN_TYPE,
NFRN_MOTF_CODE,
NFRN_FRNS_CODE,
NFRN_FORC,
NFRN_NIVE,
NFRN_NIVP,
NFRN_CODE,
NFRN_COD1,
NFRN_COD2,
NFRN_COD3,
NFRN_COD4,
NFRN_COD5,
NFRN_LIBE,
NFRN_APPL,
NFRN_DFIN
FROM [lib://Data/Share\FR\REFERENTIELS\PRODUITS\PFRN\NFRN.csv]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

And here is what we get :

I can't figure out how the extra 0 in front of 44 just appeared out of the blue

Any idea ?

PS : tested on versions September 2018 and November 2018

[marcus_sommer]

I think I would add a recno() and isnum(NFRN_COD1) and len(NFRN_COD1) to the load-statement to check the records and the values a bit more closely. Further thoughts go to the codepage and the msq-statement within the fileformat which might not working like expected.

- Marcus

[Wlad_Masi]

Sebastien do you mind sending me a csv with a couple of lines so I can check it here, and what Qlik Sense version you are using there?

[Vu_Nguyen · Accepted solution]

Hi Sebastien,
Check if value "044" does appear in field NFRN_COD1 before line 81 in your csv file.
There is a known behavior that when multiple rows having the same numerical value appears in the same column (for example 044, 44, 00044), Qlik engine will take whatever appears first to be the display value.
Check this article for more discussion: https://support.qlik.com/articles/000003759
Hope this helps!

[SebastienPreau]

Hy,

I think you have fund the reason

044 appears upstream of the 47881th record in the same column

44 appears upstream (36717th records) but never in this column before 47881.

If it does solve the issue, at least it explain the pb.

Meanwhile, we seems to have found the work around :

text(NFRN_COD1) AS NFRN_COD1

seems to avoid the pb. But I'm not sure yet that there is no side effect...

[SebastienPreau]

OK, I checked the article after posting, so text() is the solution, my only concern being : does it suppress 0 in 044. So far it seems ok.

[SebastienPreau]

The file was big so i was looking for a solution for file transfer, but as it is resolved, i won't send it.
Thanks anyway for the proposal