This project was my most recent large project at Marine Travelift. It was also my first project there with a very strict due date, which wasn't a problem, but the project took longer than it should've. Most of the reasons why weren't even my fault either.
History
Very recently, Marine Travelift bought out one of our vendors in order to open a new customer service branch in Florida. Partially because of that and partially because it would be a useful tool for all of Customer Care, we needed to implement Praxedo. Praxedo is a tool for the scheduling and management of our service techs based on customer data.
Moving our data over couldn't just be a one-time transfer, though, since customer data is always changing in Salesforce. Therefore, I needed to build an app that harnessed both the Salesforce and Praxedo APIs. Also, I needed to do a one-time import of sites and generate a CSV file with assets for the Praxedo employee to import (since the REST version of their API didn't exist for assets yet). The reason why these were one-time is that the Salesforce data is a mess at this point.
Experience
Code
Originally, it was supposed to be a simple app that directly transferred the data from Salesforce to Praxedo. There isn't too much data, but it didn't matter how long it would run for anyways since it would just automatically run in the background. However, the Praxedo API has minutely and hourly rate limits that I believe to be too small (PUT 80 customers a minute).
I couldn't just check the data against what was already in Praxedo and only update what I needed to either because GET also has limits. Therefore, the process I built checks each record against SQL, either inserts to or updates the SQL table, and only inserts to Praxedo if needed. Because SQL and Praxedo would match by being updated at the same time, I only need to check against SQL.
Data
Customer Care wants to make sites and assets automatic as well, but that would be basically impossible until the data is cleaned up. Salesforce is largely freeform, so you might run into inconsistencies like an address in the model number field, an extra comma in an address, or no address at all. I had to dump all the assets into a spreadsheet and manually remove anything missing addresses. Something that continually broke my data transfer was the presence of almost every single spacing-related escape character. I'm not sure if the Salesforce address uses a rich text box or something, but I just replaced them all with spaces.
In order to create custom IDs for sites, I used the first few chars of the address, which ended up using invalid characters (you can't put '/' in a URL for example), so I had to remake the assets file a few times. One major problem is countries. For some reason, those are also freeform instead of being selectable via dropdown. Praxedo requires 2 character country codes, but I couldn't even do checks in my code and convert them there since I would have no idea how somebody typed it in. Thankfully, customers don't really need a country assigned to them, and sites were manual so I was able to change countries to country codes by hand in that file.
Side story: I copied over some data from one of the Excel files to another one time, and instead of accepting the data like it always had and always should, Excel decided to throw an infinite number of rows in the file which capped memory and froze the application.