Rounding off of values when datasheet written to excel

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:

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

1 Like

@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.

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

@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.

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

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

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})
1 Like

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.