Cell values getting truncated while excel upload

Question

I am using following code for excel upload

 OleDbConnection sSourceConnection;
            string properties = "Excel 8.0; HDR=NO; IMEX=1;";//properties set for connection to excel
            string sSourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filePath + ";Extended Properties=\"" + properties + "\"";
            sSourceConnection = new OleDbConnection(sSourceConstr);//creating the OLEDB connection
            try
            {
                //select statement to select data from the first excel sheet
                string sql = string.Format("Select * FROM [{0}]", "Sheet1$");

                //commands to fill the dataset with excel data
                OleDbDataAdapter excelAdapter = new OleDbDataAdapter();
                OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
                sSourceConnection.Open();
                excelAdapter.SelectCommand = command;
                excelAdapter.Fill(dSet, EXCEL_DATA);

I have to upload around 300 records. One column has some text comments. The length of comments varies from 10 chars to 1000 chars. But all of comments above 255 length are getting truncated in that column.

I have used this post Excel cell-values are truncated by OLEDB-provider to change a registry setting, but it didn't work.

I have also tried everything mentioned in the post OleDB & mixed Excel datatypes : missing data, still nothing works.

score 0 · Accepted Answer · answered Mar 18 '13 at 09:10

I was using ACE engine so the correct place to update the registery is

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRowsHKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

score 0 · Answer 2 · edited Apr 23 '18 at 19:41

For Microsoft Office 2010-2013-2016/365

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

And better is to scan for the text ‘TypeGuessRows’ and when you find it, in combination with Excel, set its value to 0. We’ve found another important location for this behavior at this location

\HKEY_LOCAL_MACHINE \SOFTWARE\Microsoft\Office \ClickToRun\REGISTRY \MACHINE\Software \Wow6432Node \Microsoft \Office\16.0\Access Connectivity Engine\Engines\Excel.

Cell values getting truncated while excel upload

2 Answers2