Tips for reading Excel spreadsheets using ADO.NET
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).
January 24th, 2006 at 5:26 pm
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.
February 21st, 2006 at 5:50 pm
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?
March 14th, 2006 at 7:57 am
[...] [...]
March 16th, 2006 at 5:20 am
[...] 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 [...]
March 24th, 2006 at 5:08 pm
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
March 28th, 2006 at 11:40 am
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.
April 3rd, 2006 at 2:40 pm
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?
April 3rd, 2006 at 5:52 pm
Very useful article. Thanks.
I have the same issue as Mike. 2012101 becomes 2.01214e+006. do you have anyway around this?
Thanks
Sri
April 4th, 2006 at 11:54 am
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.
April 5th, 2006 at 4:53 am
Good one.
Solved my problem…
April 10th, 2006 at 3:14 pm
Nice article, but Ive a question.
Do I need any MS Excel files installed to implement this method?
May 17th, 2006 at 9:20 am
Excellent… Thanks a lot…
May 25th, 2006 at 1:23 am
Hi,
This article solved my prblm of reading alphanumeric character field from excel sheet into vb.net . Thankx….
June 1st, 2006 at 3:20 am
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.
June 9th, 2006 at 8:19 am
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 ?
June 9th, 2006 at 12:46 pm
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.
June 14th, 2006 at 7:24 pm
Great , i have been working on Excel/CSV imports and this came to light recently
June 15th, 2006 at 7:51 am
Excellently written article, nice one.
June 21st, 2006 at 2:27 pm
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 ???
June 21st, 2006 at 4:17 pm
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
June 27th, 2006 at 9:32 am
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 !!
June 29th, 2006 at 7:30 am
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?
June 29th, 2006 at 8:58 am
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!
June 29th, 2006 at 3:25 pm
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.
July 5th, 2006 at 3:11 pm
Excellent article. Solved my problem.
July 11th, 2006 at 11:28 am
Hi I m getting error while opening connection
“External table is not in the expected format.”
July 19th, 2006 at 3:53 am
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.
July 20th, 2006 at 4:09 am
I have the same problem as Danny’s. Is there any way to avoid the sorting of the Sheet Name.
July 27th, 2006 at 3:51 pm
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!
August 5th, 2006 at 2:54 am
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
August 9th, 2006 at 6:12 pm
Excellent article. Thanks
August 10th, 2006 at 2:48 am
Excellent article!! Explained in simple words!!
Was very helpfull.. Thanks!!
August 11th, 2006 at 7:08 am
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?
—
August 21st, 2006 at 1:29 am
Very good article. Thanks.
August 22nd, 2006 at 2:55 am
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..
August 23rd, 2006 at 3:54 am
[...] 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 [...]
August 30th, 2006 at 4:38 pm
Thanks a lot for your explanation, it was of great help!
September 15th, 2006 at 1:00 pm
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)
October 5th, 2006 at 12:55 am
Yes, this information is very useful….
October 19th, 2006 at 4:40 pm
Thanks a lot. Excellent explanation. It points right to my problem.
Though there is no perfect solution for this, atleast I’ll be cautious.
October 30th, 2006 at 5:27 am
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…………..
November 1st, 2006 at 6:18 pm
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…
November 6th, 2006 at 4:40 pm
Thanks for the Information,
Anand.
November 10th, 2006 at 8:25 am
Hi,
Thanks for your greatest support.
Atlast i done with your greatest solution and really superb article.
Thanks once again.
Muthu Kumaran.D
November 17th, 2006 at 4:47 am
great help. thanx for support
November 30th, 2006 at 6:55 pm
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.
January 25th, 2007 at 2:02 am
hi,
excellent,
thanks for the great article
February 2nd, 2007 at 7:33 pm
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.
February 6th, 2007 at 12:01 pm
Excellent article..this solved my DTS problem.
March 2nd, 2007 at 11:02 am
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?
March 9th, 2007 at 9:06 am
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
March 9th, 2007 at 8:40 pm
Thanks alot!
Fixed my 255 charecter limit on text fields.
just great !
March 19th, 2007 at 9:20 am
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
April 7th, 2007 at 5:22 am
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
April 25th, 2007 at 1:40 am
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!
April 28th, 2007 at 6:06 am
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.
May 8th, 2007 at 11:48 am
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.
May 21st, 2007 at 8:29 am
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.
May 23rd, 2007 at 10:41 am
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?
May 24th, 2007 at 2:19 am
same problem for me
long integer is reading like this
4.08217e+009
4.08217e+009
please help
June 7th, 2007 at 9:15 am
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???????????
June 7th, 2007 at 9:22 am
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???????????
June 12th, 2007 at 2:55 am
[...] Fast totgesucht, bis ich hier fündig wurde. [...]
June 19th, 2007 at 9:03 pm
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.
July 5th, 2007 at 11:15 am
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…
August 10th, 2007 at 7:11 am
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$']
August 29th, 2007 at 1:23 pm
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
September 18th, 2007 at 12:23 am
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.
September 26th, 2007 at 1:07 pm
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.
October 16th, 2007 at 5:09 am
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.
October 16th, 2007 at 12:45 pm
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
October 17th, 2007 at 12:55 am
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”
October 17th, 2007 at 6:12 am
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.
October 19th, 2007 at 11:20 am
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.
October 23rd, 2007 at 10:49 am
Hey Joe, cool article! It’s very rare to come across something this useful yet compact. My hat off, man.
October 24th, 2007 at 8:37 am
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 ?
October 24th, 2007 at 10:15 pm
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
December 14th, 2007 at 6:08 am
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
December 26th, 2007 at 4:02 am
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
December 27th, 2007 at 5:10 am
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
January 4th, 2008 at 9:28 am
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”);
January 16th, 2008 at 1:02 am
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.
January 24th, 2008 at 1:05 pm
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!
January 24th, 2008 at 1:10 pm
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.
February 14th, 2008 at 12:38 pm
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
March 5th, 2008 at 3:01 pm
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
March 7th, 2008 at 11:38 am
Important note here–using IMEX=1 makes the spreadsheet read-only.
March 19th, 2008 at 10:49 pm
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.