The SAS Output Delivery System and reporting techniques

Problem with Proc Import reading an Excel file created with ExcelXP tagset?

Reply
N/A
Posts: 0

Problem with Proc Import reading an Excel file created with ExcelXP tagset?

Hi all, has anyone had a problem with reading in an excel file created using the ods excelxp tagsets? When I try this:

proc import datafile="H:\APRIL 2007\Quick Recovery Report for APRIL 2007.xls"
out = tempQuickRec
dbms=excel replace;
sheet="Consolidated";
run;

I get the following error :

ERROR: Connect: External table is not in the expected format.
ERROR: Error in the LIBNAME statement.

If I replace the excel file with one created using excel it works fine. I'm running v9.1.3 sp3 with version 1.7 of the excelxp tagsets.

Thanks.
SAS Super FREQ
Posts: 8,865

Re: Problem with Proc Import reading an Excel file created with ExcelXP tagset?

Posted in reply to deleted_user
Hi...
When you use TAGSETS.EXCELXP to create a file, you CAN use the following invocation:
[pre]
ods tagsets.excelxp file='c:\temp\myfile.xls';
...SAS code...
ods tagsets.excelxp close;
[/pre]

However, you are creating a Spreadsheet Markup Language XML file. You can prove this to yourself by opening the file -- created in the above code -- myfile.xls -- with Notepad or ANY text editor. What you should see at the top of the file (in Notepad) is this:
[pre]
<?xml version="1.0" encoding="windows-1252"?>

<Workbook xmlns="urn:schemas-microsoft-comSmiley Surprisedffice:spreadsheet"
xmlns:x="urn:schemas-microsoft-comSmiley Surprisedffice:excel"
xmlns:ss="urn:schemas-microsoft-comSmiley Surprisedffice:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
[/pre]

What does that mean? That means that the XML file is not a "true" binary Excel file -- it is just an XML file that Excel knows how to open. (Well, Excel "knows" how to open the XML because the XML conforms to the Microsoft spec -- that doesn't make it a "binary" Excel file -- no matter what the file extension is.)

PROC IMPORT is geared to read a "true" binary Excel file (a file created with Excel) -- NOT an XML file, such as the file created by the ExcelXP tagsets. If you write your ODS invocation as:
[pre]
ods tagsets.excelxp file='c:\temp\myfile2.xml';
...SAS code...
ods tagsets.excelxp close;
[/pre]

This is a more accurate representation of what the file is -- Spreadsheet Markup Language XML -- however -- the XML file extension does not automatically cause SAS to launch Excel for you when you click the icon in the Results window or for Windows Explorer to launch Excel for you when you click the icon in an Explorer window.

If you resave the XML file as an XLS file using FILE --> Save As in Excel, then you should be able to read it with PROC IMPORT.

However, since you used a SAS dataset to create the ExcelXP file, why not just use THAT SAS dataset instead of trying to re-import the ODS output file. I can imagine that, if your ODS output was the result of a summary procedure or an analysis procedure, that you might want to get that information from the procedure into Excel. You can create SAS datasets from procedures by using the ODS OUTPUT statement or by using the OUTPUT statement available in most SAS procedures.

For example, consider this program:
[pre]
ods tagsets.excelxp file='c:\temp\shoe_rept.xls' style=sasweb;

ods output onewayfreqs=work.freqout;
proc freq data=sashelp.shoes;
tables subsidiary;
run;
ods tagsets.excelxp close;
[/pre]

This program is creating 2 different files: shoe_rept.xls (really an XML file) AND the SAS data set WORK.FREQOUT -- which contains the results of the OneWayFreq in a SAS dataset.

I guess it all comes down to what you need to do and why you need to read the output from ExcelXP back into SAS.

cynthia
N/A
Posts: 1

Re: Problem with Proc Import reading an Excel file created with ExcelXP tagset?

Posted in reply to Cynthia_sas

In my case we will update the excel and we should import it back to sas. By chance is there any work around for this to import?

New Contributor
Posts: 3

Re: Problem with Proc Import reading an Excel file created with ExcelXP tagset?

I have the same issue.  The only work around I found is to have the user make their updates to the exported XML file, use the Save As command in Excel to save it as a XLS or XLSX file, and send it back to me.

SAS Super FREQ
Posts: 8,865

Re: Problem with Proc Import reading an Excel file created with ExcelXP tagset?

Hi, you have responded to and reactivated a 7 year old posting. It would be much better for you to start a new post with your question and refer to this post. However, the answer is still the same. You don't need a "workaround", you need to adjust to the reality of using TAGSETS.EXCELXP. That ODS destination (TAGSETS.EXCELXP) does NOT, NOT, NOT create a true binary Excel file that can be imported. So your choices are:

1) create the XML file using TAGSETS.EXCELXP and manually or with a VBScript program save the file as proprietary .XLS or .XLSX

2) create the XML file using TAGSETS.EXCELXP and manually export the file to CSV format and read the CSV file back into SAS

3) create the XML file using TAGSETS.EXCELXP and use SAS XML Libname Engine with an XML Map to import the XML file back into SAS, as described in this paper http://www2.sas.com/proceedings/sugi31/115-31.pdf

cynthia

Ask a Question
Discussion stats
  • 4 replies
  • 1847 views
  • 1 like
  • 4 in conversation