Dynamic loading of excels files into Qlikview

[s10157754]

Dear Qlikview Experts,

I had been stuck at this part for a couple of days and I hope you all could help me solve this problem. I want my Qlikview to loop through every excel files that contains 'AB' in the file name in a folder and import into Qlikview. There are multiple sheets in each excel file. However, I only want to import sheets "AB_100" and "AB_200" only (If able to create field based on sheet name would be great! Just create the field based on number will do).

The most challenging part is pulling the data from each sheet. As the file name contains the date information. I would only like to import the data 1 day before the file date. For example: In my 01-01-2018AB excel file, every sheet contains data range from 12/31/2017 (00:00:00) ~ 1/1/2018 (06:00:00). So I only want to import the data for 12/31/2017 if the file date is 01-01-2018AB. (E.g. Import the data for 1/1/2018 if the file date is 01-02-2018AB; Import the data for 1/2/2018 if the file date is 01-03-2018AB    etc...)

I had attached the zip file below for your reference. I am not good at scripting. So any ideas from you all would be greatly appreciated! Thanks a lot in advance!

[tamilarasu]

Hi Zhang,

As mentioned by Pradosh, you can change the below line. Kindly let us know if you have any issues.

Date#(Left(SubField('$(File)','\',-1),10),'DD-MM-YYYY') >= '$(LoadDate)'

[pradosh_thakur]

I think you should follow tamilarasu‌ 's suggestion. It  is better and more dynamic. You can Mark his answer correct and mine/other which helped you as helpful. That will be beneficial for anyone who visits this in future.

[s10157754]

Dear Tamil,

Thanks for the correction and your code works perfectly for .xlsx files. I decided to make use of your code on .xls files in another folder also.

However, this time there are errors appeared as follow:

May I know what is wrong and how could I load .xls files into my Qlikview as well? I had attached a sample of .xls files for your reference. Thanks for your help in advance!

Best Regards

QianNing

[pradosh_thakur]

Did you change this ?

for each File in FileList(vPath & '\*AB*.xls') you need to change the file format from xlsx to xls.

[s10157754]

Dear Pradosh,

I did changed the coding as follow but the error still exist

Set vPath = 'C:\Users\QianNing\Desktop\GE';  //Change your path here

Let LoadDate = Date(Date#('01-01-2018','MM-DD-YYYY'),'MM-DD-YYYY');  //Change your date here

for each File in FileList(vPath & '\*GE*.xls')

If Date#(Left(SubField('$(File)','\',-1),10),'MM-DD-YYYY') >= '$(LoadDate)' then

for each Sheet in 'BC_227', 'BC_226'

Data:
LOAD A,
B,
C,
SubField('$(Sheet)','_',2) as SheetName,
FileBaseName() as FileName,
Date(Date#(left(FileBaseName(),10),'MM-DD-YYYY'),'MM-DD-YYYY') as FileDate
FROM
[$(File)]
(ooxml, explicit labels, table is $(Sheet)) Where  Date#(left(FileBaseName(),10),'MM-DD-YYYY')>A;

Next

ENDIF

NEXT

[pradosh_thakur]

try this

Set vPath = 'C:\Users\QianNing\Desktop\GE';  //Change your path here

Let LoadDate = Date(Date#('01-01-2018','MM-DD-YYYY'),'MM-DD-YYYY');  //Change your date here

for each File in FileList(vPath & '\*GE*.xls')

If Date#(Left(SubField('$(File)','\',-1),10),'MM-DD-YYYY') >= '$(LoadDate)' then

for each Sheet in 'BC_227$', 'BC_226$'

Data:
LOAD @1 as A,
@2 as B,
@3 as C,
SubField('$(Sheet)','_',2) as SheetName,
FileBaseName() as FileName,
Date(Date#(left(FileBaseName(),10),'MM-DD-YYYY'),'MM-DD-YYYY') as FileDate
FROM
[$(File)]
(biff, no labels, table is $(Sheet)) Where  Date#(left(FileBaseName(),10),'MM-DD-YYYY')>@1;

Next

ENDIF

NEXT

please mark the answer which helped you as helpful as it will be beneficial for other who visits in future. You can mark multiple thread as helpful

[tamilarasu]

Hi QianNing,

We have provided the code for "xlsx" file but you are loading "xls"  files in your system. So you need to modify the code accordingly. You should understand the difference between these two file formats before loading it into Qlikview. I have attached a working file. Please have a look and let us know if you still have any issues.

biff Vs. ooxml

@ pradosh_thakur Thank you for helping QianNing.

[s10157754]

Dear Pradosh,

Thanks a ton for your help. Your solution works perfectly for me.

Best Regards

QianNing

[s10157754]

Dear Tamil,

Thanks so much for your explanation, you have cleared my doubts.

Best Regards

QianNing

[s10157754]

Dear Pradosh,

Is it possible for me to just have the number without dollar sign from the sheetname? So it would be nicer at later part when I creating charts to present the data.

Best Regards

QianNing