Workflow Soluiton

 View Only

 Excel Get Value

Gary R's profile image
Gary R posted Aug 04, 2021 07:45 PM
Hi all! I have what I thought would be a very simple task.  Import a 3 column spreadsheet (.xlxs) and map it to an existing 3 column data type (all text fields).  I have had occasion to import single columns and have never had a problem. 

It seems the 'Excel Get Value' component outputs a single column no matter what I put in 'Values to Get'.  Per the sparse hints I could find I believe the entry should be A1:C500/<Filename>.  However, the next step (a 'multiple mapping' component) apparently only allows a source array type of 'text' for the excel import <filename> and it refuses to show the 2nd and 3rd column in the mapping process.

Any assistance would be greatly appreciated (even if you feel the need to chuckle) :)
Tylor Giles's profile image
Broadcom Employee Tylor Giles
Hello Gary.

I just did a quick test with a simple 3 x 3 Excel file. I was able to successfully pull all values from the spreadsheet. In order to do this I needed to specify 3 different value ranges to pull in order to get the data. Here is a screen shot of my configuration of the component. 

Gary R's profile image
Gary R
Taylor,

Thanks for straightening me out on the 'Excel Get Value issue'!  It would appear my real problem is importing the retrieved data to a collection.

I set up a 3x3 excel document (Below)

I modified my 'Excel Get Value' per you example. (Rows are arrays)


My next step (Multiple Value Mapping) is where everything goes wrong.  I'm apparently missing something.  How do I get the data rows from 'Excel Get Value' into a collection?  The 'multiple mapping' process I used for a single column doesn't seem to work here. 

When I go to choose a source for multiple mapping, now I'm looking at three arrays  (one for each column) instead of the expected single array with three columns.  (could be an expectation issue :) )  Selecting 'excelFile.[Contents]' just results in random numbers.



My 'Target Type' (IDList) is three column text.

Tylor Giles's profile image
Broadcom Employee Tylor Giles
I don't believe the 'Excel Get Value' component is what you really want to use for what you are attempting. I recommend creating an Integration component using our 'Excel Generator'.
Using this you can specify the Excel file you wish the generator to use to build the data class. Once you've built the integration library and imported it into your project you can use the 'Dynamic Class Read' component and specify the Excel file you opened in the project. This will then create an array / collection of your custom data class with each entry in the collection being the contents of the row from the Excel sheet.

Here is a sample of what the data would look like if you browse the data.

Does this make sense?


Gary R's profile image
Gary R
Taylor,

Perfect!  Didn't realize you could use the Excel Generator as a 'template'.

Many Thanks!!