Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Loading from Multiple excel files

Dear Experts,

         I would like to ask you for multiple loading excel files. I have daily downloaded data and I would like to load these data by each date by writing scripts. While I am using filename() funtion and it shows the error.   Here is my script and error message in attached file.I am looking forward your help.

Daily_Tr:

LOAD

     filename() as FileName,

     date(date#(mid(filename(), 12, 8), 'YYYYMMDD'), 'DD MM YYYY') as Tr_Date,

     [Sr No],

     [Ac No],

     [Old Ac No],

     [Opening Balance],

     Debit,

     Credit,

     [Closing Balance]

FROM

(ooxml, embedded labels, table is [20150601]);

Warm Regards,

Kyaw Myo Tun

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Loading from Multiple excel files

Hi,

In one of the file [Sr No]  column is missing, to skip this error load the files like below

Daily_Tr:

LOAD

     filename() as FileName,

     date(date#(mid(filename(), 12, 8), 'YYYYMMDD'), 'DD MM YYYY') as Tr_Date,

    *

FROM

(ooxml, embedded labels, table is [20150601]);

Hope this helps you.

Regards,

Jagan.

9 Replies
Siva_Sankar
Honored Contributor

Re: Loading from Multiple excel files

In File DailyTrans_01062015.xlsx, the  [Sr No] column is missing... check and fix the excel file

Not applicable

Re: Loading from Multiple excel files

Hi,

As per the screen shot the column Sr No is missing in the excel.

MVP & Luminary
MVP & Luminary

Re: Loading from Multiple excel files

Hi,

In one of the file [Sr No]  column is missing, to skip this error load the files like below

Daily_Tr:

LOAD

     filename() as FileName,

     date(date#(mid(filename(), 12, 8), 'YYYYMMDD'), 'DD MM YYYY') as Tr_Date,

    *

FROM

(ooxml, embedded labels, table is [20150601]);

Hope this helps you.

Regards,

Jagan.

Siva_Sankar
Honored Contributor

Re: Loading from Multiple excel files

Kyaw,

Open the file DailyTrans_01062015.xlsx and see the Sr No column is there or not or check if there is any typo.

Not applicable

Re: Loading from Multiple excel files

Hi Jagan,

           Thanks for your help.The error is fixed but it only show a date that I loaded recently. I would like to load further 02062015,03062015 etc. what I've downloaded daily.

Regards,

Kyaw Myo Tun

Not applicable

Re: Loading from Multiple excel files

Hi Jemimah,

      I've already checked my excel file and it seems correct. if I take out that [Sr No] and the error show [Ac No] is not found.

Regards,

Kyaw Myo Tun

MVP & Luminary
MVP & Luminary

Re: Loading from Multiple excel files

Hi,

You are loading only one sheet , refer sheet name highlighted in Bold letters.

Daily_Tr:

LOAD

     filename() as FileName,

     date(date#(mid(filename(), 12, 8), 'YYYYMMDD'), 'DD MM YYYY') as Tr_Date,

    *

FROM

(ooxml, embedded labels, table is [20150601]);

To load multiple sheets you need to look through all the sheets, please refer sample script in the below link

Load Multiple excel sheets using For loop

Regards,

jagan.

senpradip007
Valued Contributor III

Re: Loading from Multiple excel files

You can indeed use ODBC CONNECT 32 to load the table names using SQLTABLES, but I don't think you will be able to wildcard load, rather loop over the files using For Each vFile in FileList(vPath).

Something like:

ForEach vFile in FileList('$(vPath)')

     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

     Sheets:

     SQLTABLES;

     DISCONNECT;

     For i = 0 To NoOfRow('Sheets')

          Let zSheet = Peek('TABLE_NAME, i, 'Sheets');

          ... do something with the file and sheet name, eg

          LOAD ....

          From [$(vFile)]

          (ooxml, no labels, table is [$(zSheet)]);

     Next

Next

MVP & Luminary
MVP & Luminary

Re: Loading from Multiple excel files

HI,

If you got the answer close this thread by giving Correct Answer to the post which helps you in finding the solution.

Regards,

Jagan.