How to automatically load .xlsx file into Qlikview

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

Hi Miguel,

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

Can you explain me.

Thanks & Regards,

Antony.

[#4]

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?

[Miguel_Angel_Baeyens]

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

[Miguel_Angel_Baeyens]

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

[sudeepkm]

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))

next

next