Cange CultureInfo for every column in datatable


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.

is it needed for ALL columns or a col set?

For all columns

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

In for each row?

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

It returns this error

Assign: Input string was not in a correct format.

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

There are always some empty rows/cells

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

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

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:

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

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

Example.xlsx (10.2 KB)

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

Sequence.xaml (11.4 KB)

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

1 Like

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.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.