Rounding off of values when datasheet written to excel

Help Studio
,
1

Hello, i am facing a challenge with the write range activity. I have 2 datatables that I am comparing using a linq query. The result of that query is a new datatable that contains rows found in one datatable and not the other. When I then write the new datatable to an excel sheet one of my columns, which contains numbers with 16 characters, is rounded off by excel. eg 5049870013795225 becomes 5049870013795220. Is there a way for me to keep the exact values for that column when i write to excel. Thanks in advance :slight_smile:

2

You can force Excel to interpret it as a string rather than a number by adding ’ in front of it.

1 Like
3

@sonnymeyer, thank you for the resonse. I first perform a read range from an excel sheet that has ’ infront of the numbers. But when i write the table back to excel, it does not have the ', and so excel rounds the numbers off.

4

Integer values cannot be that big, if you are not doing any math operations with that number, in you linq query that generates that datatable, use a ToString, so that column will be a String and not number.

1 Like
5

@bcorrea, thank you for your response. I am not manipulating that column. The link query compares values in another column in the same row. But when i write back to excel its rounded off. Let me try to give an example.
I have two tables, simliar to the example below. I read these two tables then compare them using column A as a unique identifier. The result from the linq query contains the rows that are found in the first table but are not present in the second table using column A as the identifier. So now whats happening is that the resulting excel sheet rounds off the values in column B, but i need the exact values. I hope that explains better.
example

6

I understand well what you need, what is the linq query you have now?

7

This is the linq query below. I’m using two columns as identifiers - Amt and RRN

dt2.AsEnumerable().Where(Function(row) Not dt1.AsEnumerable().Where(Function( r) r(“RRN”).ToString.Equals(row(“RRN”).ToString)And r(“Amt”).ToString.Equals(row(“Amt”).ToString)).Any).CopyToDataTable

8

I know you can convert the “columns” to string using a query like this:

Dim result = dt1.AsEnumerable().Where(Function(row) Not dt2.AsEnumerable().Where(Function(r) r("RRN").Equals(row("RRN")) And r("Amt").Equals(row("Amt"))).Any).Select(Function(r) New With {
            .RRN = r("RRN").ToString,
            .Amt = r("Amt").ToString,
            .Name = r.Field(Of String)("Name")
        })

But to be honest i dont know if it will help you as it wont be a DataTable. Then one could do like this to make it a DataTable with code:

Dim tblResult As DataTable = dt1.Clone()
For Each item In result
    tblResult.Rows.Add({item.RRN, item.Amt, item.Name})
Next
1 Like
9

Thank you so much for the assistance.Let me try to use this and see if I get the desired result. I will get back to you soon.

10

Any solution?

11

feel free to open a new topic for your case as in general available options were discussed. When creating a new topic we can adress individually your case