Automation in a csv file

csv
activities

#1

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 ?


#2

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

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


#4

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


#5

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


#6

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


#7

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


#8

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)


#9

?Hi ,

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


#10

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


#11

Can you help me with a csv automation ?


#12

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


#13

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


#14

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


#15

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


#16

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.