Converting excel table to C# DataTable is cropping off some data

Question

i am using the following code to convert an excel set of data on a worksheet to a C# dataTable:

 var connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _filename + ";Extended Properties=\"Excel 12.0;HDR=YES\";");
        var adapter = new OleDbDataAdapter("SELECT * FROM [owssvr$]", connection);
        connection.Open();
        var ds = new DataSet();
        adapter.Fill(ds);
        connection.Close();
        return ds.Tables[0];

This seems to work fine but when i loop through the dataTable to extract value out, i have one field that is about 400 characters and i when i grab it from the datatable i only see the first 255 characters.

Is there any way i can import this from excel with out this character limit?

Answer 1

OK. Are you sitting down?

I'm about to tell you something that you are not going to like.

You have to edit the registry in order to fix this. And from what I've read, you still need to do this even if you are using the newer non-Jet driver. Yes, it's terrible. No, I have no idea why they designed it this way. This has burned me before on a number of use cases in the past.

The only real solution IMO is to not use this driver at all and use XLSX files instead, which are XML files wrapped up in a ZIP package and renamed with the XLSX extension. When given one of these files, you could either use the Office Open XML to read and manipulate it, or you could just unzip the package with SharpZibLib, find the relevant data file, and read it as XML, which I sometimes find simpler, believe it or not.

Answer 2

Well, are you still sitting down? So please keep sitting!

Unfortunately, Dave is right, but I would go further and would get rid off OLEDB and replace it with a robust 3rd party component. I can recommend Spire.XLS. It was tested in various scenarios and was found as the most convenient and reliable one.

Hope this helped