Tips for reading Excel spreadsheets using ADO.NET

January 4th, 2006

Microsoft ADO.NET provides a handy, if quirky way to access Excel spreadsheets from Windows applications. The idea is to treat spreadsheets like databases, with each worksheet represented as a "table". Worksheets are expected to be in a table-like format with column headings in the first row and rows of data beneath. For example, the following code reads worksheet "foo" from spreadsheet file C:\BAR.XLS into a DataTable:

DataTable fooData = new DataTable ();
OleDbConnection dbConnection =
  new OleDbConnection
    (@"Provider=Microsoft.Jet.OLEDB.4.0;"
     + @"Data Source=C:\BAR.XLS;"
     + @"Extended Properties=""Excel 8.0;HDR=Yes;""");
dbConnection.Open ();
try
{
    OleDbDataAdapter dbAdapter =
        new OleDbDataAdapter
            ("SELECT * FROM [foo$]", dbConnection);
    dbAdapter.Fill (fooData);
}
finally
{
    dbConnection.Close ();
}


If you want to process the data row by row rather than snarfing it into a DataTable, you can do it this way:

OleDbConnection dbConnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BAR.XLS;Extended Properties=""Excel 8.0;HDR=Yes;""");
dbConnection.Open ();
try
{
    OleDbCommand dbCommand = new OleDbCommand ("SELECT * FROM [foo$]", dbConnection);
    OleDbDataReader dbReader = dbCommand.ExecuteReader ();

    // Say we are interested only in the columns "YearOfBirth" and "Country":
    int yearOfBirthIndex = dbReader.GetOrdinal ("YearOfBirth");
    int countryIndex = dbReader.GetOrdinal ("Country");

    while (dbReader.Read ())
    {
	string yearOfBirth = dbReader.GetValue (yearOfBirthIndex).ToString ();
	string country = dbReader.GetValue (countryIndex).ToString ();

	// ...
    }
}
finally
{
    dbConnection.Close ();
}

But what if you don’t know the name of the sheet you want to read? As you can see from the examples, the ADO.NET interface requires you to name the worksheet – but in many cases you just want to read the first worksheet regardless of its name. It would be cool if ADO.NET provided a suitable notation like this:

// I WISH THIS WORKED
OleDbCommand dbCommand = new OleDbCommand ("SELECT * FROM [0#]", dbConnection);

… but it doesn’t. You must tell ADO.NET the specific name of the sheet you want to read. The solution is therefore to read the spreadsheet schema to find out the sheet names, thus reducing the second problem to the first one, like this:

OleDbConnection dbConnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BAR.XLS;Extended Properties=""Excel 8.0;HDR=Yes;""");
dbConnection.Open ();
try
{
    // Get the name of the first worksheet:
    DataTable dbSchema = dbConnection.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null);
    if (dbSchema == null || dbSchema.Rows.Count < 1)
    {
        throw new Exception ("Error: Could not determine the name of the first worksheet.");
    }
    string firstSheetName = dbSchema.Rows [0] ["TABLE_NAME"].ToString ();

    // Now we have the table name; proceed as before:
    OleDbCommand dbCommand = new OleDbCommand ("SELECT * FROM [" + firstSheetName + "]", dbConnection);
    OleDbDataReader dbReader = dbCommand.ExecuteReader ();

    // And so on...
}
finally
{
    dbConnection.Close ();
}

The main quirk about the ADO.NET interface is how datatypes are handled. (You'll notice I've been carefully avoiding the question of which datatypes are returned when reading the spreadsheet.) Are you ready for this? ADO.NET scans the first 8 rows of data, and based on that guesses the datatype for each column. Then it attempts to coerce all data from that column to that datatype, returning NULL whenever the coercion fails!

I suppose that makes a twisted kind of sense if you have spent a lot of time working with relational databases, but there are some unfortunate consequences of this design which aren't obvious at first. For example, say your spreadsheet contains the following columns:

YearOfBirth    Country	PostalCode
1964	       USA	10005
1970	       USA	10001
1952	       Canada	K2P1R6
1981	       Canada	L3R3R2
1974	       USA	10013

ADO.NET will correctly guess that the YearOfBirth column is numeric, and that the Country column is of type string. But what about the PostalCode column, which contains a mix of numbers and strings? In this case ADO.NET chooses the type based on the majority of the values (with a tie going to numeric). In this example 3 of the 5 postal codes are numeric, so ADO.NET will declare the column to be numeric. Therefore it will attempt to cast each cell to a number, which will fail for the Canadian postal codes - which will therefore come out as NULL values. Ha ha. Isn't that fun?

