Help using Base SAS procedures

upload an excel file to box.com using PROC HTTP

Reply
Occasional Contributor
Posts: 11

upload an excel file to box.com using PROC HTTP

Hi,

I am new to use PROC HTTP. I found a post here is very useful to my situation but I couldn't make it work for me. I have successfully uploaded an excel file via cURL as below: 

curl https://upload.box.com/api/2.0/files/content -H "Authorization: Bearer <my token>" -X POST \
 -F attributes="{\"name\":\"test.xlsx\", \"parent\":{\"id\":\"43742861365\"}}" \
 -F file=@c:\temp\test.xlsx
So my environment is not an issue. 

I can upload text files successfully using below codes, but get '400 Bad Request' when I try to upload an excel file or other binary files:

 

filename copyfile "c:\temp\test.xlsx" ;
filename request TEMP ;
%let boundary=foobar;
%let boxfolderID=43742861365;
%let updestfile=upload.xlsx;
%let my_token=12345dfdgrfhggfnhgn;

filename resptext temp;
filename resphdrs temp;

 

*box.com upload require json two parts for multipart/form-data;

data _null_;
    file request termstr=CRLF;
    if _n_ = 1 then do;
        put "--&boundary";
        put 'Content-Disposition: form-data; name="attributes"';
        put ;
        put '{"name":"' "&updestfile" '", "parent":{"id":"' "&boxfolderID" '"}}';
        put "--&boundary";
        put 'Content-Disposition: form-data; name="file"; filename="' "&updestfile" '"';
        put "Content-Type: application/vnd.ms-excel";
        /*put "Content-Type: application/octet-stream";*/

       put ;
      end;
run;

 

*append my binary file;
data _null_;  
    file request mod recfm=n;
    infile copyfile recfm=n;
    input c $CHAR1.;
    put c $CHAR1. @@;
run;

 

data _null_;
    file request mod termstr=CRLF;
    put "--&boundary--";
run;

data _null_;
    length bytes $1024;
    fid = fopen("request");
    rc = fread(fid);
    bytes = finfo(fid, 'File Size (bytes)');
    call symput("FileSize",trim(bytes));
    rc = fclose(fid);
    put bytes;
  run;

proc http
      url="https://upload.box.com/api/2.0/files/content"
        method    = "POST"
        out       = resptext
        headerout = resphdrs
        in        = request
        ct        = "multipart/form-data; boundary=&boundary;Content-Length=&filesize;Content-MD5"   ;
    headers      "Authorization"  = "Bearer &my_token"    ;
 run;

 

 I am not sure why it is not working. The same codes works fine for .csv files, but not .xlsx files. Thanks in advance.

 

Helen

Super Contributor
Posts: 498

Re: upload an excel file to box.com using PROC HTTP

Posted in reply to HelenLondon

Assuming that you have a local SAS installation: have to tried using x-command with the tested curl-command?

Occasional Contributor
Posts: 11

Re: upload an excel file to box.com using PROC HTTP

Posted in reply to andreas_lds

Hi Andrea,

 

Yes. I tested it using x command on my local SAS and it works well. But I would like to use PROC HTTP since I will use it on our SAS server environment eventually. Any ideas something wrong with my codes on PROC HTTP part? Thanks again,

 

Helen

Regular Learner
Posts: 1

Re: upload an excel file to box.com using PROC HTTP

Posted in reply to HelenLondon

Helen: Can you please share the piece of code you submitted via X command. I'm curious how you dealt with quotes.

I'm doing something similar Thanks. 

Occasional Contributor
Posts: 11

Re: upload an excel file to box.com using PROC HTTP

of course. For windows, you need to add '\' before double quotes:

 

x 'curl https://upload.box.com/api/2.0/files/content \
 -H "Authorization: Bearer <my_token>" -X POST \
 -F attributes="{\"Content-Disposition\": \"form-data\",\"Content-Type\": \"application/octet-stream\",\"name\":\"test.xlsx\", \"parent\":{\"id\":\"<folder_ID>\"}}" \
 -F file=@c:\temp\test.xlsx' ;

 

Can anybody explain why my proc http codes is not working? Thanks,

 

Helen

Community Manager
Posts: 3,354

Re: upload an excel file to box.com using PROC HTTP

Posted in reply to HelenLondon

If it works with CSV but not Excel, then I'm suspicious of the DATA step that copies the binary content.  Your version:

 

data _null_;   
    file request mod recfm=n;
    infile copyfile recfm=n;
    input c $CHAR1.; 
    put c $CHAR1. @@; 
run;

Any reason you didn't go with the version in the thread you cited?

 

