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.
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.
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.
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.
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\";");
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....
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With