How to get data in the next row from excel table

Help Studio
, ,
1

Hi everyone,
I have table like below, i use for each row activities to read each row in the table. If row(0) = “許可登録（種類1）” and row(1) =“建設業者” ⇒ get data “大臣”. Now in the same condition, i wanna get data “００－０１９８３５” but i dont know how to get it.
Similarlly, in the table, If row(0) = “許可登録（種類1）” and row(1) =“宅地建物業者” ⇒ get data “大臣” and i wanna get data “２３－０１９６７４” .
Because there are many files and it sometimes dont have data in the red border of the table. In this case, only get data “大臣” and “００－０１９８３５”.

dataTable
dataTable764×219 4.09 KB

Can you tell me how to get data like that, please!
Thanks

2

HI @Phuong_Bui

You can try with Lookup datatable
it will find the value you need in the row you want and you can set the target column index or name and get the data you want from that row

Regards
Sudharsan

1 Like
3

Hi

If you have the datatable with you named dt

Then use a assign activity to get specific cell value like this

stroutput = dt.Rows(rowindex)(columnindex)

Where rowindex and column index both starts from 0 for first row and first column

As you have the value in second row and third column it will be like

stroutput = dt.Rows(1)(2).ToString

@Phuong_Bui

4

Hi @Sudharsan_Ka
I get this data table from excel sheet. In that sheet, it not only data table but also have other data text. So i cant determine column index. In other file, column index can also change.

5

Then in that case read the excel file as a datatable and convert that to string using OUTPUT DATATABLE activity and get string as Strinput

  • now use a Regex method to get the value between two words like this in a assign activity

Strinput = System.Text.RegularExpressions.Regex.Match(Strinput.ToString, “(?<=your right side word).*(?=left side word”)).ToString

Right side and left side word means

Word-1 word-2 word-3

Here if we want word-2 means the expression will be
Strinput = System.Text.RegularExpressions.Regex.Match(Strinput.ToString, “(?<=word-1).*(?=word-2”)).ToString

Cheers @Phuong_Bui

1 Like
6

Hi @Palaniyappan
It’s used to the same regular expression. Thanks so much for your answer. I will try it.
Regards

1 Like
7

Fine

Is there any further queries on this topic

@Phuong_Bui

1 Like
8

Hi @Palaniyappan
If i wrote like that:
System.Text.RegularExpressions.Regex.Match(strInput.ToString, "(?<=“許可登記番号”).*(?=“取得・更新年月日” ")).tostring
Maybe I can get data number “００－０１９８３５” but how about my condition (建設業者), i want to get all 3 values ​​(建設業者,大臣,００－０１９８３５) as 1 group. This is a required condition when get the value number.

Because with conditon "宅地建物業者 ", i also get value number between “許可登記番号” and “取得・更新年月日”

Regards