Salesforce blobs

[simon]

Has anyone been able to succesfully download a Salesforce attachment (jpg) using the Download tool?

I tried the SF input tool but it can't handle binary data so that one's out. 

I did have more luck using curl like this:

curl https://yourInstance.salesforce.com/services/data/v20.0/sobjects/Attachment/001D000000INjVe/body -H "Authorization: Bearer token"

But I really want it to work with the download tool, then use 'Blob Convert' tool to convert from blob to jpg. 

Any help appreciated!

Simon

[asteryx]

Hi - I'm afraid I don't have a solution for you, but I have a similar issue and wanted to add to your question before starting a new thread.

I'm looking for info on how to identify the location of attachments - do you have to know the "table" name as you do when downloading other SForce data?

And how do you identify a particular attachment? Is there a table in SForce that holds the meta data for an attachment?

I found what looks like relevant information here:

http://stackoverflow.com/questions/5865736/how-can-i-extract-attachments-from-salesforce

But there's a lot of knowledge assumed in that post.

Thanks.

[simon]

Hi,

You have to traverse the tables, I imagine each SF instance/ table structure may look different? 

Look in SF first to see what object your attachments tie to. Opportunity? Proposal?

Mine is the proposal. So when you query the attachment table like so using the parent Id (proposal ID)

Select Id, ParentId, Name, ContentType, Body from Attachment WHERE Id='00Pa000000cuTnJXXX'

the ID will be the attachment ID and data will look like this:

ParentId	Id	Name	ContentType	Body
001a000001JsueBAAX	00Pa000000cuTnJXXX	Cover.jpg	image/jpeg	/services/data/v20.0/sobjects/Attachment/00Pa000000cuTnJXXX/Body

• 
  Then get your Bearer access token
• Your image_url will be [instance_url]+[Body] or 

https://csXX.salesforce.com/services/data/v20.0/sobjects/Attachment/00Pa000000cuTnJXXX/Body

with formula tool create batch file like so

bat='.\curl\curl.exe ' +[instance_url]+[Body] +' -H "Authorization: Bearer '+[Access_Token]+ '" -o ".\SF_Output.jpg"'

and feed it into the run command tool (write option) then attach image tool to viz your jpg attachment. I hope your attachments are not PDFs...

so I use curl and it works great but still would like to try this with download tool. good luck!

Simon

[asteryx]

Thanks, Simon.

This is very helpful. I will give this a try.

Is your system only capturing .jpgs? Or does SForce only store attachments as .jpgs as far as you know?

Our users are sending us .xlsx files, so I hope they stay that way.

[asteryx]

So, I tried following your procedure, Simon, but I've given up.

Getting the Bearer access token out of Salesforce has proven too difficult. I got to the App page in Salesforce, but don't see the tokens and secrets I was expecting (which seemed to be what's needed according to some research I did on Bearer Access Tokens).

I am not versed in the concepts of APIs and call back URLs, so it's become a rabbit hole.

Instead, we have to export files from Salesforce and drop them in a folder where Alteryx can read them using basic input tools.

Too bad Alteryx doesn't offer this functionality as more of a built-in tool.

[simon]

Hi Asteryx,

We have jpgs and pdf's with occasional spreadsheets. You should be able to download whatever as long as you have the attachment path/ID.

Not sure if you want to delve into this further but it's definitely a multi-part step. You need:

1. username
2. password
3. consumer key
4. consumer secret
5. security token
6. Bearer token (you get this using 1-5)

Make sure you are admin or ask your SF admin.

To get 3&4 do this: 

Go to your username in top right and click Setup. In Left Margin, go to Build>Create>Apps and from the list go to Connected Apps and click the Connected App Name (e.g. Alteryx) to get Key and Secret.

To get 5 do this: 

In your SF instance, go to your user top right, and select My Settings. Then in left margin select you should see 'my settings'. drill into personal and you should see 'reset security token'. you should get an email with the new token in it. Save it, protect it and don't lose it (else reset again). If you don't get an email, check email address in 'Personal info'. 

To get 6 Authorization Bearer token, you need all five previous pieces. This token is not available through SF interface. You get it thru curl or download tool. 

