Automated Data Onboarding: Set Up the Input
Welcome back to our blog series on automated data onboarding / list loading. If you missed the introduction, you can find it here.
Step 1: Set Up an Input File & a Shared Folder
The first step in automating list loading is to set up how data enters the process. You need a standard file format, a shared folder, and a master template.
A Standard File Format
Choose from CSV, Microsoft Excel, or Google Sheets as a standard file format. The decision is largely a matter of organizational or personal preferences. We recommend Google Sheets for two reasons:
- It’s easy to turn CSV and Excel documents into a Google Sheets document. Just drop the original file into Google Drive, and it takes one click to open and convert the file to a Sheets file.
- Google Sheets handles non-English text very well, whereas exporting Excel to CSV can easily ruin non-English text if the Excel configuration is not correct.
A Shared Folder
Set up a shared file repository where the team can drop the files to be loaded. This can be any of the popular file-sharing services such as Box, Dropbox, Google Drive, or Microsoft OneDrive. FTP server is a more secure, but less convenient option.
A Master Template
Once you have picked a file format and a place to deposit the files to be loaded, you need to design a master template with a standard set of data fields / spreadsheet columns that the automated process can expect. This template should include a superset of the usual suspect of data fields you may want to import, including but not limited to: First Name, Last Name, Email, Company Name, Street, City, State/Province, Postal Code, Country, Phone Number, Job Title, etc. Make sure this template is as comprehensive as possible to deal with the typical fields you get from third party data providers.
Depending on the technology you use to automate the import, you need to keep either the column position or the column header label consistent across the files you load. If you’re using Openprise, the column position in the spreadsheet must be consistent. The column header labels can vary from file to file.
The One Remaining Manual Step
Once you have all the input set up, the only manual steps remaining are to:
- Change the raw file into the master template format. This means:
- Moving the data columns around so the file conforms to the master template
- Adjusting any non-compliant input fields to the master format, e.g., break full name into separate first name and last name columns
- Put the file to be loaded in the folder in the right format
Next time, we’ll discuss the types of tasks you should consider including in the automated process.