topic Re: Trouble importing Excel using PC File Server in SAS Data Management

Trouble importing Excel using PC File Server

wgfmoonpie — Thu, 17 Sep 2015 02:48:58 GMT

I've downloaded Excel files containing data for air monitoring stations on a yearly basis. Therefore I'm looking at 20 or more Excel files per year from 1991 to 2015. I've been able to easily import the Excel files from the later years using DBMS=xls. However, when I get to those in 2007 they're save in an early Excel format. Using DBMS=xls I get the indication that the file is Version 4 or early. I then tried using the DBMS=excel and got a Class not found error. Searching the internet I determined that I needed to install the PC FIle Server on my system as I'm running a 32 bit Windows and 64 bit SAS. I downloaded the appropriate version based upon my SAS version and installed. Using the following commands (note this is a portion of my macro which I know works as its what I've used in the later year runs):

proc import datafile=&excelfilename
         out=temp1
         dbms=excelcs replace;
         sheet=&sheetname;
         run;

I end up with the following error messages:

ERROR: CLI describe error: [Microsoft][ODBC Excel Driver] External table is not in the expected format.
ERROR: Import unsuccessful. See SAS Log for details.

I'm at a loss. I know I can open each excel file and convert it to a newer version of excel but that's more than 200 files.

Any help would be most appreciated.

Thanks, WIlliam

Re: Trouble importing Excel using PC File Server

Reeza — Thu, 17 Sep 2015 04:23:28 GMT

PC Files are for later versions not earlier versions.

Try DBMS=Excel4 or Excel5

http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#p0jf3o1i67m044n1j0kz51ifhpvs.htm

Re: Trouble importing Excel using PC File Server

Kurt_Bremser — Thu, 17 Sep 2015 05:47:53 GMT

Hint for the future: do not store raw data in a Microsoft-derived format. Always use something that is easily readable across platforms and software generations and can be monitored with a simple text editor.

I'd use the opportunity and write a VBS that batch-converts all the data to .csv

Re: Trouble importing Excel using PC File Server

wgfmoonpie — Thu, 17 Sep 2015 12:32:18 GMT

Definitely appreciate the responses.

I had tried 'Excel4' and just tried 'Excel5'. Unfortunately both give the following message:

ERROR: An exception has been encountered.
Please contact technical support and provide them with the following traceback information:

Kurt, I am in 100% agreement with you. Unfortunately, this is data I had to download not data I had collected. I therefore had no control of the format used, just trying to find the most effective way to get the data into a usable source.

Thanks again.

Re: Trouble importing Excel using PC File Server

Reeza — Thu, 17 Sep 2015 15:34:48 GMT

Can you post a sample of the file? It may be faster to convert to cvs as Kurt suggested. Try it without sheet option.

Re: Trouble importing Excel using PC File Server

Ksharp — Fri, 18 Sep 2015 07:22:28 GMT

What version is your SAS ? If it is 9.4 , also could try : dbms=xlsx