Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Parsing an Excel file in C#, the cells seem to get cut off at 255 characters... how do I stop that?

I am parsing through an uploaded excel files (xlsx) in asp.net with c#. I am using the following code (simplified):

string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connString);
DataSet ds = new DataSet();
adapter.Fill(ds);
adapter.Dispose();
DataTable dt = ds.Tables[0];
var rows = from p in dt.AsEnumerable() select new { desc = p[2] };

This works perfectly, but if there is anything longer than 255 characters in the cell, it will get cut off. Any idea what I am doing wrong? Thank you.

EDIT: When viewing the excel sheet, it shows much more than 255 characters, so I don't believe the sheet itself is limited.

like image 432
naspinski Avatar asked May 29 '09 15:05

naspinski


People also ask

Can we read Excel file in C?

Solution 1: Search for a library that can enable you reading . xlsx file. Solution 2: Write your own parser for . xlsx format. .xlsx is an open format. .xlsx is an ooxml format, so it is essentially a zip formatted compressed file.

How do I convert Excel to XSD?

Go to the Excel file and click on the developer tab, then go to source. This will add a panel to the right, which will allow you to add your XSD by clicking on “XML Maps” at the bottom right. The XML Maps window will open. Click on “Add” and add your XSD.

Can Excel be converted to XML?

Excel can export data into XML (or Extensible Markup Language). It is based on an XML schema, which defines the structure of the file. You can create it in NotePad. To convert the data, go to the Developer tab – Source.


1 Answers

The Solution!

I've been battling this today as well. I finally got it to work by modifying some registry keys before parsing the Excel spreadsheet.

You must update this registry key before parsing the Excel spreadsheet:

// Excel 2010
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\
or
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\

// Excel 2007
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\

// Excel 2003
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\

Change TypeGuessRows to 0 and ImportMixedTypes to Text under this key. You'll also need to update your connection string to include IMEX=1 in the extended properties:

string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";");

References

http://blogs.vertigo.com/personal/aanttila/Blog/archive/2008/03/28/excel-and-csv-reference.aspx

http://msdn.microsoft.com/en-us/library/ms141683.aspx

...characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key....

like image 129
Andrew Garrison Avatar answered Oct 08 '22 00:10

Andrew Garrison