Wrong number format when reading CSV

Hi,

I am trying to get a number from csv- or xlxs- files. I’ve tried finding solutions on the forum, but with minimum success. In the files I have different numbers of length 20, i.e. 29102938891002091829. If I open the file in excel, it is displayed as 2.91E19, and that is fine because the rest of the digits are stored in the metadata.
If I follow the approach of “Open Spreadsheet” (xlxs) -> “Get Cell Value” (at coordinate) and save to a variable, it is however stored as 29102938891002090000. Even if I try to reformat it from the regex to correct format, I lose the 4 last digits.
If I read from CSV (stripping elements on “,”, and simplifying through foreach loops), I can get the numbers assigned to correct variables. No issues getting the variables, and the last 4 digits are still maintained.
After I’ve gotten the variables/numbers, I check the length of the variable. It is important that they are equal to 20. The csv-files comes from the same data source, and on the same format. Store length of String containing number as varlength, and do if-else to see if it is “=20”. However, sometimes “Get Length” will return 19 (I counted, it’s 20), sometimes 20, and sometimes 41 (I counted, it’s 20). That means that the String 29102938891002091829 (from above) can take on at least 3 different lengths. During the “41-length-occurrences”, I did substring split (1,19), and got 29102938. This happens even if I trim whitespace. The digits in the String are counted twice. If anyone has experience anything similar, or has a solution, please chime in with a solution.

Kind regards,
Jon

Hi Jon, can you post a sample .csv or .xlsx file to test?

Hi, this is an example file purely generated for this purpose.

test1.csv (149 Bytes)

@ashapkina, Hi Alesia, any suggestion?

Hi Jon,

If you need to read the number from Excel, you will need to store it in the file as text. In this case it is written correctly and doesn’t lose the 4 last digits.

As for the issue with different string length: if the data is store in the form of a table as in the file attached, when you use “Read file” the recorder reads the new line characters, too, and counts them as a part of the string, so you need to add one more action to further divide the strings with a new line character.

The lengths of all strings are correct

image