Proc Import excel that is html

[Tom]

---

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

[Reeza]

Can you provide a sample of the file?

[paul2877]

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

[Reeza]

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

[Tom]

---

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

[paul2877]

So how can I get SAS to treat it as a text file and remove the html portion

[Reeza]

You can't, you have to parse it. 

If you have R try the htmltab() or the readHTMLTable().

[paul2877]

sorry.. can you provide me with an example how I can do this?

[Reeza]

Sure, but I/we need something to work with which is why we need demo files. 

[Reeza]

There's no underlying HTML in that file.

[ChrisHemedinger]

Hi @paul2877, your attachments here are not openable by Excel because they are missing parts.  Have you tried to open them locally?  Really no point posting them here if you can't even open them with your own instance of Excel.

[paul2877]

Here you go

[Reeza]

---

@paul2877 wrote:

Here you go

---

File is corrupt and will not open on my machine, Excel 2010....