Hi, I have a requirement where I have to read the course name “commerce” (the name is dynamic) from the given excel sheet. How to read the course name from excel without opening the excel sheet(Cell Address is constant). And also I have to read the table from the given excel into Datatable.
TIA
Hi @MEGHA_cs ,
Read excel using Read workbook range activity.
Convert datatable to String using Output datatable activity.
Use regex and extract course name.
System.Text.RegularExpression.Regex.Matches(extractedText,“(?<=COURSE:).*$”)(0).Value.Tostring.Trim
Hi,
Course
Can you try ReadCellWorkbook activity?
Table
How about ReadRangeWorkbook from “A13” with headers OR “A15” without headers?
Regards,
I dont know the exact Cell adresses. these are fixed, but dont know the exact address.
I know only it sheet has "course " word, based on this I have to fetch the course name and also the table
You could read the entier Excel document as a DataTable, then go through each row until you find the row where the value of the first item is the word “COURSE”. (e.g. currentRow(0).ToString = “COURSE”). Then you can fetch the course name from column C (e.g. currentRow(2).ToString).
Use read range workbook activity with add headers unchecked
Now…filter the datatable on first column with course keyword…then from the row you can get the course name by checking each column in a loop…
For the table…first find out the row index of No which is the first cell header of the table…for this you can use as below
Dt.Rows.IndexOf(dt.AsEnumerable.Where(function(x) x(0).ToString.Equals("No"))(0))
Now use dt.AsEnumerable.Skip(ObtainedIndexRow).CopyToDataTable this will give the datatable with only the table values…
If needed you can now assign the column names from first row to the column names and remove the first row…this can be done using loop on columns
Hope this helps
Cheers
Thanks… but I didnt get.
Put course in upper case and try.
System.Text.RegularExpression.Regex.Matches(str,“(?<=COURSE:).*$”)(0).Value.Tostring.Trim
“then from the row you can get the course name by checking each column in a loop…”
Thanks
first if you filter on course in first column then you will get only single row
And if you already know the column in which the course name is present then we can use Filtereddt.Rows(0)(3).ToString to get the course name(Assumign course name is in D column or the fourth column)
If it is not static then use a for loop on dt.Columns and check each column for value and get the value
cheers
Hi @MEGHA_cs ,
Try this:
Properties of matches activity:
Output expression: matchedData(0).Value.Replace(“,”,“”)
where matchedData is output of Matches activity.
Hope it helps.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
