Chapter 4

Transforming files

Three tools change data: Transform Data runs a file through a template and mapping, Quick Transform applies ready-made clean-ups with no setup, and the Expression Builder lets you try expressions on a sample row. Files are read in your browser and nothing is uploaded.

Transform Data

Open Transform Data and work down the three steps:

Source file. Choose a CSV, TSV or Excel file (up to 10 MB; Excel up to 5 MB). The first five rows are shown as parsed, so you can check the delimiter and header were read correctly.
Template and mapping. Both are optional. Pick a template, or leave No template (auto-detect) to detect the delimiter and header from the file itself. Pick a mapping, or leave No mapping (pass through) to keep every column under its own name.
Output. Choose CSV, TSV or JSON, and whether to write the target column names as the first line. JSON output ignores the header setting.

Then use the command bar:

Preview runs the transform and shows the first ten rows without downloading anything. Use it to check your mapping before committing.
Transform & Download applies the mapping to every row and saves the result to your downloads folder.

A few things to know about how the file is read:

Encoding. A file is read as UTF-8 when it is valid UTF-8, and as Windows-1252 (what Excel's "CSV" export uses) when it is not, so accented names stay intact. The page tells you when it used the fallback.
Header row. First row is a header is set to Auto, which treats the first row as the header unless the file shows it is data: a number or date where the rows below have numbers or dates, a blank or repeated column name, or a first row shaped exactly like every row below it. Choose No for the rare file that has no header and does not show it. A template you pick decides the header setting itself.
Missing columns. If a mapping rule names a source column that is not in the file, you get a warning listing the names.
Failed rows. A row that fails a rule is still written, with that cell blank, and counted in the error total. Check the count before you use the file.

TipIf a column comes out empty, compare its Source field in the mapping with the header in the file. With no source template, names must match the file's header exactly.

Quick Transform

Quick Transform applies common clean-ups straight to a file, with no template and no mapping. It takes CSV or TSV files up to 10 MB and 100,000 rows.

Choose the file.
From Available transforms, add the clean-ups you want. They join the Queue and run in the order listed.
Click Execute to apply the queue to every row, then Download Result. Clear Queue starts again.

Group	Available transforms
Case Conversion	UPPERCASE All Text · lowercase All Text · Title Case All Text · PascalCase Headers · camelCase Headers · snake_case Headers
Cleanup	Trim All Whitespace · Normalize Whitespace · Remove Empty Rows · Remove Duplicate Rows · Lowercase Email Addresses · Uppercase State Codes
Formatting	Format US Phone Numbers · Strip Phone Formatting · Convert Dates to ISO Format · Convert Dates to US Format · Format ZIP Codes · Strip Currency Symbols
Data Transformation	Split Full Name · Combine First + Last Name

For the full layout change (renaming, reordering, choosing an output format), use Transform Data instead.

Expression Builder

The Expression Builder evaluates an expression against a sample row you type in. It never reads a file and nothing on the page is saved, so it is a safe place to work out logic.

Under Sample row, use Add Column to create the columns your expression refers to and type a value into each.
Type the expression. Refer to a column by putting its name in braces, like {FirstName}.
Click Evaluate to see the result. Clear resets the page.

Some examples from the page:

Expression	What it does
`upper({Email})`	The email address in capitals.
`title({FirstName})`	The first name in title case.
`if({Amount} > 100, Premium, Standard)`	Premium when the amount is over 100, otherwise Standard.
`round({Amount}, 2)`	The amount rounded to two decimal places.
`year({OrderDate})`	The year of the order date.
`dateadd({OrderDate}, days, 30)`	The date 30 days after the order date.
`contains({Email}, @)`	Whether the email contains `@`.

The Functions panel on the page lists every function with its syntax.