Data migration is one of those things that seems so easy at first, but quickly devolves into a pit of time-wasting & mistake-making. It doesn’t matter how many edge-cases you account for, there still seems to be a few hundred rows of data that undermine the whole import. I have a workflow that doesn’t involve costly SAAS tools, let me explain:
Disclaimer: this requires a developer. Data cleanup & migration usually revolves around the humble spreadsheet; Spreadsheets are simple and almost anyone can read and edit them. This approach starts by:
- Importing your source CSV file into Google Sheets.
- Next, share the sheet with anyone involved in the cleanup effort for easy collaboration.
- Change column names so that they match the data better.
- For example in product data: “ITM_NMBR” becomes “sku”
- Utilize ‘Conditional Formatting’ (Format > Conditional formatting) to highlight cells where values aren’t acceptable.
- For example, a price column could use a rule like “Greater than 0”.
- Utilize ‘mapping columns’ with formulas that calculate destination values from source values.
- For example, create a new column called ‘is visible’ containing a formula: ‘=IF(B2 > 0, “true”, “false”)’
- Attach an Apps Script to the sheet.
- Within the Apps Script, create a menu button that performs data import via API.
- For example, the script can read the selected rows, make authenticated calls to the destination API using the row data and write the results to the row (such as new ID, import status, etc…)
- Import data in batches using scripted menu button.
- When errors are encountered, start again at step 3 and iterate until the import is perfect.
There isn’t a “silver bullet” for data migration. Only a truly iterative approach can make it seem less maddening and help you deliver on time.