I want to extract all row between the rows containing specific keyword.
I have a excel sheet with some rows, i want to search the excel sheet for the rows where first column contains keyword “class” and “height” respectively. then i need to get all the rows encountered between these two rows and copy it to another excel sheet.
I have used DT.rows(counter).item(0).ToString.Contains(“class”) and DT.rows(counter).item(0).ToString.Contains(“height”) to search for rows but even after using nested while loops I cant get correct result. please help
Hi @TwinkleTyagi,
"datatable = dtOutput.Select("ColName='class' OR ColName='height'").CopyToDataTable()"
Here i have attached the sample file also.
File : Extract Rows.zip (2.5 KB)
Regards
Balamurugan
Hi @TwinkleTyagi,
- Retrieve excel data into a data table
- Use select to find the rows that contains [Column0] = ‘Class’
- Find row index of first match row as consider it as start row index.
- Use select to find the rows that contains [Column0] = ‘Height’
- Find row index of first match row as consider it as end row index.
- Use while loop to retrieve data between start row index and end row index.
Please find the xaml attached.
SearchDataTable.xaml (9.4 KB)
The more simplified way is to use linq query and get all the matching rows and store it in a datatable.
- Retrieve excel data into a data table
- Use select to find the rows that contains [Column0] = ‘Class’
- Find row index of first match row, increment it(if you want to retrieve matching row as well, then don’t increment) as consider it as start row index
- Use select to find the rows that contains [Column0] = ‘Height’
- Find row index of first match row, subtract Start row index from this value and consider it as count of rows to fetched from start row.
- Use Linq query in assign statement, fetch data and assign it to a datatable.
Please find xaml attached.
SearchDataTable.xaml (10.4 KB)
3 Likes
What is ColName ? It’s showing " undefined function call ColName" exception.
1 Like
I am reading data from an excel file, I am using “read range” to get Data table.
HI @TwinkleTyagi,
So you need the change condition below that instead of ColName you give your first column name.
" dtOutput.Select("ColName='class' OR ColName='height'").CopyToDataTable()"
Regards
Balamurugan
1 Like
here’s something I have done, trying to fetch data rows between class and height and deleting rest of the data rows, but result is not correct.
Can you please give it a look.
thank you.
Hi @TwinkleTyagi,
I understand the flow but the purpose is that you need
1.the data “class” and “height” .
2.in between the range you need the data.
Because it is so much rounding. Which one is your purpose ?
Regards
Balamurugan
2.in between the range I need the data.
But that range is occurring more than one time in my excel sheet.
1 Like
Hi @TwinkleTyagi,
Can you provide a sample data with excel sheet. To get better idea. I can able to give a sample attachment to you.
Regards
Balamurugan
Hey
I am providing you the excel sheet from where I need data extracted. Please find the attachment. I have to extract data starting from Article EAN and ending with total row wise and write it other excel document… and also I want PO Number Address and other details extracted in any variable…
to be send.xlsx (27.8 KB)
Any updates?
@TwinkleTyagi Have you tried running the xaml I attached in the above thread?
yes I have tried but it didn’t work
What’s the issue you faced?
how to apply string operations in a text file to extract data between two strings?
@TwinkleTyagi Can you please elaborate your question?
yes. suppose i have text file and i want to extract data between two words. how is it possible to extract or trim that text file or apply any data manipulation>?
Anil_G
Nidhi_Gupta1