By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,526 Members | 1,888 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,526 IT Pros & Developers. It's quick & easy.

Importing Data from Excel sheet to Dataset

P: 37
I am working on an application Where I want to import data from Excelsheet to Dataset. But While doing that i am facing following problems:
1) When I close excelsheet and try to import data from excel it gives me following error.
Code is as follows:

Expand|Select|Wrap|Line Numbers
  1. public DataTable originalData1(String _filename, String _sheet)
  2.         {
  3.             OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ _filename + ";Extended Properties=Excel 8.0");
  4.             OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + _sheet + "]", con);
  5.             DataTable dt = new DataTable();
  6.             da.Fill(dt);
  7.             return dt;
  8.         }
Error :"External table is not in the expected format"


but when I keep open excelsheet it imports data.

So I m not sure what to do. As user obiviously close the excelsheet.
Aug 12 '08 #1
Share this Question
Share on Google+
6 Replies


DrBunchman
Expert 100+
P: 979
Hi sachinkale123,

A couple of things: please could you wrap your code in CODE tags using the # button in the edit window - it makes your posts much easier to read. Also, please do not double post your questions - if you need to make changes to a post you can edit it up to an hour after posting. See the Posting Guidelines for more info.

With regards to your problem: how was your excel spreadsheet created? Was it created in MS Excel or programatically? If it was the latter then try changing the Extended Properties from Excel 8.0 to HTML Import.

Hope this helps,

Dr B
Aug 12 '08 #2

P: 37
Hi,
I am not creating excel sheet.
1) I will be provided excel sheet to user. User will provide the excel sheet path. 2) right now I am using Office Excel 2007.

Hi sachinkale123,

A couple of things: please could you wrap your code in CODE tags using the # button in the edit window - it makes your posts much easier to read. Also, please do not double post your questions - if you need to make changes to a post you can edit it up to an hour after posting. See the Posting Guidelines for more info.

With regards to your problem: how was your excel spreadsheet created? Was it created in MS Excel or programatically? If it was the latter then try changing the Extended Properties from Excel 8.0 to HTML Import.

Hope this helps,

Dr B
Aug 12 '08 #3

DrBunchman
Expert 100+
P: 979
I'm sure the problem lies with either the format of your excel file or the connection string. Have you used this connection string to connect to an excel file succesfully before?

You could try using the ACE provider instead of JET. Change your connection string to use
Expand|Select|Wrap|Line Numbers
  1. Provider=Microsoft.ACE.OLEDB.12.0
Does this help?
Dr B
Aug 12 '08 #4

P: 37
It worked.
Thanx
Thanx
Thanx
Thanx
Thanx
I am very happy...thanx once again..
Aug 12 '08 #5

DrBunchman
Expert 100+
P: 979
No problem, glad to help.

As a footnote to this thread, I had a quick check on google and found that the JET provider doesn't work with Excel 2007 which was the cause of your problem. Not an issue I'd come across before but useful to know.

Dr B
Aug 12 '08 #6

Curtis Rutland
Expert 2.5K+
P: 3,256
Ace is also the engine you will need for Access 2007, just in case you ever need to know that.
Aug 12 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.