I have the following files:
RPA_21_IND.csv
RPA_22_IND.csv
And RPA_21_22_IND.xlsx
CSV Files contains 7 Columns [A,B,C,D,E,F,G,H]
XLSX File contain more than 15 Columns
In RPA_21_IND.csv File:
Step 1: Read the Column A,B,C in SheetName: “RPA_21_IND” and write into the SheetName: “Data” of “RPA_21_22_IND.xlsx” Column A,B and C
Step 2: Read the Column D in SheetName: “RPA_21_IND” and write into the SheetName: “Data” of “RPA_21_22_IND.xlsx” Column G
Step 3: Read the Column E in SheetName: “RPA_21_IND” and write into the SheetName: “Data” of “RPA_21_22_IND.xlsx” Column L
Step 4: Read the Column F,G,H in SheetName: “RPA_21_IND” and write into the SheetName: “Data” of “RPA_21_22_IND.xlsx” Column N,O,P
Like this same way, have to do RPA_22_IND.csv file.
Now i can read the data of csv file one by one but im not able to write the data in particular column of .Xlsx file.
Also,it is overwriting too.
Hi @sandeep13,
I already using “Write Cell” Activity.
First File can read & write. [RPA_21_IND.csv].
But, when write the data of second file [RPA_22_IND.csv] it is overwriting.
Fine
—let’s create a Datatable with build Datatable activity with set of columns we want and get the output with a variable of type datatable named FinalDt1
—read the csv file with Read CSV activity and get the output with a datatable variable named dt
—then use a for each row loop and pass the read datatable variable dt
—inside the loop use a assign activity like this FinalDt1.Rows(dt.Rows.IndexOf(row))(“yourcolumnname in FinalDt1”) = row(“yourcolumnname in dt1”)
—this applies for all columns so we can use that number of assign activity within the same for each row loop and mention the appropriate columnnames along the above expression
—now once this is done between the first csv and FinalDt1 then we can create another datatable FinalDt2 and read the csv file with the read read csv activity with the output variable of datatable named dt2 and do the same process with the second csv file but with expression like this FinalDt2.Rows(dt.Rows.IndexOf(row))(“yourcolumnname in FinalDt2”) = row(“yourcolumnname in dt2”)
—once both FinalDt1 and FinalDt2 is ready with the data then we can use MERGE DATATABLE ACTIVITY where in the source mention as FinalDt2 and destination as FinalDt1 which will merge them together
—now use a WRITE RANGE ACTIVITY form workbook activities and mention the sheetname, Filename with filepath and datatble as FinalDt1 as now that has the consolidated data and make sure that ADD HEADERS property is enabled
Simple Isn’t it
Hope this would help you
Cheers @muthu.m
I have finished this process. My workflow working fine.!
But i have a doubt, the way of workflow is correct or not? @Lahiru.Fernando@sandeep13
Herewith I have attached my workflow. Pls find the attachment…
Hi @Palaniyappan
How to get LAST Row of COLUMN A…?
Because i need to write the data after the last entered row of column A.
So I need to get the Last row index of Column A. Pls help… @sandeep13@Lahiru.Fernando@KarthikByggari
Fine no worries
Your workflow looks good and I was about to tell
Sorry for the delayed response
So for this
It’s like this Yourdatatablename.Rows(yourdatecolumnname.Rows.count-1)(“columnname-A”).ToString
That was typo error buddy
sorry for that
It’s yourdatatablename
Fine
Ok to get the last row and column - index of columnA yourdatecolumnname.Rows.Count.Tostring
Now the problem is, the above screenshot Column-E and Column_G having formulas.
Formulas are applied to the Rows(5000).
When I’m trying to count the datatable, it calculate the total rows and returns the value=5000.
But i need the Last Row of (Index Number) in Column A.
How can I ??
Pls help…
Thanks
Muthu
