SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Trouble importing Excel using PC File Server

Reply
New Contributor
Posts: 2

Trouble importing Excel using PC File Server

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

Super User
Posts: 17,829

Re: Trouble importing Excel using PC File Server

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#p0jf3o1i67m044n1j0...

Super User
Posts: 6,938

Re: Trouble importing Excel using PC File Server

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 2

Re: Trouble importing Excel using PC File Server

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.

Super User
Posts: 17,829

Re: Trouble importing Excel using PC File Server

Can you post a sample of the file? It may be faster to convert to cvs as Kurt suggested. Try it without sheet option.
Super User
Posts: 9,681

Re: Trouble importing Excel using PC File Server

What version is your SAS ? If it is 9.4 , also could try : dbms=xlsx
Ask a Question
Discussion stats
  • 5 replies
  • 386 views
  • 1 like
  • 4 in conversation