topic Re: libname oledb to import Excel files in SAS Programming

libname oledb to import Excel files

Oleg_L — Thu, 21 Feb 2013 12:44:58 GMT

Hello SAS-Users.

I use the code below to access lots of Excel files that we receive from our subsidiaries.

libname myxls oledb access=readonly provider="Microsoft.ACE.OLEDB.12.0"

properties=("data source"="&fullname" "Mode"="Read")

provider_string="Excel 12.0;ReadOnly=True;HDR=no;IMEX=1;"

;

Excel files are in .xlsb format.

This code works fine.

But for some files i get the error:

ERROR: Error trying to establish connection: Unable to Initialize: External table is not in the expected format.

ERROR: Error in the LIBNAME statement.

These problem Excel files are opening in Excel without any errors.

I tried to open file in Excel and save it as another copy. But it does not help.

The only way i found :

1. Open file in Excel

2. Copy and paste only values (paste special) to another file.

3. Save this new file and then import.

I hope someone knows how to solve this problem. May be i missed some option in the libname statement.

Files are hundreds and it is very difficult manually copy/paste data.

Thanks in advance.

Oleg.

Re: libname oledb to import Excel files

Oleg_L — Fri, 22 Feb 2013 07:00:18 GMT

I find out that error files have protected structure enabled with a password.

I googled some information about this protection. Programmers (non-SAS) say that it is not possible to make an OLEDB access to protected Excel files.

I can not use DDE unfortunatly. I am running EG.

Oleg.

Re: libname oledb to import Excel files

bentleyj1 — Fri, 22 Feb 2013 14:51:59 GMT

I'm not familiar with using the OLDB engine but I use the Excel engine quite a bit. I don't work with password-protected spreadsheets and I don't work with .xlsb files but I _think_ it has an option that allows you to pass a password to Excel.

Check the documentation on the Excel libname engine. You'll likely have to rework your code to use the Excel engine but it might be a solution.

Thanks for the tip about password-protected files throwing this error.

Good Luck.