topic Re: Proc Import excel that is html in SAS Enterprise Guide

Proc Import excel that is html

paul2877 — Mon, 18 Dec 2017 20:07:19 GMT

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!

Re: Proc Import excel that is html

Reeza — Mon, 18 Dec 2017 20:20:29 GMT

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!

Re: Proc Import excel that is html

paul2877 — Tue, 19 Dec 2017 03:28:37 GMT

I have tried this

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

Re: Proc Import excel that is html

art297 — Tue, 19 Dec 2017 04:59:40 GMT

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

Re: Proc Import excel that is html

paul2877 — Tue, 19 Dec 2017 13:38:54 GMT

I am on Windows.

Re: Proc Import excel that is html

Reeza — Tue, 19 Dec 2017 15:00:52 GMT

Post your exact code and log.

Re: Proc Import excel that is html

paul2877 — Tue, 19 Dec 2017 16:22:13 GMT

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;

Re: Proc Import excel that is html

Reeza — Tue, 19 Dec 2017 16:27:19 GMT

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

Re: Proc Import excel that is html

paul2877 — Tue, 19 Dec 2017 17:39:09 GMT

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

Re: Proc Import excel that is html

Reeza — Tue, 19 Dec 2017 17:47:02 GMT

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?

Re: Proc Import excel that is html

paul2877 — Tue, 19 Dec 2017 17:50:50 GMT

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

Re: Proc Import excel that is html

ChrisHemedinger — Tue, 19 Dec 2017 17:50:53 GMT

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.

Re: Proc Import excel that is html

paul2877 — Wed, 20 Dec 2017 14:50:20 GMT

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?

Re: Proc Import excel that is html

ChrisHemedinger — Wed, 20 Dec 2017 15:03:17 GMT

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:

Re: Proc Import excel that is html

paul2877 — Wed, 20 Dec 2017 15:18:23 GMT

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.

Re: Proc Import excel that is html

Tom — Wed, 20 Dec 2017 15:35:32 GMT

@paul2877 wrote:
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.

Go back to to whoever asked for this and tell that they have not supplied you the source data yet. What you are trying to read is a report, not a data transfer. Ask for the data to be supplied in a format like CSV file that is easy to read.

Re: Proc Import excel that is html

Reeza — Wed, 20 Dec 2017 15:40:54 GMT

Can you provide a sample of the file?

Re: Proc Import excel that is html

paul2877 — Wed, 20 Dec 2017 15:43:09 GMT

I cannot as its company owned. Basically it looks like an .xls file but when I open it and go to save it.. it prompts me as an .HTML file

Re: Proc Import excel that is html

Reeza — Wed, 20 Dec 2017 15:50:37 GMT

Create a replica that has the same features. Or preferably, open the file, mask the data, and then provide that file.

Re: Proc Import excel that is html

Tom — Wed, 20 Dec 2017 16:17:03 GMT

@paul2877 wrote:
I cannot as its company owned. Basically it looks like an .xls file but when I open it and go to save it.. it prompts me as an .HTML file

It IS an HTML file. Just because the name ends with .XLS does not mean the content is an Excel file in the XLS format. The content is pure HTML. Excel is smart enough to detect that and will convert it into a spreadsheet for you.

Posting an example of the file will help others guide you in writing SAS code that can parse the data out of the HTML tags.

Open the file in a TEXT editor, not in Excel. Change any company or personal details and save it back as a text file. If possible remove some of the rows of data (normally in an HTML file rows will be enclosed by <TR> and </TR> tags) to make the file smaller.

Also change the file extension on the saved file to .TXT instead of .XLS or .HTML so that if you post it to the forum we can browse it without downloading.