Desktop productivity for business analysts and programmers

Proc Import excel that is html

Reply
Contributor
Posts: 32

Proc Import excel that is html

I am trying to import an excel file that was created by a machine process. The file is saved as .xls however it is html base. I need to import it but when I try and import it gives me an error its-->

 

Spreadsheet isn't from Excel V5 or later. Please open it in Excel and Save as V5 or later

Requested Input File Is Invalid.

 

I need to import these files and I can't simply open and resave as excel!

Super User
Posts: 22,820

Re: Proc Import excel that is html

It's not really an XLS file so PROC IMPORT won't work.

 

The other option is to parse the HTML itself but that isn't fun either. 

You can read it in as text file and attempt to recreate the idea. 

 

IMO, using a visual basic script to automate the conversion of the HTML files to XLS or XLSX is your fastest solution.

SAS has helpfully provided a script to help you with that:

http://support.sas.com/kb/43/496.html

 


paul2877 wrote:

I am trying to import an excel file that was created by a machine process. The file is saved as .xls however it is html base. I need to import it but when I try and import it gives me an error its-->

 

Spreadsheet isn't from Excel V5 or later. Please open it in Excel and Save as V5 or later

Requested Input File Is Invalid.

 

I need to import these files and I can't simply open and resave as excel!


 

Contributor
Posts: 32

Re: Proc Import excel that is html

I have tried this

 

options noxsync noxwait;.. however I get these errors-->ERROR 13-12: Unrecognized SAS option name NOXSYNC.

PROC Star
Posts: 8,094

Re: Proc Import excel that is html

What operating system are you on? That option is for Windows. Are you on a system that can run visual basic?

 

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 32

Re: Proc Import excel that is html

I am on Windows.

Contributor
Posts: 32

Re: Proc Import excel that is html

ERROR: Insufficient authorization to access /sas94apps/prod94config/Lev1/RAApp/c:\temp\convert\temp.vbs.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

ERROR: Shell escape is not valid in this SAS session.
54
55
56 options noxwait noxsync;
_______
13
ERROR 13-12: Unrecognized SAS option name NOXWAIT.

56 ! options noxwait noxsync;

Super User
Posts: 22,820

Re: Proc Import excel that is html

So it looks like you're running SAS on a server and it doesn't have access to your local machine. 

You need to create the VBS file on the server and run it there, but I'm not sure Unix supports VBS ....

 

Super User
Posts: 22,820

Re: Proc Import excel that is html

Post your exact code and log. 

Contributor
Posts: 32

Re: Proc Import excel that is html

do you know of any other way to import this type of data?

Super User
Posts: 22,820

Re: Proc Import excel that is html

Go back and find the source data instead....if it came out of SAS into ODS reports it must exist in some other format somewhere first.

 


paul2877 wrote:

do you know of any other way to import this type of data?


 

Contributor
Posts: 32

Re: Proc Import excel that is html

It did not come from SAS and I don't control the source.
Community Manager
Posts: 3,344

Re: Proc Import excel that is html

You could use the DATA step to read and parse the table structure/values that you need -- similar to scraping a web page for data.  You already have the HTML, so no need to fetch it.  Therefore, you could pick it up from step 2 on this blog post.

 

But it won't be easy going.  Whoever created the HTML-ified XLS file didn't do you any favors by supplying it as a mode of data exchange.  Did that person/entity create it in SAS in the first place?  Any chance of getting connected with the original data?

 

JMP (another tool from SAS) can read HTML tables -- I mentioned that in my blog post too.

Contributor
Posts: 32

Re: Proc Import excel that is html

Posted in reply to ChrisHemedinger
so the file is stored in our folder. It is saved as an .xls file however it seems that it was created as from html and saved with an .xls extension. The actual file is visible but in order for me to import it into SAS I need to open it and resave as a .xls file extension again. Any thoughts?
Community Manager
Posts: 3,344

Re: Proc Import excel that is html

From your replies it seems that you are resigned to your fate here.  You have been supplied with HTML files that are masquerading as XLS files, and you must spin this straw into gold.

 

Given that scenario, the options I see are:

- Use DATA step parsing techniques like SCAN and PRXMATCH to pull the HTML table values out into something that looks like data

- Use more tasks in SAS Enterprise Guide to download the fake XLS file to your local PC, automate open and save with Excel (or save as CSV), and re-upload the file.  Then use PROC IMPORT or DATA step to read it.

 

The Copy Files task (used for the download and later upload steps) is built into SAS Enterprise Guide 7.13 and later -- in the Tasks-Data menu.  In earlier releases you can obtain it as a custom task.

 

The System Command task, which you can use to run local shell commands including scripts to automate Excel, is available as a custom task.

 

A successful flow might look like this:

 

flow.png

Contributor
Posts: 32

Re: Proc Import excel that is html

Posted in reply to ChrisHemedinger
I think this is a little out of my league.. I am at the mercy of the company, we only have EG 6.1 and I need to put this into a scheduler and it doesn't run off of my local pc.
Ask a Question
Discussion stats
  • 34 replies
  • 305 views
  • 2 likes
  • 5 in conversation