Hi!
I have tried to figure this out for a week now. So my problem is i have one big excel file with 12 columns in one row and it has lot of rows. Now i want to divide it to different excel files depending on one column. I have figured out how to get the specific row but now i need to add the rows to the new datatable that i can create the file. How to do that?
Now i have done it so that
i go through for each row in the datatable
and inside the loop i use get row item
then i use add data row with these array of values what i get from get row item
But this cannot be the way… i have now 12 variables just used with that get row item and i have done that get row item for each column.
I have tried also to get for each row and then add that row to this new datatable but it say’s that _ This row already belongs to another table_ is there any way to do that more efficiently?
Read the excel sheet range into a datatable (dt) using read range activity.
Create a new datatable variable in the variable section say newDt
Using an assign activity, you can directly copy the data to the newDt using the below expression
newDt = dt.Select(“Your Filter Expression”).CopyToDataTable() (Your filter expression has to be like ColumnName=Value)
Oh that would be really great. But my “filter” contains a string. And it doesn’t seem to understand when there is spaces and comma in the string.
Or could i do something like DT.Select("CollumName=’ " + StringValue + " ’ ").CopyToDataTable.
Have you tried this method? Does it work, it would be super handy way to do that.
It works fine for me with spaces and comma within a string inside a filter expression as well. And the it should also works with a variable as you have mentioned above.
Hi Karthik…it gives me “Excel application scope: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))” error when i try to copy from datatable to macro enabled sheet. Any idea?
First can you able to get the data from macro enabled excel to data table ? If yes copy of data table is independent of excel sheet .I will also try today