Append Excel Data By Column | CSV to XLSX

Hi all,
@HareeshMR @Lahiru.Fernando @sandeep13

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.

Thanks
Muthu

1 Like

@muthu.m
For write Excel you should use write cell instead of write range for writing on cell

2 Likes

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.

Thanks
Muthu

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

2 Likes

Hi @Palaniyappan,
Thanks for your response!!

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…

ExcelDataProcess.zip (31.4 KB)

Thanks in Advance
Muthu

1 Like

Hmm fine
Were we facing any issue buddy
Cheers @muthu.m

@Palaniyappan bro, now dont have any issue.
Its working fine!!
My doubt is, Way Of WorkFlow is Right or Wrong?

Thanks for ur respone !!!

Thanks
Muthu

1 Like

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

Thanks
Muthu

1 Like

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

This will give the last row value of column-A

Hope this would help you
Cheers

1 Like

Any other queries to be discussed further on this buddy
Cheers @muthu.m

1 Like

Hi @Palaniyappan,
Thanks for your quick response!!
Also thanks for go through my work flow…!

Ok, now i tried your query. Its getting the value of Last Row.
But, I need the Index No. of the LastRow

Yourdatatablename.Rows(yourdatecolumnname.Rows.count-1)(“columnname-A”).ToString

I cant understand “yourdatecolumnname”.

Thanks
Muthu

@muthu.m
Yourdatacolumnname is name of datatable
I think its misspelled

3 Likes

It means name of the COLUMN A, am i right ?
@sandeep13

Thanks
Muthu

2 Likes

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

Cheers @muthu.m

2 Likes

No @muthu.m
Name of datatable
Yourdatatablename.Rows(Yourdatatablename.Rows.count-1)(“columnname-A”).ToString

3 Likes

Thanks @Palaniyappan @sandeep13
Now I got it.

Thanks
Muthu

2 Likes

Fantastic
Cheers @muthu.m

1 Like

Your welcome

3 Likes

Hi @Palaniyappan @sandeep13

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

1 Like

Hi @Palaniyappan @sandeep13

I found the Solution.

rIndex=dtExcel.Rows.Cast(Of DataRow)().Where(Function(row) Not row.Item(“YEAR”) IsNot DBNull.Value Or row.Item(“YEAR”).ToString() <> “”).Count

I get the Last Row Index of the Column “YEAR”

Thanks
Muthu

2 Likes