data _null_;
    file request mod recfm=f lrecl=1;
    infile copyfile  recfm=f lrecl=1;
    
    input;
    put _infile_;
run;

The CHAR format does not trim spaces, but with a width of 1 that shouldn't cause an issue.  And I think the width of 1 would be just 1 byte even on UTF-8 sessions, but I haven't tested.

Occasional Contributor
Posts: 11

Re: upload an excel file to box.com using PROC HTTP

Posted in reply to ChrisHemedinger

Hi Chris,

 

I tried that codes in the first place and got same 400 bad request.  I suspect something wrong when I combine plain texts with binary file contents inserted between. So I played different copy binary codes without any luck. The codes for copying binary file which I have tried as below:

data _null_; file request mod recfm=f lrecl=1; infile copyfile recfm=f lrecl=1; input; put _infile_; run;

data _null_; file request mod recfm=n; infile copyfile recfm=n; input c $CHAR1.; put c $CHAR1. @@; run;

data _null_; file request mod recfm=n; infile copyfile recfm=n; input c $CHAR1.@; put c $CHAR1. ; run;

Is there anything I can try further? thanks,

 

Helen

 

Community Manager
Posts: 3,354

Re: upload an excel file to box.com using PROC HTTP

Posted in reply to HelenLondon

I notice that you also have "Content-MD5" in the POST, but I didn't see where you computed/specified the MD5 hash for the file.  Is it required for this Box API?  I don't see reference to it in the cURL command, but maybe that's handled by cURL under the covers.

 

And to be clear, the headersout file is consistently just "400 bad request" -- not a more specific error? 

 

And if you have the latest SAS 9.4 maint 5, you could try the DEBUG statement in PROC HTTP.

 

 

Highlighted
Occasional Contributor
Posts: 11

Re: upload an excel file to box.com using PROC HTTP

Posted in reply to ChrisHemedinger

Hi Chris,

 

Actually I saw Box.com (here) saying 'A different Box URL, https://upload.box.com/api/2.0/files/content, handles uploads. This API uses the multipart post method to complete all upload tasks. You can optionally specify a Content-MD5 header with the SHA-1 hash of the file to ensure that the file is not corrupted in transit.' 'Content-MD5' is just my part of playing options. With or without it, it doesn't make any difference. Below is the contents from log (unfortunately I don't have SAS 9.4M5, my SAS version is 9.4M4, but I will ask our IT to get M5 and use DEBUG once I have it):

63 %put filesize=&filesize;

filesize=5206757

64

65 *Submit the Upload request to Box ;

66 proc http

67 url="https://upload.box.com/api/2.0/files/content"

68 method = "POST"

69 out = resptext

70 headerout = resphdrs

71 in = request

72 ct = "multipart/form-data; boundary=&boundary;Content-Length=&filesize"

73 ;

74 headers "Authorization" = "Bearer &my_token";

75 run;

NOTE: PROCEDURE HTTP used (Total process time):

real time 2.59 seconds

cpu time 2.26 seconds

NOTE: 400 Bad Request

76

77 /* Macro that simply echoes the contents of a fileref to the SAS log */

78 %macro echofile(file);

79 data _null_; infile &file; input; put _infile_; run;

80 %mend;

81 %echofile(resphdrs);

NOTE: The infile RESPHDRS is:

Filename=C:\Users\user\AppData\Local\Temp\SAS Temporary Files\_TD23544_DTOP90_\#LN00013,

RECFM=V,LRECL=32767,File Size (bytes)=156,

Last Modified=03Jan2018:13:32:47,

Create Time=03Jan2018:13:32:47

HTTP/1.1 400 Bad Request

Date: Wed, 03 Jan 2018 18:32:45 GMT

Content-Type: text/html; charset=UTF-8

Content-Length: 0

Age: 7

Connection: keep-alive

NOTE: 7 records were read from the infile RESPHDRS.

The minimum record length was 0.

The maximum record length was 38.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

Occasional Contributor
Posts: 11

Re: upload an excel file to box.com using PROC HTTP

Posted in reply to HelenLondon

Update: I have sas9.4 M5 installed. First I used 'debug level=1; the log has no difference comparing with without this statement; when I changed to 'level=2', SAS crashed as below:

image.png

My SAS version:image.png

Any ideas?

 

Occasional Contributor
Posts: 11

Re: upload an excel file to box.com using PROC HTTP

Posted in reply to HelenLondon

below is the log when I am using 'debug level=1;':image.png

Thanks for any thoguhts again!

 

Ask a Question
Discussion stats
  • 10 replies
  • 365 views
  • 2 likes
  • 4 in conversation