topic RE: Excel with Complex Structure in Product Forum

Excel with Complex Structure

RahulRamesh — Mon, 21 Jun 2021 11:41:00 GMT

Hello,
I have a Usecase to Take values from certain columns of one complex Excel to Another excel. When try moving into the collection it is not identifying certain Columns as the Excel structure is like the below.

Couldn't get the headers of the Excel when try working with Excel neither as Collection.
Don't think getting the values of the column through the column position is a good approach.

Please provide any solution for this to fetch the values based upon the column names.

Thanks in Advance

------------------------------
Rahul Ramesh
------------------------------

RE: Excel with Complex Structure

mkumar407 — Mon, 21 Jun 2021 13:18:00 GMT

Hi Rahul,

I believe you must be knowing how many columns need to extract.
Try using: Get Worksheet Range as Collection and specify Start Cell as A14 (from your picture shared) and End cell as last Column-Row. Set Header As False.
Once data is fetched into collection, it will add auto create columns as Column1,Column2 .. so on.

Later you can use Utility - Collection Manipulation -->Rename Collection Fields
Now you have collection with headers, you can keep which ever column you want/remove.

Thanks.

------------------------------
Manish Kumar
------------------------------

RE: Excel with Complex Structure

michaeloneil — Tue, 22 Jun 2021 09:22:00 GMT

Hi Rahul

You may not be getting the column headers as it looks like a lot of the rows where the column headers are, are merged cells and it might be causing an issue in identifying the text in these. As @Kumar Manish has suggested use the lower rows as the start point and have BP enter the headers you want after you have the data.

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

RE: Excel with Complex Structure

RahulRamesh — Tue, 22 Jun 2021 13:14:00 GMT

Hello @Manish Kumar , @Michael ONeil
Thanks for your responses. I'm trying to follow the procedure you mentioned and following these steps mentioned for renaming the Columns --> "https://stackoverflow.com/questions/50253464/rename-collection-fields-in-blueprism"

But Number of columns present in my Input Excel is 140+.
1) So should I be creating another Collection with the same number of Columns inorder to rename it?
2) Is there any way to copy only the necessary Column from one Excel/Collection and keep in another Collection/Excel?

Kindly provide me the necessary answers.

Thanks and Regards

------------------------------
Rahul Ramesh
------------------------------

RE: Excel with Complex Structure

mkumar407 — Tue, 22 Jun 2021 13:43:00 GMT

Hey Rahul,

To Answer:
1) So should I be creating another Collection with the same number of Columns inorder to rename it?
-- Yes, that is standard procedure which we follow
2) Is there any way to copy only the necessary Column from one Excel/Collection and keep in another Collection/Excel?
-- Yes, this is possible as well. For necessary columns, you must be knowing which column to copy to new collection.. Create New Collection of needed columns with headers. Loop Original collection and Use Multi Cal stage to copy only selected data to new Collection.

------------------------------
Manish Kumar
------------------------------