topic Re: import data from multiple excel files in a folder in QlikView

import data from multiple excel files in a folder

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

want to pop a question, if Qlikview has a function to import data from multiple excel files in a particular folder. or we have to take a route of declaring the 10 different files name and then importing it

path names:

\\Nyk\Prime\Product Management\Quant PB\EMEA\FillReports\AMS\20170331

files names:

AMS_Fills_Summary_AA_EU_1_31-03-2017

AMS_Fills_Summary_CA_EU_1_31-03-2017

so on

thanks in advance

Re: import data from multiple excel files in a folder

sunny_talwar — Tue, 11 Apr 2017 12:48:10 GMT

May be like this:

LOAD ....

FROM \\Nyk\Prime\Product Management\Quant PB\EMEA\FillReports\AMS\20170331\AMS_Fills_Summary_*_EU_1_31-03-2017.xlsx

..... ;

Re: import data from multiple excel files in a folder

Tue, 11 Apr 2017 13:36:52 GMT

in order to make AMS and 20170331 flexible, I wrote the below for loop but not working:

for i = 0 to vcount

Let vdate = peek('tddate',i,'datesinfo');
LET vfillxcelFileName = '\\intranet.barcapint.com\dfs-amer\Group\Nyk\Prime\Product Management\Quant PB\EMEA\FillReports\'&vfoldername&'\'&vdate&'\*.csv';

next

please advise

Re: import data from multiple excel files in a folder

jrepucci15 — Tue, 11 Apr 2017 13:41:02 GMT

This works for me:

The * is a wild card and this statement loads in all of the spreadsheets in that path.

FROM $(vExternalSourcePath)\ADP - Hours and Wages by Project\*.xls

Re: import data from multiple excel files in a folder

sunny_talwar — Tue, 11 Apr 2017 13:41:47 GMT

What is the error you are getting? You have only provided partially script... what is vcount, what is tddate? you use just i in the Peek() function, may be use $(i).... There could be any number of things going on and it would be difficult to comment by just looking at this

Re: import data from multiple excel files in a folder

Miguel_Angel_Baeyens — Tue, 11 Apr 2017 13:45:34 GMT

If that does not work it is because some of the variables don't get the values or that the file corresponding to those values do not exist. Use a TRACE variable in the script so you can see how they are loaded.

Also, while playing with dates, the "vdate" variable is getting an unexpected value, like "42836", so you will need to modify the LET statement with the Date() function to provide the same format you have in your file names. Indeed, your folder name, according to the example on your original post has the format YYYYMMDD but the file has the format DD-MM-YYYY

Re: import data from multiple excel files in a folder

Tue, 11 Apr 2017 15:37:08 GMT

hi all,

based on your suggestions: below code is now working:

NoConcatenate
folderscount:
load
count(folders) as foldercount
FROM
$(filename)
(ooxml, embedded labels, table is [Static Data]);

Let vfoldercount = peek('foldercount',0,'folderscount');
drop table folderscount;

dumpingdata:
load * from fillsdata.qvd (qvd) where DateTimeStamp=1;
store dumpingdata into fillsdata.qvd (qvd);
drop table dumpingdata;

let f = 0;
for f = 0 to vfoldercount-1

NoConcatenate
folders:
load
folders
FROM
$(filename)
(ooxml, embedded labels, table is [Static Data]);

Let vfoldername = peek('folders',f,'folders');
drop table folders;
///////////////////////date count and names ///////////////////////////////////////////////
NoConcatenate
datescountinfo:
load distinct TRADE_DATE as counting resident bodata;
NoConcatenate
datescountinfo1:
load distinct counting resident datescountinfo;
drop table datescountinfo;
NoConcatenate
datescountinfo2:
load count(counting) as counting1 resident datescountinfo1;
drop table datescountinfo1;
Let vcount = peek('counting1',0,'datescountinfo2');
drop table datescountinfo2;
let i = 0;
for i = 0 to vcount-1
NoConcatenate
datesinfo:
load distinct TRADE_DATE resident bodata;
NoConcatenate
datesinfo1:
load distinct Date(TRADE_DATE,'YYYYMMDD') as tddate resident datesinfo;
drop table datesinfo;
Let vdate = peek('tddate',i,'datesinfo1');
drop table datesinfo1;
fillsdata:
load * from $(vfillxcelFileName)$(vfoldername)\$(vdate)\*.csv;
Concatenate
load * from fillsdata.qvd (qvd);
store fillsdata into fillsdata.qvd (qvd);
drop table fillsdata;
next
next
endif
fillsdata:
load * from fillsdata.qvd (qvd);

now i am looking to ignore some files from below path in order to reduce run time:

fillsdata:

load * from $(vfillxcelFileName)$(vfoldername)\$(vdate)\*.csv;

any suggestions

thanks in advance

Re: import data from multiple excel files in a folder

Miguel_Angel_Baeyens — Tue, 11 Apr 2017 15:39:26 GMT

Use the DoDir() function to reload one file at a time. It will be slower but allows you to control each file that is loaded:

count files in folder