topic Re: Load multiple excel file with different name but same structure. in QlikView

Load multiple excel file with different name but same structure.

jsingh71 — Wed, 09 Jul 2014 07:37:46 GMT

Hi everyone,

I need to load multiple excel file with different name but same structure.

earlier I used following script and its working fine.

For Each File in 'First,'Second','Third'

Let vFileName = File;

Let vPath = '[//ABC..com/project/MDM9/Docs/CS'&vFileName&'.xlsm](ooxml, no labels, table is [Sheet_Name])';

Temp:

Load

FROM

$(vPath);

Next;

But now i want to load 20 files instead of three files which I load earlier, so please let me know is there any dynamic way to do this without giving the name of each excel file name in ForEach loop.

Re: Load multiple excel file with different name but same structure.

datanibbler — Wed, 09 Jul 2014 07:41:32 GMT

Hi,

are those files all in the same directory? Do the names follow some rule (do the file_names contain the calendar_week or so)?

=> In that case, you can build a LOOP.

I haven't tried that, but you can find out how many qvw files are there in a certain directory and you can build a LOAD statement going through them one by one.

HTH

Best regards,

DataNibbler

Re: Load multiple excel file with different name but same structure.

avinashelite — Wed, 09 Jul 2014 07:45:15 GMT

Hi Jsingh,

If you want to load many excel files with common fields in a folder you can try this...

Load *

select*

from //path/*.xlsm

It will load all the Excel files in that folder

Re: Load multiple excel file with different name but same structure.

jsingh71 — Wed, 09 Jul 2014 07:48:29 GMT

All files are same directory but they didn't contain any number( like calender_week and so on). Earlier I provide file names in ForEach loop itself then its working, now I dont want to add name manually.

Re: Load multiple excel file with different name but same structure.

jsingh71 — Wed, 09 Jul 2014 07:50:25 GMT

Hey Avinash, this will work when we are loading excel files locally but in my case I am pulling from server. So this was not working.

Re: Load multiple excel file with different name but same structure.

noman212 — Wed, 09 Jul 2014 07:59:31 GMT

Please try this script

For each vPath in filelist ('*.xls')

EOM_HISTORY:

Load

A ,

C ,

From $(vPath) (biff, embedded labels, table is [rptCUMTIMESHEETAll.rpt$], filters(Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Information Systems')))));

Re: Load multiple excel file with different name but same structure.

jsingh71 — Wed, 09 Jul 2014 08:03:00 GMT

I already explain this plz read my previous comment. * will not work if you are pulling data from server.

Re: Load multiple excel file with different name but same structure.

avinashelite — Wed, 09 Jul 2014 08:04:27 GMT

Hi Jsingh,

I think It should work, please check with the folder path once.

Re: Load multiple excel file with different name but same structure.

jsingh71 — Wed, 09 Jul 2014 08:12:14 GMT

I already tried it is showing error msg BAD ZIP FILE. but what code I mentioned in my original post its working fine but need to mention file name. that I want to avoid.

Re: Load multiple excel file with different name but same structure.

shane_spencer — Wed, 09 Jul 2014 08:53:49 GMT

I've seen a few posts about this error now, often when the files are on a SharePoint. Are the files on a normal share or on sharepoint?

Re: Load multiple excel file with different name but same structure.

noman212 — Wed, 09 Jul 2014 09:00:15 GMT

1please specify path as

load '.\\192.168.1.2\Sharing\Foldername';

Re: Load multiple excel file with different name but same structure.

jsingh71 — Wed, 09 Jul 2014 09:00:39 GMT

I only asked this question before that but any how I loaded files through sharepoint. As I mentioned my earlier reply that time I used file names in ForEach loop, now I want to do dynamically without giving the file names manually. So us there any way to do this?? If you suggest anything its really helpful for me.

Thanks..

Re: Load multiple excel file with different name but same structure.

Anonymous — Tue, 09 Sep 2014 06:16:32 GMT

Hi ,

Use this script if you can keep this set of files in separate directory:

FOR EACH FILE IN FileList('$(DIR)*.xls')

load .......

FROM

$(FILE)

(biff, embedded labels, header is 1 lines, table is Sheet1);

next FILE

if you are having other files also in the same directory, then you have to maintain a common suffix/prefix inorder to identify them. Accordingly syntax will change as follows;FOR EACH FILE IN FileList('$(DIR)Prefix*.xls')

Regards,

Venkat

Re: Load multiple excel file with different name but same structure.

Tue, 09 Sep 2014 08:07:52 GMT

Hi,

I have my QVD files in another server from where I use the script below:

for Each file in '\\10.xxx.xxx.32\d\Developer\Abhishek Sen\Deployment\QVD'

HotelApp:

load *

From $(file)\*.qvd (qvd);

(I just put 'x' to mask the digits in my IP address of the server).