09-16-2015 10:48 PM
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
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.
09-17-2015 12:23 AM
PC Files are for later versions not earlier versions.
Try DBMS=Excel4 or Excel5
09-17-2015 01:47 AM
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
09-17-2015 08:32 AM
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.