BookmarkSubscribeRSS Feed
paul2877
Calcite | Level 5

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!

34 REPLIES 34
Reeza
Super User

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
Calcite | Level 5

I have tried this

 

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

art297
Opal | Level 21

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
Calcite | Level 5

I am on Windows.

paul2877
Calcite | Level 5

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
Super User

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
Super User

Post your exact code and log. 

paul2877
Calcite | Level 5

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

Reeza
Super User

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
Calcite | Level 5
It did not come from SAS and I don't control the source.
ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
paul2877
Calcite | Level 5
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
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
paul2877
Calcite | Level 5
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 34 replies
  • 2378 views
  • 2 likes
  • 5 in conversation