[Advanced Excel] Manually read excel rows, skip first rows and place the data rows in a record list

Forge Component
(52)
Published on 27 Aug by Hanno
52 votes
Published on 27 Aug by Hanno

Using the "Excel to Record List" requires an excel in which the header is on row 1 and matches the record structure.

I have an excel with some aggregated data in the first rows, and after that the Header row comes and the data rows.  So the header is not on row 1.

I want to place the data rows in a record list.

It seems to be possible to do this with the Advanced-Excel component, but i don't know how. I read the documentation and tried to understand the demo, but I still did not find the way to do this.

Hans Hol wrote:

Using the "Excel to Record List" requires an excel in which the header is on row 1 and matches the record structure.

I have an excel with some aggregated data in the first rows, and after that the Header row comes and the data rows.  So the header is not on row 1.

I want to place the data rows in a record list.

It seems to be possible to do this with the Advanced-Excel component, but i don't know how. I read the documentation and tried to understand the demo, but I still did not find the way to do this.

Hi Hans,

As of my understanding you want to retrieve the data from excel and read one by one column. Right?

If i am correct please use following methods in Advance Excel Component to achieve this.

1. Workbook_Open

2. Worksheet_Select

3. Cell_Read (this method to read data from excel column)

Please let us know if you have any queries or concern. Otherwise i will provide demo for you.

For your Reference 

https://hannojs.outsystemscloud.com/AdvancedExcelDemo/Index.aspx

Hope this help

Best Regards,

Amit Verma


Hi Hans,

You can click the Try now button of this component.

In there you will find Read the value of a specific cell.

When you download the component including the demo you can see how to do this.

After that its just the regular way of iterating through the rows and columns.

Thank you all for your feedback!

suppose I have an excel with 30 columns, and the header is on row 10.


So if I understand you correctly, this is what to do:

Set a CellRow variable to 11

Perform 30 x Cell_read to get all the values of row 11

Append a record with those values to the recordlist

Increase the CellRow variable with 1 and start reading (30 x ) the values of row 12

etc.

Hans, yes. At the moment, unfortunately, it is quite cumbersome. I will look at the possibility of extending the component to make it easier to read the data as you specified.