Proc Import excel that is html

[paul2877]

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!

[Reeza]

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!

---

[paul2877]

I have tried this

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

[art297]

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

[paul2877]

I am on Windows.

[paul2877]

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;

[Reeza]

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

[Reeza]

Post your exact code and log. 

[paul2877]

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

[Reeza]

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?

---

[paul2877]

It did not come from SAS and I don't control the source.

[ChrisHemedinger]

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.

[paul2877]

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?

[ChrisHemedinger]

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:

[paul2877]

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.