Wrong format of decimal numbers when reading csv - possibly bug

Help Activities
, ,
1

Hi! I have a problem writing a csv file into excel file because I get decimal numbers with both decimal separators (“,” and “.”)

My steps:

  1. Reading csv file as a string (activity ‘Read text file’)
  2. String is converted into datatable
  3. Writing that DT into Excel

First picture shows csv column, and the second picture the same column written in excel.

csv image

Notice a difference in decimal separator within numbers with 3 decimal places. It needs to be with “,” as a decimal separator like in a first row!

This picture shows csv values in dt variable:
DT

Csv file is a table with more than a hundred columns and It’s not an option to convert every single column with numbers into double format. It would take too long :frowning:

Is this a bug maybe?

2

Hi @dhubak

Try this syntax. In the mean while can you share us sample input.
→ Read Text File
Output-> csvContent
→ Use below syntax in Assign

Assign -> dataTable = (From line In csvContent.Split(Environment.NewLine.ToCharArray())
                Let adjustedLine = line.Replace(".", ",")
                Select adjustedLine.Split(","c)).CopyToDataTable()

→ Write Range Workbook

Regards

3

Hi,

Can you try the following sample?

image
image710×706 25.2 KB

dt.AsEnumerable.ToList().ForEach(Sub(r)
   r("Value")=r("Value").ToString.Replace(".",",")
End Sub
)

Sample
Sample20240211-3.zip (3.0 KB)

note: this sample converts data in “Value” column

Regards,

4

Thank you for a quick response :wink: This forum does not allow to share csv files :confused:

5

I tried to test your solution but it gives me an error - ‘Split’ is not a member of ‘System.Data.Datatable’

1 Like
6

Hi @dhubak

→ Read Text File

image
image488×621 8.08 KB

Output-> csvContent
→ Generate Data Table From text
image
image1108×795 19.4 KB

→ Use below code in Invoke Code

' Iterate through each column in the DataTable
For Each col As DataColumn In dt.Columns
    ' Check if the column is of type Double (or any numeric type)
    If col.DataType Is GetType(Double) Then
        ' Iterate through each row in the DataTable
        For Each row As DataRow In dt.Rows
            ' Perform the replacement operation for the specific column in each row
            row(col) = row(col).ToString().Replace(".", ",")
        Next
    End If
Next

Invoked Arguments:

image
image888×260 9.37 KB

→ Write Range Workbook
image
image258×546 4.72 KB

Check he below workflow
Sequence39.xaml (8.9 KB)

Regards

7

“,” is a decimal separator in input csv file. I don’t need to replace the dots (“.”)

8

Hi,

Can you elaborate? Your input file has 2 type decimal separator (“,” and “.”), right?
The above expression replace dot with comma in “Value” column to unify single decimal separator “,” Isn’t this same as your intent?

Regards,

9

Yes I can :wink:

My input csv file has ONE type of decimal separator, and it is “,” - first picture!

When I write it to Excel, the output has TWO tyes of decimal separator - second picture - and that is the problem.

The third picture shows dataTable with csv data red correctly

10

Hi @dhubak

May be you could try this

Regards

11

Hi,

Do you use GenerateDataTable activity? If so, turning off AutoDetectTypes may help you.

image
image406×570 26.7 KB

Regards,

12

Nice try :wink: But…

As my csv file contains more than a hundred columns, some of them have the same name. So, when I’m generating a datatable I don’t use option ‘Use headers’ because it gives me an error that I have duplicate columns. So, my first row is with the actually column names. I think its the reason why robot can not detect column type.

After I generate a data table, I use first row values with column indexes to rename columns in the whole DT. But then I don’t know how to check wether is column a type ‘double’.

If you get what I mean…

13

I tried every combination :frowning:

14

Hi,

Which WriteRange activity do you use? At least, WriteRangeWorkbook seems to work well. How about using this?

Regards,

1 Like
15

Is it possible that WriteRangeExcel has a bug?!?!?!

Right now I used WriteRangeWorkbook and it gives the CORRECT result…how is it possible?

16

Hi,

This may be good to unify decimal separator to dot as it seems locale matter.

Can you try to the following invokeCode to replace commna with dot

dt.AsEnumerable.ToList().ForEach(Sub(r)
   r("Value")=r("Value").ToString.Replace(",",".")
End Sub
)

image
image758×383 16.1 KB

Then use WriteRangeExcel?

Regards,

17

Yes, it is a local matter. We use “,” as a decimal separator. That’s why I can not replace “,” with “.”

Seems like the usage of WriteRangeWORKBOOK instead of WriteRangeEXCEL is a solution. But, that means that WriteRangeEXCEL has a bug?!

18

Hi,

I think it’s specification. UiPath works under Invariant culture, we need to use Write Range assuming it.

The following is an example of similar matter. Even if we use Spanish environment, we need to use =TODAY() instead of =HOY() in UiPath.

So, in this case, replacing comma with dot may solve this matter, too.

Regards,

19

Ok, I get it.

But still…why some of the numbers are written correctly as in my first post, and some of them are not when using WriteRangeExcel?

20

Hi,

Because if there are comma and 3digits, the comma is recognized as thousand separator in invariant culture. So it’s recognized some number larger than 1000. As thousand separator is dot in your locale ,it’s displayed finally.

Regards,

1 Like