topic How to automatically load .xlsx file into Qlikview in QlikView

How to automatically load .xlsx file into Qlikview

Sun, 13 Nov 2011 09:48:26 GMT

I've tried below script and it works for .xls :

SET MODELED_Folder="C:\Users\Administrator\Desktop\MY_Report";

for each file in filelist('$(MODELED_Folder)\*.xls')

load*

from $(file)(biff, embedded labels, table is Sheet1$);

next file

However, I cannot change it for .xlsx file.

I tried to change the format to "ooxml", but it doesn't work.......

Anybody has the solution for .xlsx?

Will be much appreciated.... thank you

Re: How to automatically load .xlsx file into Qlikview

Miguel_Angel_Baeyens — Sun, 13 Nov 2011 18:16:40 GMT

Hi,

Check the following code, it's very similar to yours but the path in the first variable is single quoted instead and the loop varies a bit:

SET MODELED_Folder = 'C:\Files\QV';

FOR EACH file IN FILELIST('$(MODELED_Folder)\*.xlsx')
  ExcelData: 
  LOAD Field1, 
            Field2
  FROM [$(file)] (ooxml, embedded labels, table is Sheet1);
NEXT

I'm using a very dummy Excel spreadsheet with just two columns, that will concatenate automatically to the previous XLSX file loaded.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: How to automatically load .xlsx file into Qlikview

Mon, 14 Nov 2011 04:57:11 GMT

Hi Miguel,

If i want to load Sheet2 and Sheet3 automatically then how do i do ??

Can you explain me.

Thanks & Regards,

Antony.

Re: How to automatically load .xlsx file into Qlikview

Mon, 14 Nov 2011 19:22:49 GMT

Hello!

Thanks for your help a lot!

By the way, I tried the above script, single quote for the first variable and also the bracket here [$(file)].

Still it doesn't work because of syntax error ..

My qlikview is Version 10. But don't think that's not the exact problem.....

I wonder what is supposed to be the correct format of ooxml for .xlsx file.

Anybody has the solution?

Re: How to automatically load .xlsx file into Qlikview

Miguel_Angel_Baeyens — Tue, 22 Nov 2011 17:56:37 GMT

Hi Antony and sorry for not answering sooner,

I'd try the following script

SET MODELED_Folder = 'C:\Files\QV';

FOR EACH file IN FILELIST('$(MODELED_Folder)\*.xlsx')
  FOR i = 2 TO 3
    ExcelData: 
    LOAD Field1, 
         Field2
    FROM [$(file)] (ooxml, embedded labels, table is Sheet$(i));
  NEXT
NEXT

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: How to automatically load .xlsx file into Qlikview

Miguel_Angel_Baeyens — Tue, 22 Nov 2011 17:58:07 GMT

Hi there,

I'm using QlikView 10 as well (SR3 update 9061). The example code above works fine in my computer. It might be indeed something related to your Excel drivers. Did you try using and ODBC connection to the Excel instead of the plain file?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: How to automatically load .xlsx file into Qlikview

sudeepkm — Tue, 03 Sep 2013 09:58:10 GMT

Hi Miguel,

I'm trying to load data from XLSX files using a loop and using the sheet number.

I found that while using sheet numbers QlikView is throwing error.

I cannot use ODBC XLS connection and cannot use Sheet Names.

Can you pls help me? It seems the sheet number works for XLS but not for XLSX

for each vFiles in filelist('$(vDataFolder)*.xls')

for i = 1 to 3

LOAD A

FROM [$(vDataFolder)$(vFileName)]

(ooxml, no labels, table is @$(i))