topic Re: How to load EXCEL worksheets and retrieve tab names in QlikView

How to load EXCEL worksheets and retrieve tab names

Mon, 15 Feb 2016 14:14:55 GMT

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?

Re: How to load EXCEL worksheets and retrieve tab names

m_woolf — Mon, 15 Feb 2016 20:38:47 GMT

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

Re: How to load EXCEL worksheets and retrieve tab names

settu_periasamy — Tue, 16 Feb 2016 03:15:51 GMT

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;

Re: How to load EXCEL worksheets and retrieve tab names

Tue, 16 Feb 2016 03:45:05 GMT

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

Re: How to load EXCEL worksheets and retrieve tab names

Tue, 16 Feb 2016 03:47:55 GMT

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

Re: How to load EXCEL worksheets and retrieve tab names

Tue, 16 Feb 2016 06:14:45 GMT

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

Re: How to load EXCEL worksheets and retrieve tab names

settu_periasamy — Tue, 16 Feb 2016 08:12:38 GMT

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

Re: How to load EXCEL worksheets and retrieve tab names

settu_periasamy — Wed, 17 Feb 2016 05:32:01 GMT

Hi Susan,

Find Attached the Updated Version..

Re: How to load EXCEL worksheets and retrieve tab names

jagan — Wed, 17 Feb 2016 08:41:22 GMT

HI,

Check this link

Load all Excel files and all sheets in a folder

Regards,

Jagan.

Re: How to load EXCEL worksheets and retrieve tab names

Wed, 17 Feb 2016 12:29:19 GMT

Thank you!