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

[bmarin]

Thank you very much.

I will check those links and let you know how they work for me, OK?

[rwunderlich · Accepted solution]

If you wish to retain the leading zeros, you should use the Text() function in the Qlik Load statement. For example

Load 
    Name, 
     Text(FIPS) as FIPS,
    ...

When loading excel, all data is converted to numeric, when possible, regardless of the excel formatting. 

For numeric values, Qlik uses the first string representation loaded as the display value for a given numeric value. This behavior can make it appear that "some are getting converted and some are not". Kinda of crazy making if you don't understand the rule. 

To demonstrate, try this script:

LOAD *,
  Text(F1) as TextF1
Inline [
ID, F1, F2
a, 001, 99
b, 1, 0099
c, 2, 098
d, 002, 98
];

And the result is:

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[bmarin]

Thank you for your reply.

I will try approach when loading the Excel spreadsheet, and see what happens.

I would be happier though if Qlik would not convert all data as it sees fit, and ask me how the importation should be done. It would be more user friendly.

Having to use scripts in order to load a spreadsheet that is already formatted as it should in order to have the importation done without any headaches is a bit of a disappointment for someone that just started to use Qlik, in terms of user friendliness and ease of use of the tool.

Thank you again, and I will let you know how it goes.

[rwunderlich]

Qlik's interpretation decisions can seem unexpected at first. They do have a dark side and a light side. For example, Qlik is excellent at automatically interpreting dates and storing data efficiently.  Qlik only considers databases to have useful "type" information. Excel is treated like any old text file. The excel formatting is not considered to be type information. Here's the doc on the rules. 

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/number-interpre...

-Rob

[bmarin · Accepted solution]

Your solution works great for files of text type (e.g. CSV), and also in Excel, if the type of cells was not declared properly.

For Excel on the other hand, I found another solution that is more proper to Excel which is to click the whole column to mark it, and right click on the header. Click on "Format cells ...", and select Text. I found out that Excel, for some reason, though the cells were obtained using the function TEXT, they are not completely text.

[LynLuyt]

Hi Rob

I have an Account Number that is mostly numeric, but does also contain leading zeros in other instances. Is there a way I can specify the data type to be text, once at the beginning of the script, or do I have to make it text(AccountNumber) each time I bring in a new piece of data?

Then my second question - I now have text saved as a very small number, so it's showing  something like 1.1798759475, for example. Is there anyway I can get back to the original number or at least part of it?

Thanks 

[rwunderlich]

I believe you have to use text(AccountNumber) each time you load a new value. 

I'm not following the small number question.  Can you post what you think the 1.1798759475 value would look like as the original number?

-Rob