Conversion to CSV has an issue with values

Hello everybody.

I am doing the below:

1)read data from csv and get them in data table
2)do all proper conversion and edits into the data
3)Convert back to csv again

I am having a problem with the csv conversion in the end: there are some columns that have digits like : 0000456 or 004535

In the final csv file the 0 is deleted and the value is 456 without the nulls, which is not what I want to do.

Please help!!!

Hi @the.christopoulos,

While editing the data which datatype the column holds, if it is a integer or double format, then this will happen. You need to have that column in string format. Or before writing it to csv you need to have this column in string format.

@sarathi125 How do I convert all rows of the datatable in string?

in your original CSV, you should wrap the digits that you want to keep the 0s in the left with β€˜β€™, like β€˜000123’.

@bcorrea the original csv does not have 0s.

I add 0s after having converted to datatable.

i mean if you add zeroes to the left and the datatable column is of type β€˜numeric’ it will go away :wink:

@the.christopoulos,

Once you did with your edits, create a β€œFor each row” loop where you will add an apostrophe (’) before each value which you want to retain as text and then use Write Csv Activity.

This worked for me.

Right, thats what is happening.

How can i convert the whole datatable column into string before creating the csv file?

that seems a trick @sarathi125.

How will I do it? what command will i use inside the for each loop?

as i told you, if you put the numbers like β€˜12122’ it will treat as a Text column and you add new numbers like this β€˜000123’ and when it is a csv again it will retain the zeroes…

@sarathi125 I did as you said for the datatable but then when i create the csv the values remain with the ’

@bcorrea Any advice bruno how to retain the 000 when converting to CSV?

If you have control over the format of your source CSV, then all fields you want to force as TEXT, you can add like this: β€œ=β€β€œ0012324234"”", because it is the nature of csv conversion to auto detect the columns type, if you have a number there then datatable you have a Int32 column type and therefore will not accept the leading zeros…

@bcorrea The source file is excel. Is there any workaround on this? :frowning:

But the source file extension is a xlsx or csv? if it is xlsx, then it is easy to just format the column as text, if it is csv, you need to put the values as β€œ=β€β€œ0012324234"”"…

@bcorrea the source is excel!
How can I format the columns then in order to create csv in the end with numbers like : 000787 ?

@bcorrea Hahaha! I thought you meant with a command or sth else :slight_smile:

1 Like

no no, that you will enough, if you can put those 000123 there (excel will paint that green triangle to warn that data looks like number but is text), than when you convert to datatable it will be text do you can do whatever with it :slight_smile: