Import Specific Range of Cells from Excel

[jdean1012]

When using an Excel workbook as a data source, is it possible to specify a specific table array or range of cells to read?  The source file is highly formatted, and I only want some of what it contains.

I want to read something like D5:AA50, for example.

THANKS

[Lisa_P]

Qlik help has a suggestion:

Use named areas

If you only want to read a part of a sheet, you can select an area of columns and rows and define it as a named area in Microsoft Excel. Qlik Sense can load data from named areas, as well as from sheets.

Typically, you can define the raw data as a named area, and keep all extra commentary and legends outside the named area. This will make it easier to load the data into Qlik Sense.

[jdean1012]

Thanks for the suggestion!  Unfortunately, I have no control over the source spreadsheet format and need to consume them as-is.

For understanding, what is the syntax for selecting only a named area?

[Or]

It's a bit clunky, but you can use filters for this. Should be:

(ooxml, no labels, table is Sheet1, filters(
Remove(Col, Pos(Top, 4)),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 2)),
Remove(Col, Pos(Top, 1))

Remove(Row, Pos(Top, 4))

Remove(Row, Pos(Top, 3))

Remove(Row, Pos(Top, 2))

Remove(Row, Pos(Top, 1))

));

This is one of those things that are actually easy to do in QlikView, since there's a wizard for it, but is a bit of a lost art in Qlik Sense since it lacks a matching wizard. Note that Qlik does not recommend using these functions in Sense, but there's no explanation as to why this is the case or whether there's a Sense-specific option that does the same thing:

https://help.qlik.com/en-US/sense/May2021/Subsystems/Hub/Content/Sense_Hub/Scripting/functions-state...