Read Range mess up numbers when read from file

Help
,
1

Hi

I have a quick question I hope someone can help me with.

I am using Read Range to read a file into a DataTable.

But when the file has been read into the DataTable it has “converted/changed format” on some of the numbers, as you can see below.

For when I open the file manually in Excel, it has the correct number formatting, as you can see below.

number format

I need to keep the number format after I have read the file into a DataTable (or before i write it back to file), how can I achieve that?

2

Hi Finken,

Can you Please Share your Excel File and Work Flow?

Thanks

3

I am not able to uplod the file since I am a new user :angry:

4

The workflow is simple:
Read Range
Remove data row
Write Range

An as shown in the picture in the post, read range changes the number format for lagre numbers
like these ones: 107374283105128081
So when it write it back it is like this: 1,07374E+17

I dont want it to change the number format, how can I avoid this when reading the file into a datatable?

5

just put a single quote before the numbers in excel file.

6

The excel file is not manually created, it is downloaded.
How can I make changes to the data in the excel file before I read into a datatable, and the numbers format are already changed?

7

as I can see from first screenshot data is not in table format, so before converting data to table add single quote at starting.

8

Can you please provide an example of what you mean?
Where in the process should a single quote be added, before read range is used?
In such a case which activity should be used for that?

9

can you give me sample data of any single row from first screenshot (data before reading the excel file)
or simply give us the format in which you receives data like number of column values in columns etc so we can create sample file.

10

3087171919101704a3,NO10099988997300,NOT_INSERTED,110402,xxxxx yyyyy ANS,2022-10-16,Rack 6 Plass 20
107374283105128081,NO10009987765500,ONLINE,5500539,113433,zzzzzz jjjjjjj AS,2018-02-22,Rack 2 Plass 30

Here are examples of two rows in the file. The first number in some of the rows are the numbers that are formatted when read into a datatable with read range. In this case it is the number in the seceond row that are formatted, and I guess it is because it it a long number

11

hello @Finken try this and let us know if this is what you need.
Numbers.xaml (12.2 KB)

update: please delete create directory activity from workflow, I forgot to delete it.

12

Hi,

yes this is bringing me closer to the solution.
The problem now is that the file is .csv so readrange does not work, neither do readCSV, because the files is badly formatted from the source. That is actually the reason why I have to open the file and remove the first row.
When i open the file manually in Excel and store in .xls or xlsx, then your solution works fine. But my problem is that I am not able to read the file with readrange. Any suggestion on how I could read the file?

13

Hello @Finken save input file with .txt extension and give this a try

Numbers (1).xaml (13.1 KB)