How to read excel data(.xls) using SSIS

Question

0.00/5 (No votes)

See more:

Hi, I am developing a SSIS package in which i will have reading data from excel file(.xls) as my first component. I am well aware that this is just a simple one two steps but i am getting errors "Microsoft Jet Database Engine", "External table is not in the expected format". I have tried various ways to so far to no avail. Below is my excel connection manager setup:

Provider=Microsoft.JET.OLEDB.4.0;Data Source=somePathHere\data.xls;Extended Properties="EXCEL 8.0;HDR=NO";

Whenever i tried to preview my records, the error will prompt out. I have MS Excel 2010 installed in my PC.

What I have tried:

1. Change excel connection manager into

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=somePathHere\data.xls;Extended Properties="EXCEL 12.0;HDR=NO";

but error is still the same except for JET has become Access.

2. Add single quote in "Extended Properties" as suggested online but to no avail.
3. Reinstall Access 2010 Distributional package but problem still persist.
4. Overwrite the .xls into .xlsx successfully solving the problem but due to some limitation i am not allowed to do that.

Posted 14-May-18 22:57pm

Jamie888

Add a Solution

Comments

Richard MacCutchan 15-May-18 4:58am

What table is the message referring to, and where does the error occur?

Jamie888 15-May-18 5:02am

Sir, it did not say which table but i believe it is referring to excel sheet. The error will prompt every time i click Preview button in the Excel Source component in SSIS.

Richard MacCutchan 15-May-18 5:13am

It sounds like the Excel file is not actually .xls. If you say that converting it to .xlsx fixed the problem than that must be the answer.

Jamie888 15-May-18 5:18am

Really appreciate your input sir but i doubt it because i have double check the data.xls(right-click properties) and indeed it is in .xls format.

Jamie888 15-May-18 5:23am

Sir, I have done a further testing by creating another .xls file and indeed it can be read. I doubt whether is it due to the original .xls file is in protected mode?

Richard MacCutchan 15-May-18 5:28am

Impossible to guess what the problem may be. However, when you say "overwrite the .xls into .xlsx", what exactly did you do?

Jamie888 15-May-18 5:31am

I have created a new .xlsx file with all the records in it.

Richard MacCutchan 15-May-18 5:33am

What happens if you try saving that in .xls format?

Jamie888 15-May-18 5:56am

Yes, i am able to read as well. I did another testing. This time i copy the exact data and set it into protected mode and save it as .xls but i am able to read also. Only i am not able to read/establish connection to the ori file.

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)