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

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

bmarin — Mon, 19 Jun 2023 19:39:27 GMT

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

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

rwunderlich — Mon, 19 Jun 2023 21:23:53 GMT

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

-Rob

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

QGTFS — Tue, 20 Jun 2023 07:29:28 GMT

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.

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

bmarin — Tue, 20 Jun 2023 13:25:48 GMT

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.

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

bmarin — Tue, 20 Jun 2023 13:28:56 GMT

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

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

QGTFS — Tue, 20 Jun 2023 13:32:55 GMT

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

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

bmarin — Tue, 20 Jun 2023 13:35:18 GMT

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?

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

QGTFS — Tue, 20 Jun 2023 13:39:27 GMT

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-reading/td-p/1795731

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

bmarin — Tue, 20 Jun 2023 14:42:12 GMT

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

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

QGTFS — Tue, 20 Jun 2023 15:24:50 GMT

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/m-p/524067

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-peek/m-p/735932/highlight/true

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

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

bmarin — Tue, 20 Jun 2023 16:14:11 GMT

Thank you very much.

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

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

rwunderlich — Tue, 20 Jun 2023 20:07:01 GMT

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

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

bmarin — Tue, 20 Jun 2023 20:23:43 GMT

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.

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

rwunderlich — Tue, 20 Jun 2023 22:04:28 GMT

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-interpretation.htm

-Rob

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

bmarin — Fri, 23 Jun 2023 13:19:27 GMT

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.

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

LynLuyt — Fri, 25 Aug 2023 06:49:10 GMT

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

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

rwunderlich — Mon, 04 Sep 2023 15:06:23 GMT

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