OleDbDataAdapter::Fill is not complete cell data

Question

I am reading excel data using the OleDbDataAdapter for doing this I am using the below code. My excel file has 80 rows and 19 columns. Each column represents different languages(e.g English Arabic, Chinese, etc). Each row has certain strings.

public DataSet ReadExcelFile(string dataSource)
{
    DataSet ds = new DataSet();
    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dataSource
                               + " ; Extended Properties='Excel 12.0; IMEX=1'";

    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = conn;

        // Get all Sheets in Excel File
        DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        // Loop through all Sheets to get data
        foreach (DataRow dr in dtSheet.Rows)
        {
            string sheetName = dr["TABLE_NAME"].ToString();

            if (!sheetName.EndsWith("$"))
                continue;

            // Get all rows from the Sheet
            cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

            DataTable dt = new DataTable();
            dt.TableName = sheetName;

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);

            ds.Tables.Add(dt);
        }

        cmd = null;
        conn.Close();
    }

    return ds;
}

It works perfectly fine except for a few cells. for the few cells table does not have complete string this is happening for the Chinese language: for example, my string is:

“个性化喂养模式”允许您预置常用的喂养模式。一旦设定好 , 当按“模式”键时 , 它将自动出现在喂养模式列表中。
-----------------------------------------------
您可以创建 , 编辑或删除个性化喂养模式。
-----------------------------------------------
提示 : 个性化喂养模式可能会被默认喂养列表隐藏。
-----------------------------------------------
使用“>”键选择需要的喂养模式。"

But I am getting only:

“个性化喂养模式”允许您预置常用的喂养模式。一旦设定好 , 当按“模式”键时 , 它将自动出现在喂养模式列表中。
-----------------------------------------------
您可以创建 , 编辑或删除个性化喂养模式。
-----------------------------------------------
提示 : 个性化喂养模式可能会被默认喂养列表隐藏。
-----------------------------------------------

The last row is missing. This is happening only for 3 cell rest cell are coming properly.

I have never used OleDbConnection to open an Excel workbook and iterate through Sheets and rows. I always use the Microsoft.Office.Interop.Excel Namespace. it has a Workbook class, and a Worksheet class that is designed specifically for working with Excel. Check it out here https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel?view=excel-pia as well as an example of how to use it to read data here https://stackoverflow.com/questions/38531974/how-to-read-the-entire-worksheet-from-excel I am not sure how to address your issue but you may have better luck with Excel Interop. — David.Warwick, Jul 12 '20 at 17:40
Maybe [this](https://stackoverflow.com/a/62839672/10216583) and [this](https://stackoverflow.com/a/62711371/10216583) give you something to go. — , Jul 13 '20 at 00:08

score 0 · Answer 1 · answered Jul 12 '20 at 22:40

It seems that it is being truncated to 255 characters.

According to this Microsoft Oledb truncates the data length to 255 characters

When you use OLEDB providers then the datatype is determined automatically by the provider based on the first 8 rows. If you have lengthy cells in the first 8 rows then data type will be set as text and otherwise it will be memo type which can hold 255 characters only. To overcome this issue either change the registry setting as mentioned in below KB article: http://support.microsoft.com/kb/281517 or use Microsoft.Jet.OLEDB provider to read the data.

Or you may try the OpenXml approach. Parse and read a large spreadsheet document (Open XML SDK)

OleDbDataAdapter::Fill is not complete cell data

1 Answers1