topic How to automate in QlikView

How to automate

rohit214 — Thu, 02 Feb 2012 09:35:25 GMT

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

How to automate

rohit214 — Thu, 02 Feb 2012 10:12:04 GMT

someone???

How to automate

rohit214 — Thu, 02 Feb 2012 10:36:30 GMT

plese help me its very urgent

Re: How to automate

Thu, 02 Feb 2012 10:56:45 GMT

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.

How to automate

rohit214 — Thu, 02 Feb 2012 11:19:50 GMT

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

Re: How to automate

Thu, 02 Feb 2012 11:35:57 GMT

check attachment.

Re: How to automate

rohit214 — Thu, 02 Feb 2012 12:54:06 GMT

thanks for your reply but it it still not working

please find my execl file

thanks

rohit

Re: How to automate

Thu, 02 Feb 2012 17:00:45 GMT

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