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.

score 4 · Answer 1 · answered Jun 12 '15 at 05:38

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];

score 3 · Answer 2 · answered Oct 01 '14 at 16:02

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!

This workaround to the problem could be pretty useful. Our solution at the time was to just make sure we sorted our columns such that the largest strings were in the first 8 rows. — Joshua, Oct 02 '14 at 12:46

score 1 · Answer 3 · edited May 23 '17 at 12:08

1

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

edited May 23 '17 at 12:08

Community

1
1

answered May 12 '11 at 14:59

weismat

7,195
3
43
58

Thanks for the pointer for Excel interop! I am attempting to make very minimal changes to an existing project. If the interop can be inserted cleanly it will be nice. Major modifications are out of the question at this point in time. – Joshua May 12 '11 at 15:08

score 1 · Accepted Answer · answered May 12 '11 at 15:11

1

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.

answered May 12 '11 at 15:11

Alan Jackson

6,361
2
31
32

1

This lead me to finding ways to convert the column to memo. In the end we found that we could simply force the row that had the string in question to the top of the file. This edit makes the row get considered as a Memo, and yields all the information. Thank you. – Joshua May 12 '11 at 17:50

score 0 · Answer 5 · answered Dec 22 '17 at 07:32

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();
 }

}

}

we can read a data row from Excel with more than 255 characters — Papun Sahoo, Dec 22 '17 at 07:33
this question was in regards to a specific scenario for Extended Properties=Excel 8.0 and Microsoft.Jet.OLEDB.4.0 — Joshua, Dec 26 '17 at 15:36
I know about questions and many persons has answered. but I am summarizing the things for understanding with different way. — Papun Sahoo, Dec 27 '17 at 06:42

score 0 · Answer 6 · answered Dec 26 '18 at 09:54

0

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

answered Dec 26 '18 at 09:54

Kailas

439
1
5
14

This is correct. As @baguiar pointed out and in my reply to him the first 8 columns can force the field type to be initialized as a longer type. This was by far the simplest solution at the time. Though if I recall correctly newer versions of Jet had better support for a schema definition. – Joshua Dec 31 '18 at 15:56

score 0 · Answer 7 · answered Sep 23 '22 at 07:51

0

If you have schema.ini to work with, use Memo datatype instead of Text for the column that stores strings larger than 255.

answered Sep 23 '22 at 07:51

Tomas Trdla

1,142
1
11
24

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

7 Answers7