Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
MohitSharma3110
Contributor II
Contributor II

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

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,

 

1 Solution

Accepted Solutions
rwunderlich
Luminary Alumni
Luminary Alumni

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

 

 

View solution in original post

2 Replies
rwunderlich
Luminary Alumni
Luminary Alumni

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

 

 

View solution in original post

MohitSharma3110
Contributor II
Contributor II
Author

@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. 🤐