question

vaishu-9143 avatar image
0 Votes"
vaishu-9143 asked Monalv-msft edited

Can we import excel file without excel providers ?

Hi All,

Is there any way to import excel without providers. please share your thoughts.

Thanks

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

@vaishu-9143,

You would need one of the Microsoft ACE OLEDB Providers: 12.0, 15.0, or 16.0

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the reply..

Without Microsoft ACE OLEDB Providers: 12.0, 15.0, or 16.0 we can't import right

0 Votes 0 ·

Correct. They are made exactly for it.

1 Vote 1 ·
JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

You could save the Excel file as a CSV file - and import the CSV file without having the Excel providers installed. Generally much easier to do that than to get the drivers installed (both 32-bit and 64-bit).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @vaishu-9143 ,

We should import excel files with providers in ssis package.

Get the files you need to connect to Excel

1.Before you can import data from Excel or export data to Excel with SSIS, you may have to download the connectivity components for Excel if they're not already installed. The connectivity components for Excel are not installed by default.

2.Download the latest version of the connectivity components for Excel here: Microsoft Access Database Engine 2016 Redistributable. The latest version of the components can open files created by earlier versions of Excel.

Please refer to Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS) and Excel connection strings.

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @vaishu-9143 ,

May I know if you have anything to update?

Best Regards,
Mona

0 Votes 0 ·
JamieMCSE avatar image
0 Votes"
JamieMCSE answered

It may matter if you try to help someone understand why you are adding the ACE OLEDB if the understanding is put forth regarding the Office vulnerabilities resulting from installing the entire Office or even Excel on a server. The Excel requires regular monthly updates and exposes a good deal of surface area. Using the ACE OLEDB provides you with the minimal possible exposed surface area and the updates are few and far between.
There is another caveat here - the ACE OLEDB 12 is acceptable for Excel 2007 and 2010. In this circumstance the providers work either for the 32 bit version or if you are using a 64 bit version of the version, then use the 64 bit provider. There is also a capability to register both the 32 bit and 64 bit (register 32 bit first, then 64 bit with the /passive command line)
If you are working with Excel 2013, then the ACE OLEDB that matches is the version 15.
More likely, your team has Office 2016 or 2019 or Excel 2016 or 2019 and for this instance, you'll want the ACE OLEDB 16.
Try the 32 bit version first - the 64 bit version and 32 bit version, unlike ACE OLEDB 12, cannot function side by side. So the better approach here is to install the 32 bit first, run some tests, and if it proves to be the wrong version, uninstall and take a turn with the installation of the 64 bit version.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.