Preparing Data for Migration to a New System

You are migrating from System A to System B. You have an export from the old system, a CSV or Excel file with thousands of rows. System B has an import template, and it expects columns in a specific order, with specific names, and specific types. Your export does not match.

You could do this in Excel with a lot of manual column dragging and formula writing. Or you could script it in Python. But if you want something faster and visual, you can build a transformation pipeline in ExploreMyData that reshapes the data to match the target schema. Here is the workflow.

A concrete example

Say you are migrating a customer database. The old system exports these columns:

cust_id, fname, lname, email_addr, phone, addr_line1, addr_line2, city, st, zip, created, status

The new system expects:

customer_id, full_name, email, phone_number, address, city, state, postal_code, created_date, is_active

The mismatches: different column names, different column order, fname and lname need to be merged into full_name, addr_line1 and addr_line2 need to be combined, status is a text field ("active"/"inactive") but the new system expects a boolean is_active, and created is a string that needs to be a proper DATE type. There is one more, and it is the one that bites hardest: zip holds New England zip codes with a leading zero, and the target wants those as text.

Step 1: Select and reorder columns

Click the green + in the Pipeline panel and select Select Columns from the Columns group. Pick the columns you need and arrange them in the order the target system expects. Drop any columns the new system does not need.

In our example, select: cust_id, fname, lname, email_addr, phone, addr_line1, addr_line2, city, st, zip, created, status. Drag them into the target order.

Step 2: Rename columns to match the target

Click the green + in the Pipeline panel and select Rename Columns from the Columns group. It takes as many renames as you like in one step and leaves every other column untouched, so there is nothing to drop afterwards.

Map the names:

• cust_id → customer_id
• email_addr → email
• phone → phone_number
• st → state
• zip → postal_code
• created → created_date

The step produces the SQL "column" AS "new_name" for each pair in the generated query. Everything downstream refers to the new names, so from here on it is postal_code and created_date, never zip and created. fname, lname, addr_line1, addr_line2 and status keep their old names for now, because they get consumed by later steps rather than renamed.

Step 3: Convert types

Click the green + in the Pipeline panel and select Convert Type from the Transform group. "Convert to" offers three options: text, numeric, date.

Convert created_date to date. Auto-detect date format is on by default and handles 04/15/2023 fine; if your source uses something stranger, fill in "Source date format" yourself. Either way ExploreMyData wraps the cast in TRY_CAST(), so a malformed date becomes NULL instead of taking down the whole step. Scan the column for NULLs afterwards, because the target treats created_date as required.

For status to is_active, use Add Column from the Columns group. Name it is_active and give it the expression "status" = 'active'. Leave the optional output type unset: the comparison already returns a boolean, and the three types on offer (text, numeric, date) would only get in the way.

Leading zeros: fix them at import, not here

Cara's zip is 02134. If the file came in with type detection doing its normal job, that column was read as a number and the value in memory is 2134. The leading zero is not hiding behind a display format. It is gone.

Converting the column back to text will not bring it back. 2134 casts to the string "2134" and you have quietly shipped a broken zip code into the new system. This is worth being blunt about, because a text column full of four-digit zips looks correct at a glance.

The fix belongs at import time. In the file list, click the gear icon (Configure CSV parsing) next to the file. In the Configure CSV Parsing dialog, open Type Detection and tick Load all columns as text. Reload the file. Now 02134 arrives as the five characters it actually is, and you cast the columns you genuinely want as numbers with Convert Type afterwards.

If loading everything as text is too heavy-handed, the same Type Detection panel has "Column names" and "Column types", so you can pin zip to text and let the rest auto-detect. Note that the gear is only offered for .csv, .tsv and .txt files, so if your export arrives as Excel or PDF, get it out as CSV first.

Step 4: Combine split fields

The old system stores first and last name separately. The new system wants a single full_name field. Click the green + in the Pipeline panel and select Combine Columns from the Columns group.

This panel does not look like most of the others. There is no multi-select column list and no separator field. Instead there is one control, Define values or columns to combine, which is a chip input. You build the output left to right by dropping in chips: pick a column from the dropdown to add a column chip, or type literal text and press enter to add a text chip. The separator is just another chip.

So for full_name you add three chips in this order:

1. the fname column chip
2. a literal chip containing a single space
3. the lname column chip

Then set "Apply results into" to a new column and name it full_name. Leave the name blank and you get a column called combined, which is rarely what you want.

Do the same for the address: the addr_line1 chip, a literal , chip, the addr_line2 chip, into a new column named address.

Every column chip is wrapped in COALESCE(CAST("col" AS VARCHAR), '') before the whole thing goes into CONCAT(), so a NULL becomes an empty string and you never get "123 Main St, null" in the output. The flip side: a row with no second address line ends up as "123 Main St, " with a dangling comma, and because the result is an empty string rather than NULL, Fill Missing will not help you clean it up. Use Find & Replace for that.

Step 5: Split combined fields (when needed)

Sometimes the migration goes the other direction. The source system has a combined location field ("Austin, TX") but the target wants separate city and state columns.

Click the green + in the Pipeline panel and select Split Column from the Columns group:

1. Select the column to split.
2. Set the delimiter (e.g., , for comma-space).
3. Set the number of parts to 2.

The result creates location_1 and location_2. You can then use Rename Columns to rename them to city and state.

Final cleanup and export

Add one more Select Columns step at the end to pick only the ten target columns, in the exact order the target system expects. This is where fname, lname, addr_line1, addr_line2 and status finally leave: they did their job feeding full_name, address and is_active, and the target template will choke on anything it does not recognise.

Export the result as CSV or Parquet. The file is now ready to import into System B without any further transformation.

The full pipeline

Before any of these, at import: the parsing gear, Type Detection, columns loaded as text so the zip codes survive. Then:

1. Select Columns - pick and reorder to approximate target schema
2. Rename Columns - six renames to target names, all in one step
3. Convert Type - cast created_date to a real date
4. Add Column - derive the boolean is_active from status
5. Combine Columns - fname + space + lname into full_name
6. Combine Columns - addr_line1 + ", " + addr_line2 into address
7. Split Column - break apart any combined source fields
8. Select Columns - final column selection in exact target order

Eight cards, and the one that saves you the most grief is the checkbox you ticked before the first card existed.

Next week's export gets the same treatment for free. Load the file, the pipeline replays over it, and you are done before you have finished reading the migration ticket.

Start preparing your migration →