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.
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
@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
Please help guys
@the.christopoulos yes, and @NazAutomate 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!
@NazAutomate 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
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.
@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â)