External table is not in the expected format

Question

0.00/5 (No votes)

See more:

I have one xls file which has 198000 rows. Whenever I try to import it in sql database table then I am getting following error to open OLE db connection.

External table is not in the expected format.

My code:

 string con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("TowerWorkOrders20110316.xls") + ";Extended Properties=\"Excel 12.0\"";
        //string con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("TowerWorkOrders20110317.xls") + ";Extended Properties=Excel 8.0";
        OleDbConnection connection = new OleDbConnection(con);
        OleDbDataReader reader;
        connection.Open();
        OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
        reader = command.ExecuteReader();
.........
........

can anyone give me the solution?

Regard
Imrankhan

Posted 11-Apr-11 20:28pm

Imran Khan Pathan

Updated 11-Apr-11 22:51pm

Manfred Rudolf Bihy

v2

Add a Solution

Comments

Manfred Rudolf Bihy 12-Apr-11 4:53am

Edit: Removed the lacs (= 100000) reference and wrote out the number so that everybody understands what OP is talking about.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Answer 1 · 2011-04-11T21:06:00

Solution 1

Here you have a nice article about reading and writing excel using oledb.

Reading and Writing Excel using OLEDB[^]

You can use it to check out what you are actually selecting and check it with the table you want to write it to. Also be sure to check the data types and whether maybe a row with headers is the problem.

Good luck!

Posted 11-Apr-11 21:06pm

E.F. Nijboer

Comments

Imran Khan Pathan 12-Apr-11 4:33am

I find out the problem. My xls file is in TAB delimeter format and because of this I am getting external table format error.

Is there anyway to read tab delimeter xls file?

I convert it into .csv format and when I read .csv file then it returns only first column records. I have total 33 columns in file.

Manfred Rudolf Bihy 12-Apr-11 4:57am

See my answer! It has a link to a great article here on CP that shows you how you can achieve your goal.

E.F. Nijboer 12-Apr-11 5:01am

But then it isn't an actual excel file, simply a tab delimited file. You can have a look at the oledb for text files. This article can help you out. Another way is to simply read and process the file yourself. That should be that hard as well.
http://www.codeproject.com/KB/cs/UsingJetForImport.aspx

Manfred Rudolf Bihy · Answer 2 · 2011-04-11T22:55:00

Solution 3

See this great article here on CP to find out how to achieve this for text files (csv): Using OleDb to Import Text Files (tab, CSV, custom)[^].

Happy coding!

-MRB

Posted 11-Apr-11 22:55pm

Manfred Rudolf Bihy