Store value of strings in DT, starting from a particular row

Hi there. I was assigned a task which requires some numbers to process some information. Refer to the picture below, There are two sets of policy numbers (1st set from C9 to C23; 2nd set from C27 to C37). My questions are:

  1. How could I set the code so that the robot knows to read the first row cell C9 and end with the last row cell C23 and store in the datatable1? The first row must be the row cell immediately below “Policy No.” (which might not necessarily be located in C8). The immediate row cell below the last row should always be blank (i.e. C24 in this case).

  2. How could I set the code so that the robot knows to read the first row cell C27 and end with the last row cell C37 and store in the datatable2? Perhaps we could set the first row to be the row below “Fan Recoveries” (i.e. A26) and read the string in that row under Column C? Or are there better methods?

  3. After storing the 2 sets of policy numbers in the two data tables. I need to use those value to perform search for new information. For that, I will create another two corresponding data tables to save the information inside them. Suppose I have save all values to those data table. I would like to input the value to Column J.

  • For example, I will use the value in C9 to search new information. Let’s say the search result is “Good Performance”, the robot should input “Good Performance” under J9. Same for C29, the string of the search result should be pasted into J29. The problem is, how could I paste the first row of the data table into J9 and J27?

Many thanks for the help! Please let me know if there is anything unclear.

Anyone can help here?

Hi @wth1993,

Can you provide the sample file? So I can work and share the XAML with you. From where do you search the policy number to get the information?

Hi @aanandsanraj,

Attached is the Excel file. The search will be performed in our company’s internal system. With the policy numbers available in DT1 & D2, I am able to do it myself and save to DT3 & DT4. However, I don’t know how the robot will know to paste the first row in DT3 to J9 and the first row in DT4 to J27 in this case.

The logic will be:

  1. store 1st set of data (C9:C23 in this case) to DT 1; perform search and store the result in DT3; then paste the result to J9:J23 respectively
  2. proceed to store 2nd set of data (C27:C37 in this case) to DT 2; perform search and store the result in DT4; then paste the result to J27:23 respectively
    (since the search for new result is not available, it is okay to just copy and paste all the strings (i.e. policy numbers) in DT1 to DT3; and those in DT2 to DT4) for demo purposes)

Thanks in advance!

Sample file.xlsx (11.7 KB)

@wth1993

Why are you splitting the data into two different datatable?
Why can’t you keep that as single table to find the policy number in your internal system one by one?

I’m not sure what you meant. The policy numbers are not in my internal system, but in the Excel file as you can see from the picture (Column C). These policy numbers are used for searching new result. For example, if I put the first policy number #00100076 in my internal system, the result could be “9912220”.

Regarding the reason I need to store 2 sets of data instead of using just a single table, it is because the methodology of getting the result for the 2 sets of data is different. Unless the robot knows the row after which another methodology should be used for searching for the result in that single data table, 2 separate data tables are required.

Could anyone offer help… Really need help here. If there is anything unclear, please let me know

@wth1993 - Below Command will skip the first 7 rows and store from 8th row to a datatable.

NewDT = YourDt.AsEnumerable.Skip(7).CopyToDataTable

Once this is done, when you do your process and write range by checking the “Add Header” will automatically write the value from C2(your Original C9)…

Thanks for your reply. If I understand correctly, your approach is to create a data table first to fetch all cell value from Column C, and then create another data table with the Assign activity as you gave.

Here is the question: How to create the first table table to store the policy numbers? There are 2 sets of policy numbers. The second set starts at C27. It can’t be hard-coded like NewDT = YourDt.AsEnumerable.Skip(26).CopyToDataTable because the row in the second set can be different.

Hi @wth1993,

That was an interesting question!

As you say the row index can be dynamic. This proposed solution will work if your column structure is as shown in the Sample file.xlsx and can handle dynamic row index.

Approach for DT1

  1. Look for a particular value in Column2(Column 3 in excel) if it is “Policy No.” then save the index as starting index for table 1: Save to StaringIndexTable1
  2. Look for an empty value in Column2 and ensure the value is after the starting index (in short look for the end of the table) save the index as ending index for table 1: Save to StartingIndexTable1
  3. Read the excel file for the given starting and ending indexes (Hardcoded “I” column)
    image

Output (PremiumDueToYou):
image

Approach for DT2
A little tricky because here when we read any range our columns will not have headers.

  1. Same as for DT1 but looking for “Claim Recoveries” : Save to StartingIndexTable2
  2. Same as for DT1 but looking for empty value and index > StartingIndexTable2 : Save to EndingIndexTable2
  3. Lets clone DT1 to get the column names : Save as RIRecoveriesduefromyou (Datatable)
  4. Read the excel file for the calculated ranges from Step 1 and 2
  5. Use a for each Row loop and add the row.ItemArray to RIRecoveriesduefromyou (Datatable)

Output (RIRecoveriesduefromyou):
image

To your other question: How will the robot know it should enter J9 and J27
Essentially you want to use the StartingIndexTable1+1 and StartingIndexTable2+1.
In this example case these are 9 and 27 respectively. The +1 is to ensure that the robot leaves the header “Result” intact.

Here is the XAML file for your reference (input file is your Sample file.xlsx): SplitDatatables.xaml (25.9 KB)

Hope this helped a bit!

2 Likes

Thank you for your perfect answer.

Since I only need the policy numbers in Column C instead of the whole table, I changed the Range to “C” + StartingIndexTable.ToString+":C"+EndingIndexTable1.ToString.

Really appreciate your help!! Wish you a Happy New Year!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.