none
I can not read an Excel cell having a leading apostrophe within it RRS feed

  • Question

  • I faced such a problem. I trying to read Excel file data, all are as a string. I used code bellow.

    try
    {
        var connectionString = string.Format( "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"", session["FilePath"] );
        using (var adapter = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM [Sheet1$]", connectionString ))
        {
            var ds = new DataSet();
            adapter.Fill( ds, "workBook" );
            workBook = ds.Tables["workBook"];
        }
        if (workBook == null)
            throw new Exception( "Could not load imported spreadsheet!" );
        if (workBook.Rows.Count <= 0)
            throw new Exception( "You are use an empty spreadsheet!" );
        foreach (DataColumn column in workBook.Columns)
            column.ColumnName = column.ColumnName.Trim();
    }
    catch (Exception exc)
    {
    }

    All worked fine, I was getting a datatable with data as a string data type and was parsing them on program level (I just have a mixed data types in one column). But when the cell have a Number format and value of this cell, for example, is 0589, I need to add a leading apostrophe in a cell because 0 must be present in 4-digit number. When I tried read such excel file using a IMEX parameter 1, I have got NULL value from this cell. I don't understand why, I read all data as a string data type.

    I tried using ADODB.Recordset and have got a same result.

    Monday, September 22, 2014 2:43 PM

Answers

  • Hi Sanprof,

    With your provided excel file, it is that you meet the 8 rows limit. If you delete row 9~14 so that the “0589” is in eighty row, then you could see that the “0589” is loaded to the datatable:

    Since you do not want to manipulate with the excel file, you need to change the value of TypeGuessRows.

    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.


    • Edited by Fred BaoModerator Wednesday, September 24, 2014 8:27 AM
    • Marked as answer by Sanprof Wednesday, September 24, 2014 9:55 AM
    Wednesday, September 24, 2014 8:27 AM
    Moderator
  • Thanks, Fred, my search in the internet gave me the same result. As I fixed that, before loading spreadsheet I set the registry key TypeGuessRows to zero on the program level and after loading back to 8 (in case other programs will use it).
    • Marked as answer by Sanprof Wednesday, September 24, 2014 9:56 AM
    • Edited by Sanprof Wednesday, September 24, 2014 9:58 AM
    Wednesday, September 24, 2014 9:55 AM

All replies

  • VSTO doesn't provide anything for this.

    I'd suggest asking Excel specific questions on the Excel for Developers forum instead.

    Monday, September 22, 2014 2:55 PM
  • Hi Sanprof,

    Thanks for posting in MSDN fourm.

    This forum is for developers discussing issues about VSTO. Since the issue is more relative to the ADO.NET Providers, I'd like move it to ADO.NET Managed Providers.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.

    Best regards

    Fei


    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.

    Tuesday, September 23, 2014 2:23 AM
  • Hello Sanprof,

    >>But when the cell have a Number format and value of this cell, for example, is 0589, I need to add a leading apostrophe in a cell because 0 must be present in 4-digit number

    I am confused because as far as I know if it is Number format, the 0 cannot be in the first place. Could you please share your excel file with us? You could upload your excel file to SkyDrive.

    And there is a limit I think I should tell is that please check if the first 8 rows are the same type as Number format and the “0589” is Text format and it is in 8+N place, then the “0589” would not be pulled from excel to memory. You could check this article for a detail description.

    If you are under this scenario, you could do as it suggests: to set the TypeGuessRows property to 0 in order to let the excel driver to use all rows in the file to determine the data types properly. However, modifying the registry is not a good practice, my suggestion is to format your column to all Text format.

    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.


    Tuesday, September 23, 2014 5:49 AM
    Moderator
  • Here is my file https://onedrive.live.com/redir?resid=9BDDC90839CB8C7E!106&authkey=!AAgTLPg0yYM3NWw&ithint=file%2cxls, the column with a bad cell is "Access Code" and value is in the last row. If is it possible, help me to understand what is wrong, my client wants that on his side all worked fine without any manipulation with the excel file.
    • Edited by Sanprof Tuesday, September 23, 2014 7:10 AM
    Tuesday, September 23, 2014 6:39 AM
  • Make sure the ImportMixedTypes setting in the Registry is correct:

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


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, September 23, 2014 5:03 PM
  • Hi Sanprof,

    With your provided excel file, it is that you meet the 8 rows limit. If you delete row 9~14 so that the “0589” is in eighty row, then you could see that the “0589” is loaded to the datatable:

    Since you do not want to manipulate with the excel file, you need to change the value of TypeGuessRows.

    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.


    • Edited by Fred BaoModerator Wednesday, September 24, 2014 8:27 AM
    • Marked as answer by Sanprof Wednesday, September 24, 2014 9:55 AM
    Wednesday, September 24, 2014 8:27 AM
    Moderator
  • Thanks, Fred, my search in the internet gave me the same result. As I fixed that, before loading spreadsheet I set the registry key TypeGuessRows to zero on the program level and after loading back to 8 (in case other programs will use it).
    • Marked as answer by Sanprof Wednesday, September 24, 2014 9:56 AM
    • Edited by Sanprof Wednesday, September 24, 2014 9:58 AM
    Wednesday, September 24, 2014 9:55 AM