Automated data onboarding: set up the input

Today we’re continuing our series on automated data onboarding and list loading, explaining how to set up the input. If you missed the introduction to data entry automation, you can find it here.

Step 1: Set Up an Input File & a Shared Folder

The first step in the automated data onboarding process 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

For your data entry setup 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:

1. 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.
2. 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 for the team to drop the files to be loaded. You can use any of the popular file-sharing services like Box, Dropbox, Google Drive, or Microsoft OneDrive. FTP server is a more secure, but less convenient option.

A Master Template

Once you’ve picked a file format and a place to deposit the files to load, you need to design a master template with a standard set of data fields and spreadsheet columns for the automated process. 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 to Set Up Automated Data Onboarding

Once you’ve set up all the input, the only manual steps remaining in the data entry setup process are to:

1. Change the raw file into the master template format. This means:
   1. Moving the data columns around so the file conforms to the master template
   2. Adjusting any non-compliant input fields to the master format. For example, breaking full name into separate first name and last name columns

1. Put the file to be loaded in the folder in the right format

Now you know what it takes to get automated data onboarding set up and ready to go. Easy, right? In the next post, we’ll cover the types of tasks you should consider including in the automated process.