How to automate

[rohit214]

Hi all,

I have xls database.In thar xls datadase each xls file name is diffrent and in that xls file 2 tables are there they also have diffrent name.

Exm. xls filename-Aug-2010.xls in this file 2 tables are there Aug(1-15) &Aug(16-30)

So how do i automate .

please share the script

thanks&regard

rohit

[rohit214]

someone???

[rohit214]

plese help me its very urgent

[#4]

Hi

You can find this link useful

http://qlikviewnotes.blogspot.com/2008/09/loading-multiple-excel-sheets.html.

You can also look aat the attached example.

[rohit214]

Hi

it is working for only one sheet which have muliple tables

but in my senario i have lot of sheets with having multiple tables

Thanks

rohit

[#6]

Hi

check attachment.

[rohit214]

Hi

thanks for your reply but it it still not working

please find my execl file

thanks

rohit

[#8]

Hi, i made some changes on Prezemek's file and it loads data. This is the script:

set ErrorMode=0;

set vSource = '.\';

for each varFile in FileList(vSource&'*.xlsx')

    let vFileName = subfield('$(varFile)','\',-1);

    let ODBC_Path = '[Excel v12;DBQ=' & '$(vSource)' & '$(vFileName)' & ']';

    let File_Path = '[' & '$(vSource)' & '$(vFileName)' & ']';

    ODBC CONNECT TO $(ODBC_Path);

    tables:   

        SQLtables;

    DISCONNECT;

    FOR i = 0 to NoOfRows('tables')-1

        LET vsheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

        let sheetName=purgeChar(trim(vsheetName),'$');

        IF NOT wildmatch('$(vsheetName)', 'Sheet*') THEN

            data:

            LOAD *,

            purgechar(trim('$(sheetName)'),'$') as SheetName FROM $(File_Path)

            (ooxml, embedded labels, table is [$(sheetName)])

            ;

        END IF

    next

    DROP TABLE tables;   

next varFile

set ErrorMode=1;

Hope this can help you

Regards