(basic) python scripts to help you with data entry stuff…

Very very basic scripts!

When you are the accidental admin of a Salesforce instance many times you have to bring data from other systems. Reality is that automatic integration in place is beyond your skills and also you do not have access to the other API systems(security/restrictions). Also the awful reality is that many times your end users like to “create a spreadsheet!”, “here is the document, yes, a Word document and the data are in these tables”, and sometimes they think that a good way to manage all their appointments and who participated what is in the email calendar…

Reality is reality, and still you will have to pull this data into your Salesforce ORG. To be practical, imagine this situation:

• Meet Sheila: your top sales person! She have been crushing her quota the last 3 quarters and because she has an entrepreneurship style, she invested part of her Q4 time in generating leads for the next fiscal!
• Sheila knows that a healthy pipeline is her secret sauce to crushing her quota! Therefore she put a marketing hat and organized several meetups to promote the products of the company! Created a Gcal appointment, invite all her contacts and there she went!
• Approaching end of Q4 Sheila asks you to have all this data into Salesforce, so she can work on this leads, and “Oh, I would love to have some report to show to Sciberra, like the ones you do on customer support, so I can show why these activities are so important! I know that with Salesforce I can convert these leads into opportunities and close faster”
• You are looking at the perspective of manually data entry the 600+ contacts and asking yourself “Why Sheila didn’t use Salesforce first place to plan all these?”

Stop there! Stop there! It will be time to get to the important stuff, but Q4 is ending and Q1 approaching, and you have to ship all the products that Sheila already sold! You decide to leave for next coffee with Sheila how to do all the process in Salesforce, streamlining everything, and maybe going back to Sciberras with a good business plan to hire a marketing person, but not for doing data entry!

How to quickly data entry all this data? Thanks to Automate the Boring Stuff with Python you can quickly put some scripts to exactly do this: get the data of guests from a Gcal appointment into a nicely formatted csv file that you can upload using dataloader.io in Salesforce. Knowing that the perfection is the enemy of getting things done, you write some code that’s flexible enough to format different csv files that can be uploaded to different objects (Leads, Contacts, Campaign Members):

• You start copying and pasting the contacts from the Gcal into a flat file:

You get all the guests in a single line, and the pattern looks like this:

Aluino Kellington <akellingtona@accuweather.com>, aliddyardc@smugmug.com, Arlyn Wolfit <awolfitd@etsy.com>, brault1@utexas.edu,...

• And the desired output is this one:

FirstName, LastName, email, CampaigID, Status
Aluino,Kellington,akellingtona@accuweather.com,701F0A83FDE68A603A0,Attended
aliddyardc,aliddyardc,aliddyardc@smugmug.com,701F0A83FDE68A603A0,Attended
Arlyn,Wolfit,awolfitd@etsy.com,701F0A83FDE68A603A0,Attended
brault1,brault1,brault1@utexas.edu,701F0A83FDE68A603A0,Attended

Let’s split the process into smaller processes, one at a time. The first one is to get each Gcal guest into a different line:

>python gcal2csv.py gcalContacts.csv gcalContacts.1.csv

Then make sure that we start having a proper formatted csv file, with commas as separators:

>python csv2columns.py gcalContacts.1.csv gcalContacts.2.csv

Some emails are enclosed in “<” and “>”, so get’s rid of them:

>python cleanEmail.py gcalContacts.2.csv gcalContacts.3.csv

If the contact is in your Contact List in Gmail, you get the First Name and Last Name, but if not, you get the email of the guest (not enclosed in “<>”), so make sure that we fix that quickly. Let’s get the first part of the email and use it as First Name and Last Name:

>python addCommas.py gcalContacts.3.csv gcalContacts.4.csv

This file is already good to insert into the Lead or Contact object and also you would like to have data to insert into the CampaignMembers object. You need to add columns for the Account Name, the CampaignID and the CampaignMember.Status:

>python csvAddStringLine.py gcalContacts.4.csv gcalContacts.5.csv ",Prospects,701F0A83FDE68A603A0,Attended"

The code starting 701F0A83FDE68A603A0 is the CampaignID from an existing campaign in your Salesforce ORG, which is better to use than the Campaign Name. “Prospects” is a bucket Account Name you just created to put any contact you do not know the Account. It is clear that you need to work more with Sheila to get all the data you need, and this is a quick shortcut to add the contacts in your Salesforce ORG (this assumes that you are not using Person Accounts).

The final step before uploading is to add a header line:
>python csvAddHeader.py gcalContacts.5.csv gcalContacts.6.csv "FirstName,LastName,email,AccountName,CampaignID,Status"

And your final file, gcalContacts.6.csv looks like:

FirstName,LastName,email,AccountName,CampaignID,Status
Aluino,Kellington,akellingtona@accuweather.com,Prospects,701F0A83FDE68A603A0,Attended
aliddyardc,aliddyardc,aliddyardc@smugmug.com,Prospects,701F0A83FDE68A603A0,Attended
Arlyn,Wolfit,awolfitd@etsy.com,Prospects,701F0A83FDE68A603A0,Attended
brault1,brault1,brault1@utexas.edu,Prospects,701F0A83FDE68A603A0,Attended

Voila!

Now using dataloader.io you can easily add this to your Salesforce ORG. You already know that these contacts do not exists, so first upload the content of gcalContacts.6.csv into Contact or Lead and then as CampaignMember:

Of course this is not the ideal process, neither these python scripts handle all the options, other formats, and they are not elegant code at all! There will be time for all this…

• The code for each python script is here! Feel free to download, drop all them in one folder. And also you can go in, and make one script of all them, or look and improve them. They are very basic scripts, just anything that a bad programmer like myself can code quickly looking at example code, and my experience coding a long long time ago…
• One way is to explore regular expressions, and of course being more powerful in the capabilities of the initial files that you can transform.
• You can see by the code that your final file will be ok, if everybody in your contact list has First Name and Last Name, this is a simplification, and there is room to use better coding to handle several different format names into the First Name, Middle Name and Last Name that Salesforce supports out-of-the-box
• Salesforce reference for Lead and CampaignMember
• In Trailhead module Data Management you can start your journey on how to import/export data in Salesforce. And for practice complete this Trailhead project Import and Export with Data Management Tools
• I like to use dataloader.io (there is a freeware version) because it handles pretty well inserting using lookups (and parent child relationships). So as you have seen in the videos you can use the Account Name, and then select in dataloader.io to find the Account ID using Account Name. Same with ContactID when importing Contacts as CampaignMembers, in this case the search has been selected using email
• This useful feature in dataloader.io (not present in Salesforce Data Loader) saves a lot of time on finding the right ID, and adding it to your csv file to use in another object. Using Salesforce Data Loader (or Salesforce DX commands) you insert the contacts first, then export them again to get the right contact ID, use this information to create a new csv file with the Contact ID… cumbersome…
• The value 701F0A83FDE68A603A0 corresponds to the Unique ID of a campaign in your Salesforce ORG: you can copy easily from the URL of the campaign detail record
• I mockup the campaign ID using Mockaroo: selecting MD5 as type and then using this formula: ‘701’+upper(this)[0..15]

creating a mockup campaign ID with Mockaroo

• Same for creating the Names and emails for the example used here! (not real names or emails have been used in this article!)