Is it possible to load all fields in text format while reading few excel files in Qlik Sense

[MohitSharma3110]

Hi,

Is it possible to load all field values in text format inside a For..each.. loop to read some excel files in Qlik. below is my code where I am reading some excel files with different kind of data in each of them. The issue arises when some of the field values in these files have zeroes appended to them and Qlik removes them while reading and keep it as number. And I need those appended zeroes in those fields values.

For each vFile in FileList('$(vPath_Input_MD_Business)/Excel/*.xlsx')

  let vScript_TableName =  replace(replace(SubField(vFile,'/',-1),'.xlsx',''),'.XLSX','');

  $(vScript_TableName):
  LOAD
      *
  from [$(vFile)](ooxml, embedded labels, table is Sheet1);


next

I need something that should work like below statement:

Load text(*) from excel.xlsx; 

Thanks,

[rwunderlich · Accepted solution]

As long as you know the names of the fields you want to text(), there is a fairly easy way of doing this.  Before loading your files, load a dummy table using text() with any possible field names you want text'd.

TempText:
LOAD
Text(0) as CatNo,
Text(0) as PartNo,
Text(0) as PostCode
AutoGenerate 0; 

// load your files

Drop Table TempText;

If you don't know the names of the fields and just want to apply text to every field, modify your loop to get the field names from each file and build a LOAD text(fieldlist) in a variable, then use the variable in a complete LOAD.

TempFields:
First 1
LOAD *
FROM [$(vFile)] (ooxml, embedded labels, table is Sheet1);

TempStmt:
LOAD
Concat('Text(' & Fname & ') as ' & Fname, ', ') as stmt
;
Load
'[' & FieldName(RecNo(), 'TempFields') & ']' as Fname
AutoGenerate NoOfFields('TempFields')
;
Let vStmt = Peek('stmt');
Drop Tables TempFields, TempStmt;

MyData:
LOAD
  $(vStmt)
FROM [$(vFile)] (ooxml, embedded labels, table is Sheet1);  

Don't share these techniques with anyone. I want to make sure people sign up for my next Advanced Scripting class. 😀

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

[MohitSharma3110]

@rwunderlich That's it. It's always the second case!

Hey Qlik custodians, throw this thread into some deep dark portal. My first question on Qlik community and Rob asked me something. Can't say no that. 🤐