How to load EXCEL worksheets and retrieve tab names

[#1]

Hi all,

I have an excel spreadsheet which contains 2 tabs with different customer names as attached. (I will have more tabs for more customers later on)

I want to be able to retrieve the tab names as part of the load script so I can link the spend with the each customers.

I will also need to use crosstable wizard so Qlikview can ready it.

I will also need a way to convert the date to date format as QlikView is currently reading it as string

The number of vendors will varies as each customer is different and has a different vendor list.

How can I do this?

[m_woolf]

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

[settu_periasamy · Accepted solution]

Hi,

Try this Script:

directory;

For Each vFile in FileList('FY1415_Report.xlsx')

     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
     Sheets:
     SQLTABLES;
     DISCONNECT;
      For i = 0 To NoOfRows('Sheets')-1

          Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);
   CustomerTable:
  CrossTable(Date, Data, 3)
  LOAD '$(vSheet)' as [Tab Name], 
  VENDOR, 
      TYPE, 
      [41821], 
      [41852], 
      [41883], 
      [41913], 
      [41944], 
      [41974], 
      [42005], 
      [42036], 
      [42064], 
      [42095], 
      [42125], 
      [42156]
  FROM
  [$(vFile)]
  (ooxml, embedded labels, table is $(vSheet))
  WHERE(not IsNull(VENDOR)); 
     Next;
     DROP Table Sheets;
Next;


Final:
LOAD [Tab Name],
  VENDOR,
  TYPE as [Project Type],
  Date(Num#(Date)) AS Reporting_Date,
  Date(Floor(Num#(Date)),'MMM-YY') as MonthYear,
  Data
Resident CustomerTable;
Drop Table CustomerTable;

[#4]

Hi,

Thanks for this! it works!

If I need to load multiple excel spreadsheet with the same format but different date on the top. (e.g. another file for FY1314_Report)

Do I need to create another load table and the final table?

Hope you can assist with this one as well.

Thank you

[#5]

Can I also ask why you set the qualifier fields to 3 not 2? Thanks

[#6]

Sorry that was the wrong attachment, can you please use this one as example (This one has different date)

[settu_periasamy]

Because, I Load the '$(vSheet)' as 1st Field. That's why i used 3 as a qualifier. You can remove it, if you don't  want.

In the Meantime, you can try to Load your fields with 'no labels' instead of Embedded Labels.

finally , you can change the Field Name..

[settu_periasamy]

Hi Susan,

Find Attached the Updated Version..

[jagan]

HI,

Check this link

Load all Excel files and all sheets in a folder

Regards,

Jagan.

[#10]

Thank you!