Load data from Excels with headers in different rows

[#1]

Req:

I have multiple excel files, in each file header may start at different row. How to automatically handle the headers started at different rows in each file.

Thanks

Ranjith

[Gysbert_Wassenaar]

Load the excel files with "No labels" instead of "Embedded labels" and specify the field names manually. You can trying adding a where clause to filter out the non-data lines.

[petter]

QlikView doesn't care which row the header starts at or which column. So you can safely read different sheets with headers and tables in different places. However if you have multiple "tables" in your spreadsheets that might be more of a challenge.

[petter · Accepted solution]

Furthermore what QlikView is considering as a Table can be two different things:

1) The name of a Sheet in an Excel Workbook - here the position of the table doesn't matter. But if you have "disconnected" table-areas QlikView will read the complete area/range which has data and you might not get the headers you need.

2) All the named ranges in an Exel Workbook can be addressed as a Table by name. Then QlikView will be able to load two different Excel logical tables correctly with correct headers too... very nice.

Example:

FOR i=1 TO 3

  TABLE$(i):

  LOAD *

  FROM

  FlexibleHeaderRead.xlsx

  (ooxml, embedded labels, table is TABLE$(i));

NEXT;

This will read three different tables spread across two Sheets in the Excel file. "table is" is now referring to the Named Ranges which I have called TABLE1 TABLE2 and TABLE3.

The named ranges approach demands that you have some controll or can influence those producing the Excel spreadsheets though ...

[#5]

thanks for the reply guys.

I have different Excel files  with different file headers(different columns) located at different rows, So automated process may not work for me. The columns across different Excel files are not unique. I think I need to manually handle it.

[petter]

As I told you already it does not matter which row or column the table starts at. QlikView will only load one table at a time so it considers every LOAD statement as a new / separate file load even if the file is the same but the Sheets or Tables change....

So:

LOAD

   *

FROM [*.XLSX] (.....);

Might actually load all tables for you without a hitch...

*.XLSX will make QlikView try to LOAD all XLSX-files in the directory.

[#7]

Hi sorry for the late reply.

1. here I am loading data from Excel files and creating QVDs using single for-loop. In some Excel files header start at different rows. Even though qlikview doesn't through any error, I  Will not be able to capture the header, which I require.

Thanks for the sharing your knowledge

[RafaCapote]

Hi,

Despite this is an 8 years ago topic, I´ve faced the same issue and found a solution today.

In QlikView, when you load an Excel File on the data load editor it opens the File Wizard

There you can modify the Header Size and Labels.

In order to dynamically modify these settings depending on the file structure is to click on the "Next >" button on the bottom of the window. So you will see the following screen.

When you click on Enable Transformation Step you open a Transformation tool that allow you to create Conditional Deletion for rows. You can create a wide range of different conditions.

As you can see on the above image, you will get a preview of the added conditions on the bottom text box.

After you create your deletion rules you should click on OK across multiple windows.

At the end, for example if your rule is to delete rows where the 5th column is empty, you will get a load statement like the following one:

LOAD * FROM
[YourFile.xlsx]
(ooxml, embedded labels, table is "YourTableName", filters(
Remove(Row, RowCnd(CellValue, 5, StrCnd(null)))
));

Hope it is still helpful for someone.

Note: I don´t know if this advanced File Wizard exists in Qlik Sense, but I´ve confirmed the same syntax is working fine in its data load editor.
It would be great to know how to use it in QS itself