Even more entertaining, there is absolutely no way to make this 100% reliable - although with some pain, you can improve the situation. Here's what you need to do. First add the "IMEX=1" option to your connection string like this:

OleDbConnection dbConnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BAR.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;""");

That tells ADO.NET to honor the following registry key when reading the spreadsheet:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

This registry key tells ADO.NET what to do when it encounters mixed types in the first 8 rows. It can either be set to the string "Majority Type" (for the default behavior) or to "Text" (which forces the column to be of type string). Note that you are still screwed if the first 8 postal codes are numeric and the 9th is Canadian. (Not to mention that the "Text" option invokes handling that fails on strings over 255 characters, but let's skip that for now.)

There's also a second relevant registry setting (which is honored regardless of the IMEX option):

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

That says how many rows to scan to guess the datatype. The default is 8, but you can set it anywhere from 0-16 decimal (0 meaning "scan the first 16384 rows", and all other values meaning what they say). Putting this all together, the most reliable way to read a US/Canadian postal code is to use the following registry settings:

TypeGuessRows = 0
ImportMixedTypes = Text

That's pretty close to perfect, although it will still fail if the first 16384 postal codes are numeric and any of the subsequent ones aren't.

This is a Bad Design for so many reasons I don't know where to start. First, the behavior of the spreadsheet importer should not depend on global registry settings - that is just inviting mysterious, data-dependent errors whenever other applications or users change those settings. All of those settings should be in the connect string and nowhere else. Second, there should be an option to say "I'm not sure what data is coming, but I want all of it - please coerce everything to something universal like an arbitrary-length string". Third, the interface should be stream-based, not file-based. If you are reading the spreadsheet data from the network, you shouldn't have to save it to a temporary file in order to parse it. Fourth, you shouldn't have to read the spreadsheet schema if you just want to select the worksheet by index (e.g. you want to read the first worksheet, whatever it happens to be called).

For an example of a nice interface for reading and writing Excel spreadsheets, check out Jakarta POI (an open source Java library).

88 Responses to “Tips for reading Excel spreadsheets using ADO.NET”

  1. Richard Hartland Says:

    This is brilliant. Thanks for all your work. regedit here I come.
    Have you come across the schema bringing back table names ending in underscores. Some sheets are fine and others have Sheet1$ and Sheet1_$, the latter not being readable. I could just ignore sheets ending in underscore but would prefer a better answer.

  2. Bill Flaherty Says:

    I noticed that there is also a quirk when I am trying to read a hyperlink where the link text is returned rather than returning the URL. For example, if I have a spreadsheet that has a cell with this in it:
    =HYPERLINK(“http://www.geocities.com/Hollywood/4262/”,”Flash Gordon”)
    I would want to perform a select statement to retrieve the URL. But rather than retrieving the URL, I get “Flash Gordon” since this is the display value of the column. Do you know of a way around this?

  3. Lautz of .NET - Not so Random, Pseudo-Daily Links of Interest #1 Says:

    [...] [...]

  4. Chris Burrows .NET Blog : Tips for reading Data From Excelsheet using ADO.NET Says:

    [...] I have certainly used ADO.NET to connect to Excel in the past.  When I have used it, I must have been lucky for I never new that ADO.NET scanned the first 8 rows of data to determine the data type.   However, now that I have heard about it I watch out for it.   When I need a solution I will definately look here.   Published Wednesday, March 15, 2006 3:22 PM by cjburrows Filed Under: .NET [...]

  5. Mike Butch Says:

    Excelent article. Explained and fixed my problem…sort of. Instead of converting long numbers to text, it is changing them to scientific notation and then to text. Is there a way around this?

    Example:
    9010100 becomes 9.0101e+006

    Thanks,

    Mike

  6. Jiho Han Says:

    I believe the scan 8 rows thing is a OleDB Provider (JET in this case) thing, not an ADO.NET thing per se. This was always an issue with classic ADO as well. I thought I’d point that out just in case.

  7. Imran Arsahd Says:

    This is excellent article. Very help full to me and solve my problem but one thing need to be address how we can update Registry Value data using .Net?

  8. Sri Says:

    Very useful article. Thanks.

    I have the same issue as Mike. 2012101 becomes 2.01214e+006. do you have anyway around this?

    Thanks
    Sri

  9. Alistair Says:

    Great article. Thanks. I must admit I never realised the Excel oledb provider behaved in such a way. Explains why I was only getting some records and not others. Although your conn string is clear enought with quotes, just a quick note that may help others; If you get this issue
    “Could not find installable ISAM.” when you add IMEX=1 to the extended properties string. Ensure it’s in quotes; i.e.
    Extended Properties=Excel 8.0; works as a conn string but when you add
    Extended Properties=Excel 8.0;IMEX=1; , it fails with the above error.
    Extended Properties=’Excel 8.0;IMEX=1′; resolves this.

    Thanks again for explaining this one.

  10. Balaji Says:

    Good one.
    Solved my problem…

  11. Cleber Says:

    Nice article, but Ive a question.
    Do I need any MS Excel files installed to implement this method?

  12. Wiebe Says:

    Excellent… Thanks a lot…

  13. Sujo John Says:

    Hi,
    This article solved my prblm of reading alphanumeric character field from excel sheet into vb.net . Thankx….

  14. Shilpa Says:

    Thanx a lot for the support. Was facing this datatype upload problem and not was not finding a solution for it past many a days.

    The IMEX and Registry setting has made my life easier to resolve the issue.

  15. Nunes Séb Says:

    Hi, Great Article, but I’m a bit perplex, for me the first “TABLE_NAME” is the first alphabetically sorted sheet name not the first sheet appearing in my Excel workbook when I open the file in excel, any idea why ?

  16. Paul Zoulin Says:

    To fix the issue of numbers being converted to scientific notation as reported by Mike Butch and Sri, try using CAST.

    Cast([TheProblemField] as int) As Expr1

    You should then get actual int numbers returned.

  17. Mahesh Says:

    Great , i have been working on Excel/CSV imports and this came to light recently

  18. Fishcake Says:

    Excellently written article, nice one.

  19. eznfree Says:

    have the data type problem and this is the clearest explanation i’ve found so far. unfortunately i can’t change the servers registry.
    found an excel macro that will force columns to text first.
    … but the real solution is to open the worksheets with VB.NET and process the rows without ADO.NET.
    anyone have this ???

  20. Jay Says:

    I shudder everytime I have to import data from Excel to SQL Server and use within a website. Rather than using DTS I decidced to import directly using ADO.NET. Everything worked ok so I naively thought I had solved my problem. Now, a few days before my golive date my import function was bombing on data that seemed to be ok. After seeting the registry values as discussed in this article everything worked ok. I have my fingers crossed.

    Thanks – I need to research this more.

    jay

  21. Sen Says:

    Wonderful research. I too found the problem (returns null from 9th row), but did not investigate to this extend to find the root cause. Great !!

  22. Vishi Says:

    hi, while importing data from Excel to Sql table, if the length of text in single cell of excel exceeds 255 character it is giving me as error, What might be the solution?

  23. Joe Morrison Says:

    Hello Vishi,
    Sadly, this is related to the part where I say “Not to mention that the Text option invokes handling that fails on strings over 255 characters”. I believe your only recourse is to use the “Majority Type” option, although you may still get some benefit by increasing the number of rows scanned by changing the “TypeGuessRows” options. Good luck!

  24. Jono Says:

    Hi,

    I’ve been having an issue with this exact problem the whole day. Every website I’ve been to, told me there is a problem. This is the first website that answered a solution and it works “perfectly”! Really Impressed.

    Thanks Joe.

  25. Grant Says:

    Excellent article. Solved my problem.

  26. Milan Says:

    Hi I m getting error while opening connection
    “External table is not in the expected format.”

  27. Danny Says:

    Hi, in regards to what Nunes mentioned earlier about getting the name of the first worksheet (“TABLE_NAME”) is the first alphabetically sorted sheet name, not the first sheet in the Excel workbook, was there a solution for this? I seem to be having the same problem also.

  28. Abraham Says:

    I have the same problem as Danny’s. Is there any way to avoid the sorting of the Sheet Name.

  29. Swan Says:

    Hi,

    Excellent article! I did exactly the same; I used OleDbDataAdapter to read an Excel spreadsheet into a ADO.Net DataTable. My spreadsheet is a very typital one. It contains mostly numbers, which are converted to decimal. No a problem.

    However, my business rule requires that I treat differently between a blank cell (i.e. empty cell) and a bad entry cell (i.e. a cell that appears as “#VALUES!” due to an error within the formula.) The first case is OK; blank cells are treated as zeros. In the second case, I need to throw an exception.

    I have a hard time trying to distinguish between the two cases. All I got is a System.DBNull in both cases. Aby idea how to solve this? THANKS!

  30. farhang Says:

    Hi
    what if we want to import from excel file with
    two rows merged? I want both rows to be imported
    to datatable so I dont miss the position of cells.
    Thanks

  31. le9569 Says:

    Excellent article. Thanks

  32. Rakendu Says:

    Excellent article!! Explained in simple words!!
    Was very helpfull.. Thanks!! :-)

  33. AarPar Says:

    Any One got Answer for this ?????
    —-
    I noticed that there is also a quirk when I am trying to read a hyperlink where the link text is returned rather than returning the URL. For example, if I have a spreadsheet that has a cell with this in it:
    =HYPERLINK(”http://www.geocities.com/Hollywood/4262/”,”Flash Gordon”)
    I would want to perform a select statement to retrieve the URL. But rather than retrieving the URL, I get “Flash Gordon” since this is the display value of the column. Do you know of a way around this?

  34. Jerry Says:

    Very good article. Thanks.

  35. Mary Says:

    Sir Joe,
    Thank you for this excellent article. I wish God always helps you. I’m trying to solve this problem for one week. Thank you a lot..

  36. CAM - Blog » Blog Archive » Moving Excel data into .net Says:

    [...] The way that Jet determines data types – in situations where the exact data is unpredictable (dynamic!) – makes me reluctant to use this solution. Also, this style solution may be a poor choice for the end user if you want to import what they see on screen (the file on-screen must be saved before changes can be read, it does not seem like a good user-interface choice to me (and automation of saving the file seems problematic/dangerous)). http://blog.lab49.com/?p=196 http://support.microsoft.com/?scid=kb;en-us;316934&spid=1249&sid=global [...]

  37. Ana Says:

    Thanks a lot for your explanation, it was of great help!

  38. Mariam - CR Says:

    Hi!
    Wonderful article, It helps me a lot…

    It works great, but I have an issue trying to read a single cell in a worksheet…
    —————————————————-

    For example, how could I read the range B1 in a worksheet?

    That’s a piece of my code:

    sqlStr = “select * from [Sheet1$B1:B1]”
    MyCommand = New OleDbDataAdapter(sqlStr, MyConnection)

  39. Muruganantham Says:

    Yes, this information is very useful….

  40. Harini Says:

    Thanks a lot. Excellent explanation. It points right to my problem.
    Though there is no perfect solution for this, atleast I’ll be cautious.

  41. chandan Says:

    its good article and
    my problem is that if we don’t know data is filled upto which column and i want to know this then how we handle this…………..

  42. Alex Says:

    Brilliant! But why oh why oh why are we always reliant on the struggling developers network? Why is MSDN so crap – this info should be on the Microsoft site, not hidden away on this (admittedly excellent) site. Isn’t there some way we can give MS a good kicking – force to start paying a bit of bloody attention? Grr…

  43. Anandkumar Says:

    Thanks for the Information,

    Anand.

  44. Muthu Kumaran Says:

    Hi,
    Thanks for your greatest support.
    Atlast i done with your greatest solution and really superb article.
    Thanks once again.
    Muthu Kumaran.D :-)

  45. M Naveed Says:

    great help. thanx for support

  46. Indu Says:

    Excellent article. I was having a hard time with my excel file. It was more like an invoice form rather than an excel spreadsheet. I was using com automation to pull data but there is no way I am going to get permission to install excel on the server. My data was getting messed up and returning a lot of nulls if I used the oledb driver.
    I included HDR=NO and IMEX=1 in the extended properties and then kept getting the “Could not find installable ISAM.” error. I came back and read the article again, scrolled further down to read other postings and thats where I found it needs to go in quotes.
    Extended Properties=’Excel 8.0;HDR=NO;IMEX=1′
    Thanks.

  47. shakeer Says:

    hi,

    excellent,
    thanks for the great article

  48. Robert M. Says:

    I’m having a problem getting past the GetOleDbSchemaTable() step. The DataTable returned by this call has Rows.Count = 0. Any ideas what’s going on here?

    I’m really surprised I haven’t found a simpler way to simply read all the worksheets into a DataSet object with each being represented as a DataTable.

  49. Venkatesh Says:

    Excellent article..this solved my DTS problem.

  50. Sergey M Says:

    Great article, and eye opener. I’d like to mention another issue I just came across. The document I’m importing has hiddent rows ( someone uses that instead of deleting ). ADO.NET imports all rows, though, both hidden and unhidden.
    Does anybody know how to tell the hidden rows apart?

  51. Sridhar Akula Says:

    Hi.Friend…
    Your blog published mindblowing article on Excel spread sheet,this learns to me how i developed a application to read data without missing with accuracy..this article removed herdele in my application developing.

    Thanks again

    Sridhar Akula

  52. Zion Mizrahi Says:

    Thanks alot!

    Fixed my 255 charecter limit on text fields.
    just great !

  53. Lee Hopkins Says:

    Thanks for the help about the “Extended Properties=’Excel 8.0; HDR=Yes; IMEX=1′;” and the cant find the isam. I am running in to a slight problem is that i have a col that should be a date but using the IMEX it brings the dates back as the excel number not a real dateformat it there a way to “Cast” or convert a col in my sql statement to a text or char
    select family,vendornum,[vendor name], [suntron part], [order number], po_line, [po rel], bal_due,[dock date], [action msg], [desired date], [last weeks desire date], po_reduction, po_rev_qty, comments, cvar([supplier commit date]) as [supplier commit date], [supplier commit qty], [supplier comments], [lead time days],SuppAccKno, [current fixleadtime days] from [sheet1$]“, myconnection

  54. chitra Says:

    hi
    i read the article, it is wonderful and it helped me a lot, but there is a little problem when i tried this code it gives me an error :”External table is not in the expected format” when i open the oledbconnectio, could u tell me where is the problem

  55. cloudie Says:

    Hie,

    how do I do an oledb select of columns with #es and ()s in theier headers? If I can’t, can I select columns based on their column numbers?

    Thanks in advance!

  56. Nurul Khalique Says:

    PERFECT SOLUTION

    even with the following options, you still cant garantee that all data will be picked up:

    TypeGuessRows = 0
    ImportMixedTypes = Text

    However, if you have a dummy row with TEXT values for each column and use these settings:

    TypeGuessRows = 1
    ImportMixedTypes = Text

    You are garanteed to get all data as you are telling it to scan ONLY one row, which you CAN garantee is text, hence it will pick up both text and numbers as text.

    We are just a few days away from go live and this solution with a hard coded first row was the only viable solution.

    Cheers!

    Nurul.

    Nurul.

  57. geoff Says:

    This is a help but I’m running into a problem where it gives me back all the data except for the last row. Any ideas what could be going on with that? Thanks.

  58. Gerhard Says:

    This not working…. Microsoft should rethink this… I think it is really, really stupid.

    Using SSMS (SQL server Management Studio) using these settings
    TypeGuessRows = 1
    ImportMixedTypes = Text
    The import IGNORES numbers if the first row is text and it IGNORES text if the first row is a number.

    Can Microsoft please take it away..this is really one of the most stupid things I have ever come accross.

    Eventually setup a Linked server with IMEX=1 setting

    eXEC sp_addlinkedserver LinkTest,
    ‘Jet 4.0′,
    ‘Microsoft.Jet.OLEDB.4.0′,
    ‘X:\ImportData.xls’,
    NULL,
    ‘Excel 8.0;IMEX=1′

    But now I get this
    zz4082165795
    4.08217e+009
    4.08217e+009

    From this in the first 3 rows:
    zz4082165795
    4082165795
    4082165795

    Is it me, or is Microsoft trying to make is difficult to import mixed colums… Please… how do I solve this.

  59. Chris Says:

    I’m having the same problem as Gerhard. I have tried several ways around the issue but got know where.

    Any one have any ideas?

  60. sasi Says:

    same problem for me
    long integer is reading like this
    4.08217e+009
    4.08217e+009
    please help

  61. Raja Says:

    The excel format is:
    column1 column2 column3
    Raja 1234
    Ramakrish 2345

    when i open the connection, i got the error. “External table is not in the expected format.”
    give me a solution???????????

  62. Raja Says:

    The excel format is:
    column1 column2 column3

            Raja 1234
            Ramakrish 2345

    when i open the connection, i got the error. “External table is not in the expected format.”
    give me a solution???????????

  63. Fehlerhafter Import von Excel-Zellen bei ADO.NET mit Jet OLEDB 4.0 at lemming.name Says:

    [...] Fast totgesucht, bis ich hier fündig wurde. [...]

  64. Avi Says:

    Hi

    I am wondering what will happen when the worksheet has (say) 80 rows and the TypeGuessRows is set to 0.

    Will the JET engine scan all the rows upto 16384 or just upto the last (ie 80th) row of the worksheet.

  65. marc benigni Says:

    Following up on Lee Hopkin’s point – there seems to be an outstanding problem with date fields. Does anyone have any idea how to address this? I can force the imported data type to text (in most cases this was the default anyway) but if I do so all dates come in as null. Is there anyway to cast so as to account for both possibilities in one select?

    This, for instance does *not* work. Looking for an alternative:

    SELECT *, CAST([col name] AS DATE) FROM [table name]

    This is truly awful ADO implementation…

  66. jack555 Says:

    Partly solved my problem. Thanks.
    Not sure if it wasn’t mentioned above, but if someone for some reason will need to query worksheet which name starts with digit, it needs to be written between apostrophes. For example for worksheet 1qrt like ['1qrt$']

  67. TheRoyalFalcon Says:

    This is a nice alternative to OLE for basics. Thank you for breaking this down, as it is quite a help.

    I was wondering, is there anyway I could change a worksheet setting via this? I would like to use the above to turn on/off gridlines for a given worksheet. Do you think that is possible? Thanks

  68. Subho100 Says:

    Answer for uploading huge decimals from excel to database when it becomes ’4.08217e+009′ like values. Convert them to strings…

    same problem for me
    long integer is reading like this
    4.08217e+009
    4.08217e+009
    please help

    string.Format(“{0}”,item[i])

    Thank you Pronojit for this tip.

  69. Sameer Rana Says:

    Excelent article. Thank you Sir!

    One small addition. While reading the sheet names, in case there is . in the sheet name for example .net data, it will replace the . with #, meaning in above example it’ll return #net data rather than .net data. While coding make sure to handel this exception.

  70. Naorem Ranjan Says:

    In scientific notation conversion issue,
    Even if we use string.Format(”{0}”,item[i]) we won’t be able to get the exact data. It is rounded off.

    Excel File column contains: 18520022
    This will be retrived as: 18520000

    If we use in the select query from excel like
    “select Format([ColName], ’0′) from [SheetName]“, the same issue exists.

    If we put ‘ at the beginning of cell then the it is readed the original data. But this is not efficient way of solving the issue.

    Have tried a lot on this issue this is the only way to get rid of the problem.

    Hoping anybody can help me solving the issue.

  71. Maverick Says:

    Hi ,

    First of all Grt article.
    Actually I m reading Excel file with columns till ‘CH’ and 15 rows.
    My problem is due to some reason its not reading the values of only these 3 columns X,Y and Z.
    (Column X,Y,Z have only values for row 12,13)

    If I add value for column X on row 5,6 ( or any row ) I strat to get values of whole column and thats true for Column Y,Z.

    Now this is very weird , I have check the excel sheet and those columns are no different from other columns in any regard , I have few other columns who have values against 2,3 rows and they all are showing proerly.

    Any sugesstions for my problem ?
    Thanks,
    Maverick

  72. Naorem Ranjan Says:

    Hi Maverick,

    Send us the
    connection string used to read the excel worksheet, the data of the X,Y,Z columns and
    Registry Setting of
    “TypeGuessRows” and “ImportMixedTypes”

  73. Maverick Says:

    Thanks Naorem ,

    I got it , I needed to set IMEX=1 ; and now I m getting all values.

    But thats very strange that why I was getting the values column X,Y,Z if I add value for for these columns for other rows ?

    Any how my problem is solved thanks for help , But I think Jet driver should throw some exception , warning or something to warn user for this kind of situation.

  74. Kelvin Says:

    Raja, if you’re still struggling with the error “External table is not in the expected format,” I’ve found that I get that error if the Excel file is in a particularly old version of Excel. In my case, when I chose File/Save As in Excel after opening up the file, Excel said it was ready to save it as type “Microsoft Excel 2.1 worksheet”. When I saved it in the modern type of “Microsoft Excel workbook” or “Microsoft Excel 97-2002 and 5.0/95 workbook”, the import worked correctly.

    The other option would be to change the “Extended Properties=”"Excel 8.0″”" code in the connection string to an earlier version. I think I’ve seen elsewhere that you’d want to use Excel 5.0 for the older worksheet formats, but I’m not sure; you’d have to test that out.

  75. Leo Says:

    Hey Joe, cool article! It’s very rare to come across something this useful yet compact. My hat off, man.

  76. Rodrigo Says:

    Hi Guys,
    I’m now using it on my to read some large files, and I want to use range , like [sheet1$11:100]. It works fine, but there is a particular case that it doesnt works. Its when I have a sheet with 31 characters, in my case : CLEAVEDB62305offer_0529163742Ge .

    When i try to do a “select * from [CLEAVEDB62305offer_0529163742Ge$10:100]” , it shows me an error message.
    Obs.: If I try : “select * from [CLEAVEDB62305offer_0529163742G$10:100]” it works fine
    Anyone have any idea to solve it ?

  77. Wectan Says:

    Hi, regards to Nunes and Danny mentioned earlier, getting the name of the first worksheet (“TABLE_NAME”) is the first alphabetically sorted sheet name, not the first sheet in the Excel workbook.

    I found a solution for this problem.
    Microsoft DAO 3.5 Library is needed

  78. SpinDonkey Says:

    Fantastic! I was scratching my head trying to work out what on earth was going on with missing number values in results selected from a largely text dominated field. You are a gentleman and a scholar :)

  79. satish Says:

    Tin 28999999999
    Form Type c-form
    Form No 1000
    vehicleno Transportername InvoiceNo
    1234 asdf 4567
    1235 zxcv 4568
    1236 xcvb 4569

    This was the data formatin the excel file. the actual data was from 4th row. how to read the data from nth row and display in grid

  80. niks Says:

    very nice article and its help me lot.
    I have .csv file instead of excel sheet with different value means string and numeric value in one column.
    I have SSN field which have string and numeric value in .csv file.when i execute my code its gives me data but the cell which have string value in ssn column and all other value in numeric data then only that string value is not retrieve its return nothing in tht cell value when i get data from ado.net

  81. Sid Says:

    There is a way to force the datatypes 100% of the time, just do the following:

    string sql = “SELECT * FROM [sheet1$]“;
    cmd = new OleDbCommand(sql, connection);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    DataSet ds = new DataSet();
    ds.Tables.Add(“xlsImport”);
    DataTable dt = ds.Tables[0];
    dt.Columns.Add(“field0″, typeof(string));
    dt.Columns.Add(“field1″, typeof(string));
    dt.Columns.Add(“field2″, typeof(string));

    da.Fill(ds, “xlsImport”);

  82. Ronald Says:

    Hai, I’ve read all the comments but no one mentioned this 1 problem:

    I gave Cell A1:C10 with a name ‘Chargeable’ in Sheet1, how can we get the data from it?
    I’ve tried using ‘Select * From [Sheet1$Chargeable]‘ but it didn’t work.

  83. Mack Says:

    Hello all. Thank you all for your help in advance! I have followed the examples and it seems to be working correctly with one exception…I get no data what so ever from my excel sheets. I seem to get the right number of rows and the column headers are there with the correct names but every cell is blank…any clues? I am using VB.net 2005 and reading from Excel 2003.

    Thanks all!

  84. Mack Says:

    Sorry have to add a correction to my first post. The column header names are NOT there, but as stated before the dataset has the correct number of rows..they just don’t show any data in them.

    Thanks again.

  85. alberto coeltti Says:

    Hi,
    I cannot find a way to read a cell range in a sheet containing merged cells, even if those are placed outside the range I’m actually reading.
    Anyone know how to handle this?
    Tanks

  86. Nal Pec Says:

    Is there a way to read values as text only. All of them!? I’m happy to convert myself in code. I am reading values that start with zero’s, and I want to keep the zero’s on. It removes them and returns as double value. I need text only… any way to do this?

    Thanks

  87. Jeff Says:

    Important note here–using IMEX=1 makes the spreadsheet read-only.

  88. Kyle Says:

    i have the same problem as Nal Pec. Has anyone ever found the solution to importing Zip Codes in the US that start with zeros? Using CAST() in your SELECT statement doesn’t work, and IMEX=1 doesn’t seem to either.