Specific Cells in a .CSV file into Excel Worksheet Specific Tabs

Hi, I need some help, I’ve searched the forums but found no resolution in similar topics, basically my process for automation goes like this, I need to open up a .csv file and copy and paste the data below the header columns, manually speaking I would have to do a “ctrl+shift+end” from Cell A2 and copy -> Open up another Excel Worksheet Report, go to the RAW tab and paste as value starting from Cell A2.

How do I achieve this? I’ve read various posts, some advised to start with “Start Process”, some with “Read CSV”

However I have no idea what goes in the “App Argument” Box, I have totally 0 knowledge in RPA/Programming/Coding by the way…

This is all I’ve done so far … pathetic I know… so the “Read CSV” Activity is supposed to somehow capture the data inside the file once I click run, and the pathing in my “Start Process” Activity is the destination file.

How do I go about copying some data from .CSV to the excel sheet ?

Would appreciate some help !

1 Like

Buddy @RPALearner93

Kindly follow the below steps buddy

  1. use read csv file and enable the property IncludeColumnNames in it
  2. use excel application scope to open a excel file
  3. use write range activity and uncheck the add headers property and mention the range as “A2” that would start from A2 cell buddy
    Simple buddy
    Kindly try this and let know buddy whether this works or not
    Cheers @RPALearner93

Hi Palaniyappan, thanks for your advice,

How should I input for DataTable under the “Write Range”?

1 Like

Buddy @RPALearner93
pass the output variable that you obtained from Read CSV activity buddy, which would be of type datatable…
Kindly try this and let know buddy
Cheers

Did that work buddy @RPALearner93

Hello friend, i’m not quite sure what “output variable that you obtained from Read CSV” means, I’m sorry i’m really terrible with these technical jargons :frowning:

I vaguely understand the “Read CSV” function kinda reads the data in the CSV file and has the information stored somewhere and somehow the logic is that I’m supposed to have the output in the “Data Table” box, but how do I do that ?

1 Like

@RPALearner93

  1. use Read csv file activity to read data from CSV file and will give you output as dataTable and say ‘DT’.
  2. use Write Range activity inside excel application scope activity and pass that output dataTable to write range activity as input.

Buddy @RPALearner93

No worries…see…when you have csv file…you can read that with a activity called READ CSV… activity
This read csv activity will take INPUT as your file path buddy and gives us a OUTPUT of type datatable…and that can be obtained by creating a variable in read csv activity-properties…just press ctrl+k in the output property field in the read csv activity and type a name that becomes a variable of type datatable…you can check that in variable panel once after creating buddy…
–then as you have now a datatable format of a csv…you can enter that to excel na…
Usiñg write range activity that takes a datatable as input and mention the range you want to start …and sheetname buddy
Thats all you are done…
Kindly try this and let know buddy whether this works or not
Cheers @RPALearner93

Hi @lakshman and @Palaniyappan

It is working now, it auto reads the designated CSV file and pasted in the range of cells I wanted in my other excel sheet just fine ! Thank you! Just to check is there a way to also include an instruction that after pasting the cells, to automatically “double-click” the field handle on my FORMULATED cells column to extend to formula up to my latest data entry ?

For example Column A - D is where i paste my data from CSV, cell E-F consist of specific formuales to display certain data depending on Column A-D’s data, so manually speaking every time we copy and paste new data and paste in column A - D, we would have to manually drag down the formula from Column E-F, can we automate this part as well?

Thank you friends :slight_smile:

2 Likes

yes buddy you can automate that as well
use start process and mention the file path of the excel file and this will open the excel file and brings the file to foreground of the screen and from there on you can automate with click and type into
or even you can go for Macro which can be executed with Invoke VB activity buddy
Try this and let know buddy
Cheers @RPALearner93

did that work buddy @RPALearner93

Not too sure how would start process works, currently i’m up to here

At one point this displays when I run it, an error displays

Not sure how to go about it, i’m not too familiar with using macro as well, not sure how the language goes and how to come up with an instruction to do what i’ve mentioned earlier

@RPALearner93

In start process Activity, you have to specify the entire file path of Excel under filename property to open it.

Hi as you can see I’ve included the entire file path but it is still displaying an error DESPITE the file opening.

Here is the expression editor

Buddy use a KILL process activity before start process and mention the process name as “EXCEL” in kill process activity
Cheers @RPALearner93

Hello Palaniyappan,

Thank you for rendering assistance, I greatly appreciate it.

I’ve tried to input a kill process activity after the “Write Range” (Copying and pasting from one excel to another) and in the Kill Process Activity, I’ve input to the process name to kill “Write Range”, I tried to run it and ultimately it does the copy and paste but still ends in an error, what am I missing here?

1 Like

@RPALearner93

Check filename properly in Start Process activity. Here, you passed like “SMS Template - 2019 05 May v2”. I guess it is incorrect. There is no extension to this file. you have to specify absolute file path here.

Hi Buddy @RPALearner93

You were almost done…the thing is the input been passed to start process under file name property is not correct…we need to pass the EXCEL FILE name with .XLSX or .XLS extension like this
"SMS Template - 2019 05 May v2.xlsx"
That would work for sure
and make sure that you have mentioned process name property in kill process as "EXCEL"
Kindly try and let know buddy @RPALearner93

@lakshman @Palaniyappan Hi thanks guys, it is working now, it successfully copy and paste over the data, I’m guessing the kill process worked as it opens up the excel file anew again after, now all I need to do is automate a procedure where it highlights certain cells and “double clicks” the field handle, I tried using the recorder however it seems it doesn’t register keystrokes but only clicks…

2 Likes

Are you trying to highlight with cell color buddy @RPALearner93
kindly correct me if am wrong
Cheers @RPALearner93