topic Re: Loading from Multiple excel files in QlikView

Loading from Multiple excel files

Mon, 06 Jul 2015 06:39:23 GMT

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

Re: Loading from Multiple excel files

Siva_Sankar — Mon, 06 Jul 2015 06:46:06 GMT

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

Re: Loading from Multiple excel files

Mon, 06 Jul 2015 06:50:03 GMT

Hi,

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

Re: Loading from Multiple excel files

jagan — Mon, 06 Jul 2015 06:53:50 GMT

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.

Re: Loading from Multiple excel files

Siva_Sankar — Mon, 06 Jul 2015 06:55:59 GMT

Kyaw,

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

Re: Loading from Multiple excel files

Mon, 06 Jul 2015 07:03:41 GMT

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

Re: Loading from Multiple excel files

Mon, 06 Jul 2015 07:07:40 GMT

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

Re: Loading from Multiple excel files

jagan — Mon, 06 Jul 2015 07:07:50 GMT

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.

Re: Loading from Multiple excel files

senpradip007 — Mon, 06 Jul 2015 07:24:53 GMT

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

Re: Loading from Multiple excel files

jagan — Sat, 18 Jul 2015 00:28:14 GMT

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.