topic Re: upload an excel file to box.com using PROC HTTP in SAS Procedures

upload an excel file to box.com using PROC HTTP

HelenLondon — Sat, 30 Dec 2017 20:35:23 GMT

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

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

andreas_lds — Tue, 02 Jan 2018 08:22:04 GMT

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

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

HelenLondon — Tue, 02 Jan 2018 11:54:16 GMT

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

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

abjain — Wed, 03 Jan 2018 01:03:20 GMT

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.

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

HelenLondon — Wed, 03 Jan 2018 12:23:55 GMT

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

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

ChrisHemedinger — Wed, 03 Jan 2018 14:47:31 GMT

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.

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

HelenLondon — Wed, 03 Jan 2018 15:01:19 GMT

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

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

ChrisHemedinger — Wed, 03 Jan 2018 17:57:54 GMT

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.

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

HelenLondon — Wed, 03 Jan 2018 18:56:37 GMT

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

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

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

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

HelenLondon — Fri, 05 Jan 2018 14:34:10 GMT

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:

My SAS version:

Any ideas?

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

HelenLondon — Fri, 05 Jan 2018 14:40:32 GMT

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

Thanks for any thoguhts again!

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

jmic_nyk — Tue, 30 Oct 2018 09:21:55 GMT

Hi - I have a similar problem.

Also XLSX, DOCX, PDF files I would like to POST

And I have a CURL statement that works - but would very much like to convert it to PROC HTTP.

So - Is this community-post solved?

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

jmic_nyk — Mon, 12 Nov 2018 11:21:35 GMT

As mentioned earlier I had a similar problem, this code works. The real problem was to detect how to build the Multipart form with the rigt bondary an content types. Feel free to copy.

However I have the same problem in M5 when using "debug level=2" - SAS crashes.

filename req "F:\SASNykredit\CURL-HTTP\HTTP trial\Datafiler\request.txt";
filename resp "F:\SASNykredit\CURL_HTTP\HTTP trial\Datafiler\response.txt";
filename binfile "F:\SASNykredit\CURL-HTTP\HTTP trial\Datafiler\testdata.xlsx";

%let _boundary=3fbd04f5-b1ed-4060-99b9-fca7ff59c113;
%let _cat=Testliste;
%let _publdate=%sysfunc(today(), yymmddd10.);
%let _unpubldate=%sysfunc(intnx(MONTH,%sysfunc(today(),8.),12,S),yymmddd10.);
%let _binname=%scan(%sysfunc(pathname(binfile)),-1,'\');
%let _title=%upcase(&sysuserid) TEST &_publdate.;

%put &=_boundary &=_cat &=_publdate &=_unpubldate &=_title &=_binname;

/*** Fanger fejlkode fra PROC HTTP ***/
%macro prochttp_check_return(_exp_code);
%if %symexist(SYS_PROCHTTP_STATUS_CODE) ne 1 %then
%do;
%put ERROR: Expected &_exp_code., but a response was not received from the HTTP Procedure;
%end;
%else
%do;
%if &SYS_PROCHTTP_STATUS_CODE. ne &_exp_code. %then
%do;
%put ERROR: Expected &_exp_code., but received &SYS_PROCHTTP_STATUS_CODE.;
%put &SYS_PROCHTTP_STATUS_PHRASE.;
%end;
%else
%do;
%put NOTE: Alt i orden, forventet returkode: &SYS_PROCHTTP_STATUS_CODE. &SYS_PROCHTTP_STATUS_PHRASE.;
%end;
%end;
%mend prochttp_check_return;

/*** MULTIPART-FORM data ***/
data _null_;
file req termstr=CRLF;
put "--&_boundary.";
put 'Content-Disposition: form-data; name="publish"';
put;
put "&_publdate";
put "--&_boundary.";
put 'Content-Disposition: form-data; name="unpublish"';
put;
put "&_unpubldate";
put "--&_boundary.";
put 'Content-Disposition: form-data; name="category"';
put;
put "&_cat";
put "--&_boundary.";
put 'Content-Disposition: form-data; name="title"';
put;
put "&_title";
put "--&_boundary.";
put 'Content-Disposition: form-data; name="uploadfile"; filename='"&_binname.";
put 'Content-Type: application/octet-stream';
put;
%_run(w);

/*** Tilføjer binær fil ***/
data _null_;
file req mod recfm=n;
infile binfile recfm=n;
input c $CHAR1.;
put c $CHAR1. @@;
%_run(w);

/*** Afslutning ***/
data _null_;
file req mod termstr=CRLF;
put / "--&_boundary.--";
%_run(w);

proc http
URL="https://ptsv2.com/t/kommunikationsplatform/post"
METHOD="POST"
CT="multipart/form-data; boundary=&_boundary"
in=req
out=resp;
*DEBUG Level=1;
run;

%put &=sys_prochttp_status_code.;
%put &=sys_prochttp_status_phrase.;

%prochttp_check_return(200);

filename req clear;
filename resp clear;
filename binfile clear;

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

smijoss1 — Tue, 23 Apr 2019 01:08:04 GMT

where do i get the value for macro variable _boundary, or is it any constant ?

Thank you for sharing your code