none
Reading Excel : does not retrieve string values RRS feed

  • Question

  • Hi, I am trying read an excel using oledb in C#, I have a column which holds alpha numeric data, my first 5 cell has number after that string , after reading string gets blocked, this is the below connection string used

    <add name="XL_DEFAULT" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties = 'Excel 12.0 Xml; HDR = YES; IMEX = 1;'" />

    using (dbXLConnection = new OleDbConnection(sXLConnection))
          {
           dbXLConnection.Open();
           using (XLAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", sXLConnection))
           {
           XLAdapter.Fill(XLDataSet);

    the string values is getting emptied.. My connection string holds the proper values

    please let me know if there are any issue or a solution for this issue.....

    • Moved by Kristin Xie Friday, December 19, 2014 5:16 AM move to better forum
    Thursday, December 18, 2014 8:02 AM

Answers

All replies

  • Hi Aneesh,

    Based on your description, your case related to ADO.NET Providers, so i moved your case to

    Data Platform Development > ADO.NET Managed Providers forum for better support.

    Have a nice day!

    kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 19, 2014 5:18 AM
  • Hello Aneesh,

    It seems that you meet the excel 8 rows limit, I am not sure the issue you encounter is same, however, I this you could check it, this article describes this issue:

    http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html

    The solution is to set the typeguessrows in the regedit from 8 to 0.

    If it does not work for you, please let me know.

    Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 19, 2014 6:10 AM
    Moderator
  • You may also need to change the ImportMixedTypes setting in the Registry:

    http://dougbert.com/blog/post/Importing-from-Excel-IMEX-and-mixed-data-types.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, December 23, 2014 2:07 PM