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!

[pradosh_thakur]

My qvw and files are in same folder

try this out

for each File in FileList('*AB*.xlsx')

     Directory;

LOAD A,

     B,

     C,

     FileBaseName() as FileName

FROM

[$(File)]

(ooxml, no labels, table is AB_100)

where Date#(left(FileBaseName(),10),'MM-DD-YYYY')>A

;

Directory;

LOAD A,

     B,

     C,

     FileBaseName() as FileName

FROM

[$(File)]

(ooxml, no labels, table is AB_200)

where Date#(left(FileBaseName(),10),'MM-DD-YYYY')>A

;

NEXT File

[tamilarasu · Accepted solution]

Hi Zhang,

You can also try,

Set vPath = 'C:\Users\Tamil\Desktop\AB';
for each File in FileList(vPath & '\*AB*.xlsx')

for each Sheet in 'AB_100', 'AB_200'

Data:
LOAD A,
B,
C,
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

NEXT

[s10157754]

Dear Pradosh Thakur,

Thanks a ton for your solution! It works like a charm!

May I know is it possible for me to have the new field created just based on the number in the sheets? Like the picture below?

In addition, can I choose to start loading the excel files from a specific date? (Maybe by changing the script for the user to choose the starting date to load?) Thanks a lot for your help!

Best Regards

QianNing

[pradosh_thakur]

for each File in FileList('*AB*.xlsx')

    Directory;

LOAD A,

    B,

    C,

    FileBaseName() as FileName,

    100 as new_field

FROM

[$(File)]

(ooxml, no labels, table is AB_100)

where Date#(left(FileBaseName(),10),'MM-DD-YYYY')>A

;

Directory;

LOAD A,

    B,

    C,

    FileBaseName() as FileName,

200 as new_field

FROM

[$(File)]

(ooxml, no labels, table is AB_200)

where Date#(left(FileBaseName(),10),'MM-DD-YYYY')>A

;

NEXT File

this will do the first requirement. For the second one  we can do the using a variable but i will be able to better guide you if you could elaborate more.

[tamilarasu]

Hi Ziang,

Set vPath = 'C:\Users\Tamil\Desktop\AB';  //Change your path here

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

for each File in FileList(vPath & '\*AB*.xlsx')

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

for each Sheet in 'AB_100', 'AB_200'

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

[s10157754]

Dear Pradosh Thakur,

For example, in stead of looping through all the files in the folder, if user wants to load the data only from 01-02-2018AB onwards. Can we achieve this in script? So next time just by modifying the script, user can choose to load the data from specific date.

Best Regards,

QianNing

[s10157754]

Dear Tamil,

Thanks a lot for your help as well! Your coding looks much simpler and dynamic. Really appreciated a lot for your kind effort!

Best Regards,

QianNing

[s10157754]

Dear Tamil,

I realised that if the user choose to load the data from 01-01-2018, the data should include 12/31/2017 & 1/1/2018 & 1/2/2018 right? But your coding here only gives the data on 12/31/2017. May I know what are the additional coding that I should have? Thanks for help in advance!

Best Regards

QianNing

[pradosh_thakur]

MAY BE THIS .

Set vPath = 'C:\Users\Tamil\Desktop\AB';  //Change your path here

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

for each File in FileList(vPath & '\*AB*.xlsx')

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

for each Sheet in 'AB_100', 'AB_200'

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