Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to automatically load .xlsx file into Qlikview

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

6 Replies
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

Not applicable
Author

Hi Miguel,

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

Can you explain me.

Thanks & Regards,

Antony.

Not applicable
Author

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
Specialist III
Specialist III

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