Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: How to automatically load .xlsx file into Qlikview

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

Re: How to automatically load .xlsx file into Qlikview

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

Re: How to automatically load .xlsx file into Qlikview

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

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

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
Valued Contributor III

Re: How to automatically load .xlsx file into Qlikview

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

Community Browser