Is there any option within Read Range activity of excel/ workbook to select customized range (let’s say A1:B5 and E1:G5)? I tried writing A1:B5 + E1:G5 in range field (within input section) of read activity but it did not work.
You can try something like this A1:G5, it is similar to the example which you have A1:B5 and E1:G5
Defining range such as A1:G5 will include rows C and D in the loop which I want to avoid. I am looking for a customised range such that it will exclude rows C and D from A1: G5.
- either read A1:B5, E1:G5 in two separate datatables (dt1 and dt2) and when iterating through the rows collection of dt1 (row1) you can obtain a reference to the corresponding row in dt2.
row2 = dt2.Rows(dt1.IndexOf(row1))
- or like Nikita said read everything at once. C and D are columns not rows. The number of rows/iterations will be the same: 5. The amount of data loaded in memory will be higher.
As per my point of view, the wise and feasible solution to this problem will be reading all the columns from the sheet at a time by using SQL Query method and retrieving from the data table using assign activity (By specifying column name or index).
In this way it will be more flexible to change the columns also if some new columns needs to be added or removed.
As per @badita advised you should try that way because if your need is only to fetch only particular columns then it is not a wise solution to fetch those columns which will be not useful for you. and will add more activity and variables to assign the things. so better to fetch only limited data according to your requirements.
I accept your suggestion, but my approach is just to be more flexible in case change in (requirements of columns) column names or change in column requirements. I dont think it will impact that much if we read 7 columns instead of 5 columns, it may not not impact on the performance that much.
The next thing is using query, we can also specify the columns we want to fetch and retrieve only those required columns.
Attached is the excel. I need to write rows 3, 4, 5 excluding column 6, and rows 9, 10----13 excluding column 2 and 3 in an email/ excel sheet. I was thinking if a SINGLE read range activity could achieve it.
Please provide .xaml solution to it.Excel.zip (6.7 KB)
Please find check this solution. Hope this is all you need.
Main.xaml (13.0 KB)
.Try it and let me know.
Regarding using Custom Range if anyone is aware then please share some idea.
U got any idea? Have same question, how can I import data from particular one row of excel or csv (e.g.only 4th row ) considering all columns(Taking first row as reference)?
You can use Read Cell activity…just pass cell number to the activity.
Hi Guys I am trying to read excel from row 15 all the way to the end and want to copy all the data and paste it to another excel. Would read range be the best bet for this?IF yes, how shall i specify last row as the last row number might change? Thanks in advance
If you know the range of columns you want to retrieve, read range to retrieve data and write range to write back the data works.
If the data is huge and you are creating a new sheet in the same excel, then you can try writing Macro to copy the data. That will be faster.
Thanks Madhavi…I actually need to copy data to a Macro enabled sheet…Any idea how do I enable macros on the sheet before i copy to it…I think I got through the first part of read range.
You can refer https://analysistabs.com/excel-vba/copy-data-from-one-worksheet-to-another/
It gives an idea.
Use Read Row activity inside excel application scope and give the row name.
this will give you IEnumerable. I have converted to array in the attached workflow file
Please find the attached workflow for more information, ReadRow.zip (16.5 KB)
Please feel free to comment.
Hope this helps you.
Can someone provide me with this solution or guidance…
Use Input file with nearly 50 rows containing 10 columns ( Column names like ID, Name, Age, Location , Project Name, Project ID , year of experience, etc…)
· It should give output with required range of value and that range should configurable.
· E.g. List of people lies in the age range of 25 to 45