none
Read Excel cell with leading apostrophe

    Question

  •  

    Hi all,

     

    I have a neat problem here. I'm creating a C# 2005 program that will read from an Excel Spreadsheet using ADO.Net. I can connect, and I can generally see the values of the cells.

     

    Generally?? I noticed that some times what I could see through code for the value of the cell was Null. For other cells in the same column, I would get a value.

     

    I've traced this down to the cell having a leading apostrophe within it. Any time the cell starts with an apostrophe (to stop Excel from formating the contents), I cannot programmatically see the value of the cell.

     

    I've tried using the DataAdapter and the DataSet - and can work with this as expected for all cell, except those that have this leading apostrophe.

     

    So, my question is: Is there a way to use C# + ADO.Net to read all cells within an Excel spreadsheet - regardless to the leading apostrophe?

     

    I really don't want to have to programmatically open Excel, and then ask it to export out into an XML or CSV if at all possible.

     

    Thanks!!

     

    James D.

    Friday, November 21, 2008 5:42 PM

Answers

  • I'm not sure that what you're seeing isn't normal. Do you only see the apostrophe in the formula bar when viewing the Worksheet in Excel? If this is the case then it's present to designate the column as text.

     

    Also, does the column in question have a mix of numeric and alphanumeric characters? If so, then you will need to specify the IMEX argument in your connection string. You can find a connection string example at the below link:

     

    http://www.connectionstrings.com/?carrier=excel

     

    Friday, November 21, 2008 11:44 PM
  • Keep in mind that Excel is not really a database where each column has its own data type. In Excel cells in one column can contain data with different types. In a case of Jet OLEDB provider, it tries to guess data type for the entire column based on scan of first number of rows, and if, for example, it defines column as numeric, but down the road gets some values which are strings, it would return NULL values. That is basic example, but you should get an idea about how it works. One way to force to retrieve all the values (without NULLs) is to set IMEX property of the connection string to 1, but in this case all the values would be treated as strings, not actual numbers or dates will be returned and you would need to convert values to their actual types in your code. You could also try my .NET managed reader for Excel that loads all the values from spreadsheet using their actual data types. You can download it from my web site

    Monday, November 24, 2008 11:52 AM
    Moderator
  • Hi again

     

    Great stuff - the IMEX flag was just the ticket, with a few extra single quotes:

    string connPath = @"C:\Documents and Settings\davidsonj\Desktop\Destination\CL Area 06 Line List_Re-IFC_Rev.7_Oct30_2008.xls";

    conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +

    "Data Source=" + connPath + ";Extended Properties='Excel 8.0;IMEX=1;'");

     

     

    Everything is coming through as expected!

     

    Thanks for everyone's help!!

     

    James D.

    Monday, November 24, 2008 4:51 PM

All replies

  • Hey James,

     

    I believe this isn't a Dataset problem per say. Which type of DBConnection are you using? Is it an OleDb connection? For the cells that are null and the cells that are empty perhaps this is because the cells once touched and erased by a user go to empty whereas an untouched cell is null.

     

    I am not exactly sure how the Jet provider works in Excel. I hope I've given you some clues to figuring some of this out. I am going to forward this question to the Managed Provider team. They will be able to give you more information on the datareader's behavior with Excel. I would indicate to them in a clearer way which cells are not able to be accessed.

     

    Thanks

    Chris Robinson

    Program Manager - DataSet

     

    Friday, November 21, 2008 7:10 PM
  • I'm not sure that what you're seeing isn't normal. Do you only see the apostrophe in the formula bar when viewing the Worksheet in Excel? If this is the case then it's present to designate the column as text.

     

    Also, does the column in question have a mix of numeric and alphanumeric characters? If so, then you will need to specify the IMEX argument in your connection string. You can find a connection string example at the below link:

     

    http://www.connectionstrings.com/?carrier=excel

     

    Friday, November 21, 2008 11:44 PM
  • Keep in mind that Excel is not really a database where each column has its own data type. In Excel cells in one column can contain data with different types. In a case of Jet OLEDB provider, it tries to guess data type for the entire column based on scan of first number of rows, and if, for example, it defines column as numeric, but down the road gets some values which are strings, it would return NULL values. That is basic example, but you should get an idea about how it works. One way to force to retrieve all the values (without NULLs) is to set IMEX property of the connection string to 1, but in this case all the values would be treated as strings, not actual numbers or dates will be returned and you would need to convert values to their actual types in your code. You could also try my .NET managed reader for Excel that loads all the values from spreadsheet using their actual data types. You can download it from my web site

    Monday, November 24, 2008 11:52 AM
    Moderator
  • Hi all,

     

    I'm connecting to the spreadsheet like this (temporary code):

     

    private static System.Data.OleDb.OleDbConnection conn;

     

    <in the method>

    string connPath = @"C:\Documents and Settings\davidsonj\Desktop\Destination\CL Area 06 Line List_Re-IFC_Rev.7_Oct30_2008.xls";

    conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +

    "Data Source=" + connPath + ";Extended Properties=Excel 8.0;");

    conn.Open();

     

    <...>

     

    Once I can read all the cells, I'll change the path to be whatever is passed to the method.

     

    I would be okay with reading the cells all as text - as that is generally what the data is (Size='1 1/2' for example).

     

    I'll try the IMEX flag, and see if that corrects the situation or no.

     

    Thanks!

     

    James D.

    Monday, November 24, 2008 3:15 PM
  • Hi again

     

    Great stuff - the IMEX flag was just the ticket, with a few extra single quotes:

    string connPath = @"C:\Documents and Settings\davidsonj\Desktop\Destination\CL Area 06 Line List_Re-IFC_Rev.7_Oct30_2008.xls";

    conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +

    "Data Source=" + connPath + ";Extended Properties='Excel 8.0;IMEX=1;'");

     

     

    Everything is coming through as expected!

     

    Thanks for everyone's help!!

     

    James D.

    Monday, November 24, 2008 4:51 PM