Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
s10157754
Contributor III

Dynamic loading of excels files into Qlikview

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!

1 Solution

Accepted Solutions
Highlighted

Re: Dynamic loading of excels files into Qlikview

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

View solution in original post

29 Replies
Highlighted
pradosh_thakur
Honored Contributor II

Re: Dynamic loading of excels files into Qlikview

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

Capture.PNG

Learning never stops.
Highlighted

Re: Dynamic loading of excels files into Qlikview

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

View solution in original post

Highlighted
s10157754
Contributor III

Re: Dynamic loading of excels files into Qlikview

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?

1.PNG

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

Highlighted
pradosh_thakur
Honored Contributor II

Re: Dynamic loading of excels files into Qlikview

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.


Learning never stops.
Highlighted

Re: Dynamic loading of excels files into Qlikview

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

Capture.PNG

Highlighted
s10157754
Contributor III

Re: Dynamic loading of excels files into Qlikview

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

Highlighted
s10157754
Contributor III

Re: Dynamic loading of excels files into Qlikview

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

Highlighted
s10157754
Contributor III

Re: Dynamic loading of excels files into Qlikview

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

Highlighted
pradosh_thakur
Honored Contributor II

Re: Dynamic loading of excels files into Qlikview

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

Learning never stops.