Cange CultureInfo for every column in datatable

[markosc]

Hi,

Since I’m working with csv file, reading as datatable and then writing it into excel file, I need to set culturinfo to Double.Parse(CurrentRow.ToString, System.Globalization.CultureInfo.CreateSpecificCulture(“es-ES”)).ToString because in csv file the number is read like 5,256 but when writing it to excel it writes it like 5256.

I solved this by adding assign activity:

I have a lot of columns and I don’t want to do this for every column. Please help.

[ppr (Peter)]

is it needed for ALL columns or a col set?

[markosc]

For all columns

[ppr (Peter)]

can you check:
Assign Acitvity:
CurrentRow.ItemArray = CurrenRow.ItemArray.Select(Function (x) Double.Parse(x.ToString, System.Globalization.CultureInfo.CreateSpecificCulture(“es-ES”)).ToString).toArray

[markosc]

In for each row?

[ppr (Peter)]

yes within the for each row we convert it on ItemArray level.

Just use the assign from your screenshot and replace the left side / right side as mentioned above

[markosc]

It returns this error

Assign: Input string was not in a correct format.

[ppr (Peter)]

this is about the values you send to the Double.Parse(… method and not about the appraoch in general.

just check your data on empty blanks or non valid strings causing a double parsing fail

[markosc]

There are always some empty rows/cells

[ppr (Peter)]

then always double.parse would fail an these blank values

We could rewrite when e.g. for empty / non valid data default value e.g. 0 can be used. Would this be ok with your business case

[markosc]

What if we do it for set of columns and not ALL columns?

[ppr (Peter)]

this will not change the fact, that an empty invalid string cannot be parsed into a double.

You can set up a defensive approach by following:

Ensure and add if it not added:

inside the for each row in datatable we add a for each and loop over all cols

if condition:
Double.TryParse(item.ToString.Trim, NumberStyles.Any, new CultureInfo("es-ES"),nothing)

then branch:
Double.Parse(item.ToString.Trim, new CultureInfo("es-ES")).ToString

else branch:
as screenshot

for each properties:

[markosc]

Now it removed the data with only text, but did nothing to cells as 5,256.

[ppr (Peter)]

maybe you can share your implementation details (xaml, screenshots) and some sample data

[markosc]

Example.xlsx (10.2 KB)

here is an example, but it should be in csv format.

Sequence.xaml (11.4 KB)

[ppr (Peter)]

we dont see that our suggestion was tried and implemented as the body block is empty

to keep the value of all non double values and/or empty cells, just remove the assign from the else branch

about the condition

about the conversion:

So in general we had shown every main bulding block and you can adapt and complete your implementation

[markosc]

Can you tell me how to do it only for certain columns because i realise that it only happens in two columns for example column AA and AB.