Workbook ReadRange cannot read filtered excel data

Help Studio
1

Hi,

I am receiving an xlsx-File from another application. It contains a header line with filters set starting in row 3, followed by the actual data from row 4 onwards, like this:

grafik
grafik1160×241 14.3 KB

When reading it with workbook read range, it is not able to correctly read the headers, neither with option ‘addHeaders’ set nor without. It does not matter from which row on I read the data, the resulting datatable always looks like this, with generic header names like column1, column2 …:

grafik
grafik902×396 20.2 KB

If I let him start to read from row 2 on it looks even weirder, he adds the header row twice:

grafik
grafik877×474 28.6 KB

Only when I manually remove the filters from the excel file and copy data+header to a new blank workbook, read range is able to correctly read it to a datatable.

Excel read range does it correctly out of the box, but excel is not installed on the system the robot is designated to run. To me it looks like a bug. Any idea on how to solve this, i.e. read the correct values from row 3 instead of generic header names?

Thanks
Sebastian

1 Like
2

Hey @Robodev,

I guess the Excel has a separate table inserted with headers.

But that shouldn’t be an issue.

Just using read range should work fine.

Else, you can go with below steps,

  1. Use Get Table Range and first get the range of the table
  2. Use Read Range with range value got from step 1

If you still face issue, share the excel with sample data.

Hope this helps.

Thanks :slight_smile:

3

Hi,

no, the header cells are plain text, the workbook has only one sheet and I cannot find a table anywhere. But it has special filter:

grafik
grafik1653×465 39.9 KB

1 Like
4

@Robodev,

Is it possible for you the sheet or a look alike sheet

Will be easy for debug.

Thanks :slight_smile:

5

Regarding table, When to select any cell on that range. You will get special menu called table options at the top bar.

There you can find table name and other details.

6

You’re right there is a table, I found it. I tried your approach with get table range, it points to the same range that I read previously, but the result is still wrong:

grafik
grafik1200×373 22.2 KB

I wanted to prepare the excel-file so I could share it here, but as soon as I replace the content cells with anonymized data and save the file, read range reads it correctly the next time.

1 Like
7

@Robodev, Is your issue resolved now ?

Thanks :slight_smile:

8

Unfortunately not. The file is generated per robot run, so it will always have this issue. And I see no way to ‘repair’ the file with the robot, since there is no excel installed I cannot open Excel and let the robot change and save it and whenever I use readRange I don’t get the correct headers ;-(.

1 Like
9

@Robodev, Try using table range method and check options in read range like preserve format & use filter.

Thanks :slight_smile:

10

I tried several combinations of read range/table range with and without header and preserve format none lead to an acceptable result. But in the meantime I convinced IT to install Excel on the machine, so that is going to be the solution.

If IT had refused to install Excel I would have used the ‘brute force’-method: completely skip the header line and hardcode the headers instead (as they will be stable for at a longer period)

1 Like