How to read a data row from Excel with more than 255 characters

Question

The issue is When I set up the DataSet as shown below, the default reads only 255 characters from the spreadsheet cell and places them into the table.

DataSet exData = new DataSet();
string connectionString = String.Format(ConnectionString, path);
OleDbDataAdapter ex = new OleDbDataAdapter(ExcelQuery, connectionString);
ex.Fill(exData);

I'm using Extended Properties=Excel 8.0 and Microsoft.Jet.OLEDB.4.0, there is no problem connecting to the excel sheet.

From my reading it follows that it is due to the Jet.OLEDB provider, what should I be using?

And I may be unable to update to new provider of Jet, is there any workaround? Any workaround would would be restricted on not being able to directly modify the Excel document to contain two cells or more for data over 255 chars.

Thanks.

Answer 1

Use ExcelDataReader and add reference in your project. and use below code to read Excel more than 255 columns...

FileStream stream = File.Open(strFileName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
excelReader.Close();
return result.Tables[0];

Answer 2

I had the same problem here and I'd found out that, by default, only the first 8 rows are used to set the type of column. If you have a larger string on any other row bellow the 8th, it will be truncated.

You just need to Run Regedit and go to :

32-bits versions:

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

Or

64-bits versions:

HKEY_LOCAL_MACHINE\SOFTWARE\wow6432node\microsoft\jet\4.0\Engines\Excel

There, change the TypeGuessRows to 0, so the Jet engine will use all the rows to set the data type.

Source: Why SSIS always gets Excel data types wrong, and how to fix it!

Answer 3

The same issue is described here.
Any specific reason why you are not considering using the Excel interop?

Answer 4

The standard max length of a text column in jet is 255 characters. I haven't tried this, but if you create a schema.ini file and tell it that your column is a memo type, you might be able to put more data in.

Answer 5

• Form the following pictures you can find how to add Excel reference library in your project.

• Select Add Reference dialogue from Project menu of your Visual Studio

• Select Microsoft Excel 15.0 Object Library of COM leftside menu and
  click OK button

• After import the reference library, we have to initialize the Excel application Object.

      using System;
      using System.Windows.Forms;
      using System.Runtime.InteropServices;
      using Excel = Microsoft.Office.Interop.Excel; 
  
  namespace WindowsFormsApplication4
  {
      public partial class Form1 : Form
      {
          public Form1()
          {
              InitializeComponent();
          }
  
          private void button1_Click(object sender, EventArgs e)
          {
  
          Excel.Application xlApp ;
          Excel.Workbook xlWorkBook ;
          Excel.Worksheet xlWorkSheet ;
          Excel.Range range ;
  
          string str;
          int rCnt ;
          int cCnt ;
          int rw = 0;
          int cl = 0;
  
          xlApp = new Excel.Application();
          xlWorkBook = xlApp.Workbooks.Open(@"d:\csharp-Excel.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
          xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
  
          range = xlWorkSheet.UsedRange;
          rw = range.Rows.Count;
          cl = range.Columns.Count;
  
  
          for (rCnt = 1; rCnt  < = rw; rCnt++)
          {
              for (cCnt = 1; cCnt  < = cl; cCnt++)
              {
                  str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                  MessageBox.Show(str);
              }
          }
  
          xlWorkBook.Close(true, null, null);
          xlApp.Quit();
   }
  
  }
  

  }

Answer 6

Data type lenght will be limited to 255 characters as Interop uses the first eight column to set the limit.

A shortcut approach to tackle this is ,just make the longest column in as the first column or any of hte first eight columns in excel. So that the limit will be set to that of the longest column