1

I am trying to read a sheet data from excel (tried both oldedb and odbc) and found out that there is 255 characters limit. I tried using Range object as suggested in other threads

(SELECT * FROM [ref_MethodInput$A1:S362])

but still no luck. So apart from using interop is there any other way to overcome this? Registry edit is also not advisable as that would need registry edits in all client machines.

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
f444ran
  • 15
  • 1
  • 6

2 Answers2

0

Since the issue is with the Jet Provider, why not try the Microsoft Excel Driver listed under ODBC connctions.

Source

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • i am doing this from my C# application. Could you pls provide a link/example on how how i go about this. – f444ran Feb 03 '14 at 15:05
  • http://stackoverflow.com/questions/4488404/excel-unable-to-insert-more-than-255-chars – Vignesh Kumar A Feb 03 '14 at 15:08
  • thanks for the links. i adapted connection string from there but still it fails . The long string is on 101 line OleDbConnection excelcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=200';"); – f444ran Feb 03 '14 at 15:27
0

below code work for me. import data from excel (.xls & .xlsx) file (work for column value more than 255 character).

using Excel;

        try
        {
            FileStream stream = File.Open(strFilePath, FileMode.Open, FileAccess.Read);
            IExcelDataReader excelReader = null;
            if (extension.Trim() == ".xls")
            {
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            }
            else if (extension.Trim() == ".xlsx")
            {
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            }
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();

            excelReader.Close();
        }
        catch (Exception err)
        {
            mResult.Message = err.Message;
        }

Download nuget packages: 1) PCLStorage (Version: 1.0.2) 2) ExcelDataReader-DevNet45 (version: 1.0.0.2)

Code reference

SadikAli
  • 594
  • 1
  • 4
  • 21