Text fields containing numbers with leading zeros are imported as numeric though in Excel they are declared text

[bmarin]

Hi,

I have the following problem. I am importing an Excel file that has 2 columns specifically formatted as text (I used the function TEXT) containing numbers with leading zeros, so that the field length is 5 characters (for instance the FIPS for US Counties), and Qlik though in the preview is showing me the leading zeros, it is importing the sheet without them and turns them into numeric, though I need them to be let as text.

Is there any possibility to let Qlik "know" that that field has to remain text, and not be turned into numeric, and thus the leading 0s remain too.

Thank you

[rwunderlich]

Are you using the Text() function in your Load statement?

-Rob

[QGTFS]

One way to manipulate your text to remove leading zero's could be to use substring and to detect if zeros are leading ( index(text,'0')>0).

Another way might be to convert your text to numeric and then back to text.

[bmarin]

No, I just load the Excel sheet as it is. The cells that are converted automatically by Qlik were obtained using the Excel function TEXT(cell, '00000'), so I was expecting that Qlik respects this.

[bmarin]

But I need the leading zeroes, and I need the field to stay as text as it was declared on the Excel sheet

[QGTFS]

Sorry, I misunderstood. There's a way to retain your format although not text, but with NUM(YourField,'00000')

[bmarin]

I saw in other discussions about the function NUM, however I would need that the field stays text simply, without any conversion of any kind. There is no possibility in Qlik to specify visually if a field has to have a certain type, in this instance text?

[QGTFS]

you can then convert it again back to text TEXT(NUM()),  the other way in the script is from @rwunderlich  :

https://community.qlik.com/t5/App-Development/Is-it-possible-to-load-all-fields-in-text-format-while...

[bmarin]

I find that it would be better to have all fields imported as text, and thus there is no loss of data, no? I find that some fields are imported as text, as the cell type indicates, and some are converted to numeric , thus loosing the leading zeros. In geographical applications, indicators are in text format and have leading 0s. For instance the County FIPS are a chain of 5 characters, and it is important that they stay like that, i.e. text (chain of characters).

[QGTFS]

Unfortunately, I haven't found much more than this :

https://community.qlik.com/t5/QlikView-App-Dev/Loading-text-data-but-converted-to-numbers-from-excel...

It seems there's no real way around it. You can check the format qlikview use to load your field also.

https://community.qlik.com/t5/QlikView-App-Dev/Checking-to-see-if-the-data-is-text-or-a-number-in-pe...

Other than the previously mentioned I don't know other solutions that may work.