External table is not in the expected format

Question

0.00/5 (No votes)

See more:

I am trying to import excel having .xlsx format.

but it gives error "

external table is not in the expected format

"

in .xls format this error is not coming, but I have to solve this problem in .xlsx format.

what is solution for that I dont know. Please Help me..

I have try 2 diff connection string for that one by one, to check solution, but non of this 2 work

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtSource.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtSource.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1;\"

What I have tried:

I have used this connection string

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtSource.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1;\"

Posted 22-Dec-20 22:05pm

Member 9720862

Updated 22-Dec-20 22:34pm

Add a Solution

Comments

Richard MacCutchan 23-Dec-20 4:18am

The ACE driver should be able to read .xlsx files. My connection string has only "Excel 12.0", without the "Xml".

"Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + txtSource.Text + ";Extended Properties=\"Excel 12.0;HDR=YES;\""

Member 9720862 23-Dec-20 4:32am

I have use connection string given by you...
Now this error is coming "Format of the initialization string does not conform to specification starting at index 83."

Richard MacCutchan 23-Dec-20 5:39am

You need to show the exact string that you used. Did you remember to use this to create a string that includes a filename?

Richard MacCutchan 23-Dec-20 6:20am

My mistake I missed the escaped double quote at the end. See my changed version.

1 solution

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)

Maciej Los · Answer 1 · 2020-12-22T22:34:00

Solution 1

Try this:

C#

string connString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={ txtSource.Text };Extended Properties=\"Excel 12.0;HDR=Yes;\";";

using(OleDbConnection connection = new OleDbConnection(connString))
{
    connection.Open();
    using(OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$];", connection))
    {
        //other instructions here
    }
}

I do not recommend to use IMEX=1, because it causes driver to treat data as text.

For further details about connection details, please see: Excel connection strings - ConnectionStrings.com[^]

Posted 22-Dec-20 22:34pm

Maciej Los

Comments

Member 9720862 23-Dec-20 4:36am

I have use connection string given by you...
Now this error is coming "Format of the initialization string does not conform to specification starting at index 83."

Maciej Los 23-Dec-20 4:51am

What value is in txtSource.Text?

Member 9720862 23-Dec-20 4:53am

txtSource.Text = C:\Users\hp\Desktop\desp report.XLSX

Maciej Los 23-Dec-20 5:13am

Try to remove "HDR=Yes" and let me know if it helped.

Member 9720862 23-Dec-20 5:19am

by removing "HDR=Yes", Same erorr coming..

Maciej Los 23-Dec-20 5:24am

Ok. Another try:

string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;';",  txtSource.Text);

Member 9720862 23-Dec-20 5:45am

I think, this is perticular for this Excel sheet Problem. because I copy data from this to another excel having same extansion. and import it. then it works...