DATA Step, Macro, Functions and more

Importing XLSX from URL Issue

Reply
Contributor
Posts: 60

Importing XLSX from URL Issue

[ Edited ]

I've found some code that works great at importing a xlsx file from a URL. The code successfully creates a duplicate of the file onto my computer but when I try to import the file into SAS I get the following issue.

 

Could not find 'workbook' entry in xlsx file.  rc=8014900E (-2146136050)
Requested Input File Is Invalid
ERROR: Import unsuccessful.  See SAS Log for details.

 

If I open the file then save it SAS can import the file successfully. That isn't too big of an issue I would just prefer it be automated. I'm quite sure the file is an xlsx file as there is no prompt warning from excel when I open the file. I have also downloaded a third party program that recognized the file as an xlsx file.

 

Any suggestions?

 

filename cm1url HTTP "http://tti.tamu.edu/documents/ums/congestion-data/complete-data.xlsx";	

data _null_;
  n=-1;
  infile cm1url recfm=s nbyte=n length=len;
  input;
  file  "C:\temp\TestPROCtoNDC.xlsx" recfm=n;
  put _infile_ $varying32767. len;
run;

proc import file='C:\temp\TestPROCtoNDC.xlsx' out=PCR_day
	dbms=xlsx replace;
	getnames=no;
run;	
PROC Star
Posts: 277

Re: Importing XLSX from URL Issue

Posted in reply to michelconn

Maybe a stupid question, but what happens if you skip the copy part, and just try to import the file from where it is?

filename cm1url HTTP "http://tti.tamu.edu/documents/ums/congestion-data/complete-data.xlsx";	


proc import file=cm1url out=PCR_day
	dbms=xlsx replace;
	getnames=no;
run;	
Contributor
Posts: 60

Re: Importing XLSX from URL Issue

From my understanding and the forum I found this answer on that will only work with CSV. With the excel format you have to copy the file essentially bit by bit.
Ask a Question
Discussion stats
  • 2 replies
  • 114 views
  • 0 likes
  • 2 in conversation