501commons / salesforce-importer

Data Import Automation is for anyone that is using the Salesforce Data Import Wizard or Data Loader on a regular basis to get the same set of external data uploaded to Salesforce. If you have ever saved a field map file (sdl file) from Data Loader then automation is probably a good option. Common tasks that are ideal for Data Import Automation are donation records coming from periodic financial reports, business cards/contacts from marketing events, external databases not integrated into Salesforce, or any other data that has the same fields you are regularly uploading. There is an investment in setting up the Data Import Automation that takes a couple of hours but once you have it setup the end user experience is Copy external data sources (e.g., csv, excel files) to a specific location Run the import automation program Review import emails (takes about 10-15 minutes for import to finish) which have a log of the import process and the Salesforce Data Loader success and error files are attached. 501 Commons Salesforce Importer Open Source project on GitHub https://github.com/501Commons/Salesforce-Importer The technology used is Salesforce Data Loader Command Line Microsoft Excel Python The bulk of the setup work is building the Microsoft Excel file by adding New Data Queries for your external sources & Salesforce Object query. Then using Excel Power Query merge data between your external data sources and Salesforce objects so that you have a list of data to insert (not already in Salesforce) and data to update. The project is open source so feel free to suggest and make contributions. Built and tested on Windows but no reason shouldn't work on Mac OS.

Data Loader https://help.salesforce.com/articleView?id=data_loader.htm

Importer Setup Instructions

1. Install Git for Windows -> http://gitforwindows.org NOTE: Don't need to have an account just need the application installed

2. Install Python 2.7.x for Windows -> https://www.python.org/downloads/release/python-2716/

After install verify there is a C:\Python27 directory

3. 501 Admin will provider zip file for custom settings ([Client].zip). Extract zip into C:\repo. After unzip there should be a c:\repo\Salesforce-Importer-Private directory

Example: C:\repo\Salesforce-Importer-Private\Clients\XYZ where XYZ are the ClientInitials should contain an Importer.bat file and a DataLoader directory.

4. Edit c:\repo\Importer-Private\Importer.bat Check & Verify the following values - update accordingly
   • EMAIL_LIST - include client emails
   • IMPORT_DIRECTORY - Location of incoming data files (e.g., DropBox, OneDrive)

Running Import

Run c:\repo\Salesforce-Importer-Private\importer.bat to start the importer. You can run - importer.bat manually - schedule with Task Scheduler (be sure to set working directory to the importer.bat directory)

Troubleshooting

1. Excel gives an error when trying to authenticate with Salesforce Resolution: Enable Excel to use TLS 1.2 https://social.technet.microsoft.com/Forums/en-US/92811d44-1165-4da2-96e7-20dc99bdf718/can-power-query-be-updated-to-use-tls-version-12?forum=powerquery

2. Importer process keeps popping up Privacy Levels dialog. Resolution: Checking ignore doesn't always stop further prompting so set to Public if you keep getting prompted. Another option is open the Excel file in C:\repo\Salesforce-Importer-Private\Clients[Client]\Salesforce-Importer\Clients[Client] and set the Privacy levels then save the Excel file.

3. Salesforce Data Loader can't install Admin version to C:\Program Files (x86)\salesforce.com Resolution: You can install on another machine where you are an administrator and then just copy the salesforce.com directory to C:\Program Files (x86) to your target machine.

4. Running importer and getting an error in the console window that says, "Unlink of file '[excelfile]].xlsx' failed. Should I try again? (y/n)" Resolution: Importer did not properly close the previous Excel session(s) - restarting your computer will solve the problem.

5. Importer Email says - Error Import and within the email there is a 'java.lang.RuntimeException: java.lang.NullPointerException' Resolution: This is probably related to empty columns in the generated CSV file. Open your Excel file and save out each sheet then edit the CSV files in Notepad. If you see a bunch of ,,,,,,,,,, in the header columns then that is the issue. To fix open Excel and select all the empty columns after your last data column and delete the columns. The other method is delete the sheet and then right click on your Data source and Load to... a new sheet.

6. Using Excel 2013 or an Error in Excel Saying unable to find Data Source? Resolution: Install the latest Power Query Add-in from Microsoft http://www.microsoft.com/en-us/download/details.aspx?id=39379&CorrelationId=3639a8ed-75d1-46e8-8910-03452c00154f

7. missing columns in pQ