Automation in a csv file

I have a CSV file.I want to add 3 columns in it.But the name of the columns should be present in the Row 8 instead of the topmost row.I tried using
1.Read CSV activity to read the CSV file.
2.Add column activity to add the column but the problem here is the name of the column appears at the topmost row .
3.I want the column name to appaer in row 8.Is this possible ?

1 Like

Hello;

I believe you are rather referring to the index and not the row of your new column.

One easy to do it is using an Invoke Code activity to add your column.

dt.Columns.Add("New One").SetOrdinal(7)

The index in starting at 0, so your column will now be in 8th position.

You can also use Invoke method with :

Targetobject : dt.Columns.Add(“NewOne”)
(Note that Add method is a function so it will return you the column you added)
MethodName: SetOrdinal

Finally add an int32 parameter and specify the index

Cheers

3 Likes

If we are comparing excel data what is the best way to achieve this ? Macros or UiPath?

Hi,

That all depends on the context to be honest.

Several point to be taken into consideration are who will be supporting what you are making.
What you want to achieve and how comfortable you feel to achieve it in both technologies.

On a professional context, where someone else might have later to support your creation, it is recommended to stick as much as possible with UiPath and avoid external dependencies/technologies. Someone without a good VBA knowledge might have to support it.

Apart from that, catching error which could occur on VBA during will be more difficult to handle than doing it within UiPath

From a performance standpoint, it will be irrelevant for most of cases unless if you are dealing with thousands of rows.

Cheers

Hi ,
Can you provide me with a sample workflow where there is an existing csv file and top 6 rows are having no valuable information.It is from row 7 that the column name starts .Can you add 3 new columns such that name of the new column start appearing in row 7.It will be of great help if you can do this

dt.column.add"new one").setordinal(7) is not working
throwing an error that expression does not produce a value

Hi,

the best is you share with me the file you work with adding data and the workflow you are working on.
I will try to find some time in the evening to have a look into it.

Cheers

Hi,

Here you have an example which will always be adding your new at index 3 (4th position).
If it’s not want you want please clarify the input you make and the output you expect in a more visual way and as said share your workflow.

Cheers project.zip (1.9 KB)

?Hi ,

Can you help me with excel automation ? I am in urgent need of this. Also i am not able to visit the community

The file you shared is a JSON file Please share xaml file

Can you help me with a csv automation ?

Hi Mudit,

I’m happy to help on specific problems during my free time when they are concise and correctly described but cannot afford to discuss requirements, a thing that I am doing enough to pay my bills :slight_smile:

I am sure that if you make another post an attempt at a workflow and good description of your issue, I or plenty of people will be happy to help you with this.

Keep in mind that the big majority of people will not help if you don’t put a minimum of efforts on your request, once again description… examples…, describing what you tried to make it work… and will definitively not make workflow upon request.

Cheers

1 Like

InsertingColumnsToExistingFile(Autosaved).xaml (34.4 KB)
Book1.xlsx (7.8 KB)
This is the workflow that i have designed.I want to filter the sheet so that all the records before 1970 are filtered out

Cannot perform ‘>’ operation on System.Double and System.String.

Hi Mudit,

Regarding this error one of the variable/value here is a string and cannot be compared with the double (decimal).

You can use double.parse(str) so you will be able to compare them. You string must have a value which is convertible to string.

Cheers

Regarding your issue here:

You have a workbook and you would like to get only the record where the “date” value (stored as DateTime in the example provided) has the year higher 1970.

There are plenty of way to achieving this but I will be using LINQ since it’s very straightforward and avoid encountering exceptions in case of non-expected inputs.

You can start with a (Workbook)ReadRange to get you records under a variable “dt”.

We will then:

  • Convert the datatable as Enumerable (so we can use Linq on it)
  • Using .Where with two conditions: Checking if the “date” is Stored as DateTime, Casting “date”
    as datetime and check if the Year is after 1970.
    -Convert back the enumerable as datatable and reasign it to the Inital dt variable.

We can achieve all of this with on assign activity as under.

dt = dt.AsEnumerable.Where(Function(r as Datarow) TypeOf r("date") is DateTime AndAlso Ctype(r("date"),DateTime).Year>1970).CopyToDataTable

It is possible to stick to dt.Select approach as you did initially using the following synthax:

dt = dt.Select("[date] > #1/1/1970#").CopyToDataTable

However this would throw you exceptions when you would have an empty or different datatype store in the date column, where the Linq would simply ignored it.

Find here an example workflow.

Example.xaml (7.5 KB)

Cheers.