Start with text input with dummyinput to get process started. Then connect to formula tool and add this:

• clientid= "your consumer key"
• client_secret = "your consumer secret"
• grant_type="password" (literally)
• username= "your username"
• password= "your password" +"securitytoken" 
  • so if pass = "123" and securitytoken "abc" then password = "123abc"
• url_oath = ""https://test.salesforce.com/services/oauth2/token"
  • so I am hitting SF sandbox (hitting production which is https://login.salesforce.com...)

Then connect formula tool to Download tool. In Download tool on basc tab, select 'url_oath' field as the url field. Headers tab stays blank, and payload you do a POST and select select/check remaing 5 fields from above.

Run it and your output will look like this - make sure DownloadHeader says "HTTP/1.1 200 OK" else troubleshoot 1-5 above.

DownloadData

{"access_token":"00D6C0000000RdD!AQEAQGJZ6xOd.T2svHOrVYEXQBEexeB00D12345678901234567890qwerty","instance_url":"https://csXX.salesforce.com",...}

Drop a JSON Parse tool after and select download data. Running it again, you get everything you need:

JSON_Name	JSON_ValueString
access_token	00D6C0000000RdD!AQEAQGJZ6xOd.T2svHOrVYEXQBEexeB00D12345678901234567890qwerty
instance_url	https://csXX.salesforce.com
id	https://test.salesforce.com/id/00D6C0000000RdDUAU/005a000000B567890
token_type	Bearer
issued_at	1473271088581
signature	Rmf/XZn45fkmrqR123456789vTtjXOc+A=

So here's your authorization bearer token. Use that and only that to pull your attachment. These tokens do expire after a while so you may have to refresh. 

Hope this helps!

Simon

[asteryx]

Great of you to offer so much detail, simon!

I got stuck on this part:

To get 3&4 do this: 

Go to your username in top right and click Setup. In Left Margin, go to Build>Create>Apps and from the list go to Connected Apps and click the Connected App Name (e.g. Alteryx) to get Key and Secret.

I sat next to the SF Admin and got to the Connected App, but there was no Key or Secret shown on the page. (I saw some screen shots from the web that show what I'm looking for. Didn't see the section.) Is that only visible to a Developer?

Alteryx was already listed on the Connected App page with some settings created by someone else a while back (no longer on staff). I'm not sure if some of those settings may have led to the Key and Secret being unused (and therefore not displayed).

So - we don't have the resources to figure the SForce side out. Our Admin is not familiar with these parts of SForce.

[simon]

Maybe since I am admin I see more... You may also see more in live vs sandbox.

Try this:

once you log in to live/sandbox, go top right and click setup then in the left margin scroll down. Don't click Managed Apps - you won't see key/secret here. Instead, scroll down further to 'BUILD' category and click 'Create' and then 'Apps'. See screenshot. Then you get a list. One category is connected apps. One of my connected apps is Alteryx - may be different for you.

(If you don't see a connected app like Alteryx here - you have to actually create it first). 

When I click on Alteryx, this is what I see. Maybe some of these settings will help you establish a connected app.

Same as before, copy key/secret and keep in a safe place.

Hope this helps!

Simon

[asteryx]

Hi Simon,

Thank you again for this great advice. Sorry it's taken a while for me to respond... Had to put the effort on hold for a bit.

Thanks to your feedback, I have found my token and secret. I built the workflow you suggest and it seems to do what it's supposed to.

Can I ask you for one more (I hope) bit of help? I've tried finding more info on DownloadData format and JSON parsing, but haven't seen anything that explains what I'm seeing.

My DownloadData field produces the following string field. When I use the JSON Parse tool, I get an "Invalid value. at character position: 2" error message.

DownloadData

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" style="visibility: visible;"><head><meta name="viewport" content="initial-scale=1.0"/><meta name="description" content="Salesforce Customer Secure Login Page. Login to your Salesforce Customer Account." /><title>Login | Salesforce</title><meta name="format-detection" content="telephone=no" /><link rel="stylesheet" type="text/css" href="/css/sfdc_198.css" /><style type="text/css">html{visibility: hidden;}a{color:#0070d2;}body{background-color:#F4F6F9;}#content,.container{background-color:#ffffff;}#header{color:#16325c;}body{display: table; width:100%;}#content{margin-bottom:24px;}#wrap{height:100%;}</style><style type="text/css"> html { visibility: hidden; } </style><script type="text/javascript">if (self == top) {document.documentElement.style.visibility = 'visible';} else {document.write = ''; top.location = self.location; setTimeout(function(){document.body.innerHTML='';}, 1);window.self.onload=function(evt){document.body.innerHTML='';};}</script><script>var SFDCSessionVars={"server":"https://login.salesforce.com/login/sessionserver202.html","im":false,"ah":"active","save":"Save","sa... 1 Change","sum":"#p# Saved Usernames","iaac":false,"hac":"Choose a Username","suo":"1 Saved Username","title":" | Salesforce","saveMany":"Save #p# Changes","lpt":"Login","host":"login.salesforce.com","le":false,"heu":"Edit Username List","ic":false,"lh":false,"hidp":"Log In Using","ih":"inactive","dc":"Username removed. Click Save to Commit Changes."};</script><script src="/jslibrary/SfdcSessionBase202.js"></script><script src="/jslibrary/LoginHint202.js"></script><script>LoginHint.hideLoginForm();</script><link rel="canonical" href="https://login.salesforce.com/?locale=us"/></head><body onload="lazyload();"><div id="left" class="pr"><div id="wrap"><div id="main"><div id="wrapper"><div id="logo_wrapper" class="standard_logo_wrapper mb24"><h1 style="height: 100%; display: table-cell; vertical-align: bottom;"><img id="logo" class="standard_logo" src="/img/logo198.png" alt="Salesforce" border="0" name="logo"></div></h1><div id="header" class="mb24"></div><div id="content"><div id="chooser" style="display: none"><div class="loginError" id="chooser_error" style="display:none;"></div><div><span id="chooser_label" class="label"></span><a href="javascript&colon;void(0);" id="edit" class="fr small" onclick="LoginHint.showEdit();">Edit List</a></div><div class="scrollable mt8"><ul id="idlist"></ul></div></div><div id="manager" style="display: none"><div class="nonscrollable mt8"><ul id="editlist" class="mb24" aria-live="polite" aria-relevant="additions removals"></ul></div><div class="mt16"><button class="button primary fiftyfifty right" id="hint_save_edit" name="Save" onclick="LoginHint.saveHintEdit();">Save</button><button class="button secondary fiftyfifty" id="hint_back_edit" name="Cancel" onclick="LoginHint.showChooser();">Cancel</button></div></div><div id="theloginform"><form name="login" method="post" id="login_form" onsubmit="handleLogin();" action="https://login.salesforce.com/" target="_top" autocomplete="off" novalidate="novalidate"><fieldset style="display:none">

<input type="hidden" name="un" value="" />

 <input type="hidden" name="width" value="" />
 <input type="hidden" name="height" value="" />
 <input type="hidden" name="hasRememberUn" value="true" />
 <input type="hidden" id="login_startUrl" name="startURL" value="" />
 <input type="hidden" name="loginURL" value="" />
 <input type="hidden" name="loginType" value="" />
 <input type="hidden" name="useSecure" value="true" />
 <input type="hidden" name="local" value="" />
 <input type="hidden" name="lt" value="standard" />
 <input type="hidden" name="qs" value="" />
 <input type="hidden" name="locale" value="" />
 <input type="hidden" name="oauth_token" value="" />
 <input type="hidden" name="oauth_callback" value="" />
 <input type="hidden" name="login" value="" />
 <input type="hidden" name="serverid" value="" />
 <input type="hidden" name="display" value="page" /></fieldset>
<div id="usernamegroup" class="inputgroup"><label for="username" class="label">Username</label><div id="username_container"><div id="idcard-container" class="mt8 mb16" style="display: none"><div id="idcard"><img id="idcard-avatar" class="avatar" alt=""><a href="javascript&colon;void(0);" id="clear_link" class="clearlink" onclick="LoginHint.clearExistingIdentity();"><img alt="Log In with a Different Username" class="clearicon" src="/img/clear.png"></a><span id="idcard-identity"></span></div></div><input class="input r4 wide mb16 mt8 username" type="email" value="" name="username" id="username"></div></div><label for="password" class="label">Password</label><input class="input r4 wide mb16 mt8 password" type="password" id="password" name="pw" onkeypress="checkCaps(event)" autocomplete="off"/><div id="pwcaps" class="mb16" style="display:none"><img id="pwcapsicon" alt="Caps Lock is on." width="12"/> Caps Lock is on.</div><input class="button r4 wide primary" type="submit" id="Login" name="Login" value="Log In" /><div class="w0 pr ln3 p16 remember"><input type="checkbox" class="r4 fl mr8" style="" id="rememberUn" name="rememberUn"><label for="rememberUn" class="fl pr db tn3">Remember me</label><br></div></form><div class="w0 links bt pt16 mb20"><a id="forgot_password_link" class="fl small" href="/secur/forgotpassword.jsp?locale=us&lqs=">Forgot Your Password?</a><a id="mydomainLink" class="fr small" href="javascript&colon;void(0);" onclick="DomainSwitcher.enterCustomDomain('Use Custom Domain',' | Salesforce')">Use Custom Domain</a></div></div><div id="mydomainContainer" style="display:none;"><p class="mb16">To go to your company's login page, enter the custom domain name.</p><form onsubmit="DomainSwitcher.handleMyDomain();return false;"><label for="mydomain" class="label">Custom Domain</label><input type="text" class="input wide focus mt8 mb16" name="mydomain" id="mydomain" onkeyup="DomainSwitcher.updateMyDomain(this, event);"/><div id="mydomain_preview" class="mb16 wordwrap">https://<em>domain</em>.my.salesforce.com</div></form><input type="hidden" id="mydomain_suffix" value=".my.salesforce.com"/><input type="hidden" id="community_suffix" value=".na17.force.com"/><button class="button primary fiftyfifty right" id="mydomainContinue" name="Continue" onclick="DomainSwitcher.handleMyDomain();">Continue</button><button class="button secondary fiftyfifty" id="hint_back_domain" onclick="DomainSwitcher.dismissCustomDomain('Login',' | Salesforce');">Back</button></div></div><div id="signup" class="tc mt24"><p class="di mr16">Not a customer?</p><a class="button secondary" id="signup_link" href="https://www.salesforce.com/form/trial/freetrial.jsp?d=70130000000Enus">Try for Free</a></div><div class="tc" id="use_new_identity_div" style="display:none;"><a id="use_new_identity" href="javascript&colon;void(0);" onclick="LoginHint.useNewIdentity();">Log In with a Different Username</a></div></div></div></div><div id="footer">&copy; 2016 salesforce.com, inc. All rights reserved.</div></div><div id="right"><iframe frameborder="0" src="/s.gif" id="marketing" name="marketing" scrolling="no" title="Marketing" tabindex="-1"sandbox="allow-forms allow-pointer-lock allow-popups allow-same-origin allow-scripts" ></iframe></div><div aria-live="polite" aria-atomic="true" class="zen-assistiveText" style="width:0px;height:0px;" id="assistive-announce">Login</div><script>LoginHint.getSavedIdentities(false);</script><script src="/jslibrary/baselogin2.js"></script><script src="/jslibrary/LoginMarketingSurveyResponse.js"></script><script>function handleLogin(){document.login.un.value=document.login.username.value;document.login.width.value=screen.width;document.login.height.value=screen.height;}function lazyload(){document.getElementById("pwcapsicon").src="/img/icon/capslock_blue.png";document.getElementById("marketing").src="https://c.salesforce.com/login-messages/promos.html";}loader();</script></body></html>

[simon]

Hi Asteryx,

Look at this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" style="visibility: visible;"><head><meta name="viewport" content="initial-scale=1.0"/><meta name="description" content="Salesforce Customer Secure Login Page. Login to your Salesforce Customer Account." /><title>Login | Salesforce</title>

You're stuck at the SF login page.

Try this: copy & paste downloadData into notepad. save as 'test.html' and open it. 

I see SF header and username / password textboxes. Are you hitting the correct url? e.g.  https://login.salesforce.com/