Load multiple excel file with different name but same structure.

[jsingh71]

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.

[datanibbler]

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

[avinashelite]

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

[jsingh71]

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.

[jsingh71]

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.

[noman212]

Please try this script

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

EOM_HISTORY:

Load

A ,

B,

    C ,

    D,

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

[jsingh71]

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

[avinashelite]

Hi Jsingh,

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

[jsingh71]

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.

[shane_spencer]

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?