Converting a column in csv file from string type to number type

Dear RPA experts,

I have a column of csv file that should be changed to number type when it is written to Excel. What is the best way to do this? I tried many ways but i did not come to the right solution.

Many thanks for your help.

Regards,

Nazarius

Just try reading the CSV file and store in a data table . Then write the same data table to excel @Nazarius.

It works in some cases :slight_smile:

Fine
–use read csv file activity and pass the file path of the csv as input and get the output with a variable of type datatable named outdt
–now use a assign activity like this
outdt.Columns(“yourcolumnname”).Datatype = GetType(system.Int32)
–then use write range to write to a excel

Cheers @Nazarius

1 Like

Many thanks for the input guys, im on my way hone but i will try this as soon as possible.
Will let you guys know the outcome.

Cheers,

1 Like

Sure
Cheers @Nazarius

We are the only people who work even on the way home :smile:
Me too, the same on my way @Nazarius

Techies :crazy_face:

1 Like

@HareeshMR:Unfortunately your solution did not work. The column still has ‘string’ as the value type.
@Palaniyappan: i tried your way but i got the following error message: ‘Cannot change DataType of a column once it has data’
I remembered that someone did the same by using add data column and assign the value with datatable expression, Unfortunately i cannot find the thread here :frowning:
Please help guys

Ty

I need the same also.

Do you have any idea how to do it?

@HareeshMR @Palaniyappan

@the.christopoulos yes, and @Nazarius was correct you have to do it by adding a datacolumn, iterating through it and converting from oldColumn to newColumn, then delete oldColumn and rename newColumn to the oldColumn’s name (last part is optional).

Here is some pseudo code to do it, i can put in .xaml if it isn’t clear:

Read CSV activity --> save as dt1
Add Data Column  activity --> TypeArgument = Double (or whatever number type you want); DataTable = dt1; ColumnName = tempColumn
For each row in dt1 activity
   Assign row.item("tempColumn") = CDouble(row.item("OldColumn")
next row
Remove data column activity --> ColumnName = "OldColumn"; DataTable = dt1
Assign dt1.Columns("tempColumn").ColumnName = "OldColumn"

Replace “OldColumn” with the actual column name you want to replace. If you want to get extra fancy, you can also put the replaced column at the old spot by getting the .Ordinal property of the “OldColumn”, then setting the “tempColumn” to be at that Ordinal after deleting “OldColumn”

@the.christopulous: The method by creating a new column works, but it is rather difficult to do when the column is located not at the end of the line but in the middle for instance. My solution was to invoke vba for this. In Excel, try to select all the column that needs to be converted, click on “Data” >“text to column” > select “Fixed With” radio button. This simply the steps! now try to translate the steps using macro recording and simplify it so that it can be called dynamically for your need. Good luck!

1 Like

@Nazarius the order of the old column shouldn’t matter, as you aren’t calling by index, but by column name instead. And if you need the newly created column in exactly the same place, that can be easily done by Assign OriginalPosition = dt1.Columns("oldColumn").Ordinal before deleting it. Then rename the new column to the old one. Then use invoke method TargetObject: dt1.Columns("oldColumn") MethodName: SetOrdinal Paramaters (in, type int32): OriginalPosition

1 Like

Hi @Nazarius,

Here is an activity to convert the csv to excel and you can able the change the column datatype. And it has video explanation also.

Regards
Balamurugan.S

Thanks Balupad,

Appreciate your help :slight_smile:

1 Like

@Dave Could you put it in xaml also to make sure I understand it correctly?

And also I want the new column with the string data type to be in the same position the old one was!

Sure thing, here you go - it’s untested but is simple enough it should be working. Let me know if you have any questions

the.christopoulos.xaml (9.9 KB)

Thank you @Dave. I used the flow and again although the values are fine with nulls. eg 00420
when I write to csv it again becomes 420 withou nulls.

Hi @the.christopoulos,
The same problem @CamiCat had it. That is reason I have developed the Csv activity. That Y I have include the camicat name in the package. Take a look carefully. You have the solution in the activity.

Regards
Balamurugan.S

@the.christopoulos - changing the format would require it to be string. If you need the final output to be in string format I would recommend doing all of your processing as numbers in the tempColumn and DONT delete the original string column. Then right before you write range into excel, do a quick for each row in the datatable and assign the value from the tempColumn to the original string column in the format you desire (e.g. 5 digits with a leading zeros if it’s less than 5 digits is .ToString(“00000”)