Read two column values from an excel sheet

Hi All,

Please refer the attached excel . From the attached sheet I want to read value from Invoice Key Column and all the Unique references which are equal to its first value , something like One to Many mapping . How can I achieve that ? Any sort of input would be much appreciated . Thanks sampleTest - Copy.xlsx|attachment (20.2 KB)
image

@Shailesh123
You can use read range activity to store Excel data into datatable after that
Perform Operation on it through iteration .

1 Like

I am reading the range but I am unable to retrieve data based on Invoicekey Column value . Were you able to refer the excel sheet which I attached ?

@Shailesh123
I am not able to download excel sheet.
for retrieve data based on Column
1)you can use filter data table activity

1 Like

@sandeep13 image
Can you refer the image . From this Data Table I want to group Inv.Key first value to Unique.ref three values and so on .

send ur excel file here

sampleTest - Copy.xlsx (20.2 KB)
let me know if you are able to view the attachment

and also, i dont get what you are trying to achieve here,

  1. reading values from invoice key and the uniq ref id associated with it?
  2. what to do with the blanks?
  3. do you only want the rows which has the invoice key?
  4. and “equal to its first value”? what is this first value?

please explain clearer on what do you want to do.

@Shailesh123

As per my understanding you have to extract row only when “Inv.key” has value right … for that use below code in assign activity after read excel through excel application scope

dtTest.AsEnumerable().Where(function(x) not String.IsNullOrWhiteSpace(x(“Inv.key”).ToString)).CopyToDataTable

Otherwise please explain you requirement with examples

@amaresan- I want one to many mapping . Bot should read Unique ref’s for first invoice key and if there is an Empty Invoice key Row it should read the next invoice key and all the relevant Unique references related to it till we reach Empty row of Invoice key . Something like Key value pair . Invoice Key and Unique ref should be read as Key Value and based on Invoice Key I should read Unique ref, was it clear ?

@Shailesh123

Did you try my above code?

please find the xaml below I hope this is my requirement

check and let me know if anything else.

Excel test.xaml (9.5 KB) project.json (682 Bytes) sampleTest - Copy.xlsx (20.2 KB)

Hi amaresan - this workflow is giving me all Unique values from Both the columns . I don’t want to delete other row values . Actually I want the entire Datatable structure to remain as it is , the Bot should just read first Invoice key- three Unique values(because for first Invoice key we have two empty rows) and move to next Invoice key and its relevant values without deleting any of the other Unique references

@Shailesh123

may I know about your Required Output format ? You need string or Data table or Loop?

could you explain with example …ex( Input data is this xxxxxx and output data is this xxxxxx like that)

Required output should be a datatable itself as I have to read values from Both invoice key column and Unique ref column

@Shailesh123

Read the excel sheet and store the data in a datatable. Let us take it as dta.

Now use one Assign Activity.

dta= (From p In dta.Select
         Select dta.Clone().Rows.Add(Convert.ToString(p("Unique.ref"),If(not string.IsNullOrEmpty(Convert.ToString(p("Inv.Key"))),Convert.ToString(p("Inv.Key")),dta(dta.Rows.IndexOf(p)-1)("Inv.Key"))).ToArray.CopyToDataTable

Note : I have not tested the Query, Please solve by yourself if any syntax errors, or reply here so that we can try to fix.

If you want a Key Value Pair then we have to write one more query by using the about output.

Regards,
Mahesh