Hi guys,
I have two excel sheets with one containing 21 columns and another containing 3. The first column of both sheets have timestamp values.
The main sheet containing 21 columns has unique headers say a,b,c,d… and the second sheet has just three headers, the timestamp and, say, j and k which are also present in the main sheet.
The main sheet has about 7k rows with timestamps and values for each column a,b,c,d… while the second sheet has no rows.
I want to compare the headers of second sheet with the main sheet, that is, check if j and k exists in the main sheet, and if it does, I want to copy all the 7k values of j and k to the second sheet including the timestamp column.
Main sheet: Timestamp || a || b || c ||…
Second sheet: Timestamp || j || k
As far as I know, excel loop iterates over each row and if a condition is satisfied, writes into another column. Can I use excel loop or advanced loop activity to iterate over the column headers and if a condition in column headers is satisfied, write the whole column values to another sheet?
Thanks for your help. I’m using the CE of AssistEdge
Hi Stevin,
You use case is pretty complex. I will give you few pointer to begin with.
Use Import Data Table activity to get header from both excel. Make sure you have deselected Has Headers check box and in setting section Rows to Take count is 1.
Post that use Advance Loop and iterate over the column name to get the matching column
Hi Amit,
I’ve made the nested advanced loops with each sheet. Can you tell me what you have given inside the while loop?
And to compare the items in the advanced loop, should I use .tostring and then compare each item? Or is using ‘is’ enough?
Shouldn’t I use two counter variables to iterate over item of each loop?
Hi Amit, thanks for your pointers.
I was able to complete this use case but I still have an issue in the final result.
I have stored the values from the main sheet as double and added it to a new datatable I have created. The values are coming right but MS Excel is treating these values as numbers stored as text. Is there any way I can store these in number/ numeric format itself?
I am using 2010 version of Excel.
Thanks for your help
Hi Amit,
Up to 400 tags the process runs successfully. But when it goes above 450 the error comes up as “Insufficient memory to continue the execution of the program.”
How can I solve this?
Regards
Hi,
When you export a data table then all column values are stored as string. If you want to convert them to number or any other data type then using Excel recorder activity record a macro to achieve the same
Regards,
Amit Anand
Hi Stevin,
Nested Loops with large data set can be resource consuming. try splitting your data set into smaller file in order to avoid this issue.
Regards,
Amit Anand