Edit, remove or adapt data before exporting

Three words to know

• Column: one kind of information in your file, for example the name, the price or the description. Its name is written on the first row of the file.
• Cell: the value of a column for one product, for example the price of the blue bowl.
• Transformation: a ready-made operation, applied to every cell of a column. The Converter offers about forty of them, each with its Google Sheets equivalent for those who know spreadsheet formulas.

Two places to make changes

Everything happens at the Mapping step (the third step, at the top of the screen), in two different places:

• Edit the data works on the columns of your file, before the conversion. The column names are those of your file. This only works for a CSV file.
• Edit the output file works on the columns of the file that will be imported into your store, after the conversion. The column names are those of the target platform, for example Title or Vendor for Shopify. This works for every file.

Add a transformation, step by step

The case followed on this page: Atelier Démo, a fictitious store, has received a ceramics supplier’s catalogue. The prices are written in cents (2200 for €22.00), the names look like Bol | Grès | Bleu (French for bowl, stoneware, blue), and the descriptions contain HTML code.

1. At the Mapping step, scroll down the page to the Edit the data box. It is below the table of columns, just above the Preview of converted rows box.
2. At the bottom of this box, click the Add a transformation button, the one that starts with a “+”. A window opens on top of the screen.

1. In the first list of the window, What to do?, choose what you want to do. At the top of the list, the Quick recipes do a common job in a single choice. Below them, the transformations are sorted into five groups: Text, Cut, add, insert, Numbers and prices, URL, slug, HTML and Columns. A grey sentence under the list explains the chosen transformation.
2. In Which column?, choose the column to transform. For the first four groups, the All columns choice applies the transformation to every column at once.
3. Fill in the fields that appear below: they are the settings of the transformation, for example the text to look for. The Google Sheets equivalent: … line shows the spreadsheet formula that would do the same thing.
4. Look at the Preview (before → after) table, at the bottom of the window: on the left, the Before column shows the first cells as they are now; on the right, After shows what they will become. If you do not like the result, change the settings: the preview updates by itself.
5. Scroll the window to the very bottom, then click the orange Add button, at the bottom right, next to Cancel. The window closes, the message “Transformation added.” appears, and the transformation shows up in the box with a number.

The transformations apply in the order of their numbers, each one on the result of the one before. A new transformation always goes at the end. To change the order, remove it with Remove, on the right of its row, then add it again. Remove all empties the whole list.

Careful: if a transformation renames or deletes a column you had set with Edit in the table of columns, that setting disappears with it. Take a look at the table afterwards.

Common cases

Each case gives the problem, what you choose in the window, and the result you get. The examples were run through the Converter’s engine: the results are exact.

Prices written in cents

The problem: the store would show €2,200 instead of €22.00. The Converter often notices this by itself: a yellow banner appears in the Edit the data box, for example “The “Prix” column looks like prices in cents (2200 → 22.00). Divide them by 100?”. Click its Divide by 100 button: 2200 becomes 22 and 1450 becomes 14.5. The final zero is not written, but it is the same price.

The banner only appears when the Converter is almost certain. All of the following must be true:

• the column’s name starts with “Prix”, “Price”, “Preis”, “Prijs”, “Precio”, “Prezzo”, “Preu”, “Tarif” or “PVP”, alone or followed by a space, a _, a -, a . or a bracket, and it contains neither “unit”, nor “kg”, nor “litre” or “liter”, nor the words “per” or “par” (a price per kilo does not count);
• the column has at least five filled cells, all of them whole numbers of three to six digits;
• the middle value, when you sort the prices from lowest to highest, is at least 300, and at least half of the prices do not end in 00.

Without a banner, choose the Prices in cents → euros recipe: it does the same thing. To round afterwards, add Arithmetic on a number, with Round to N decimals in Operation and 1 in Value: 34.75 becomes 34.8.

Titles with “|” in them

• Keep every word, without the “|”: the Remove the “|” from a title recipe. Bol | Grès | Bleu becomes Bol Grès Bleu.
• Keep a single part: Keep the Nth part. Type | in Separator and the number of the part in Part number: 1 keeps the first part (Bol), -1 the last one (Bleu).
• Remove a repeated part: the Remove repeated parts and words recipe. Vase | Grès | Bleu | Grès becomes Vase | Grès | Bleu.

Create a clean slug from the name

The slug is the end of a product page’s address: in lower case, without accents, with hyphens instead of spaces, for example bol-gres-bleu. Choose the Create a clean slug from this column recipe, then the name column. The recipe copies the column under a new name, the column’s name followed by _slug (here Nom_slug), then transforms the copy: Bol | Grès | Bleu becomes bol-gres-bleu. The name column does not change. Next, in the table of columns, look at which platform field the new column goes to, and change it with Edit if needed.

HTML code in a description

HTML is made of tags between < and > that format the text: <p> for a paragraph, <ul> and <li> for a list, <strong> for bold. Add Clean up the code on the description column, then choose its Mode:

• Text only removes all the tags and keeps the text. A list <ul><li>Grès</li><li>30 cl</li></ul> becomes two lines: “• Grès” and “• 30 cl”.
• Clean HTML keeps the tags that structure the text (paragraphs, line breaks, lists, bold, italics, subheadings, tables) and removes the others, keeping the text inside them. It is the right choice for a column that stays in HTML in the store, like Shopify’s Body (HTML): this kind of column does not show the line breaks of plain text.

Join two columns

• Join several columns creates a new column. Tick the columns to join, choose the separator (a space by default) and the name of the new column. The columns are joined in the order they appear in the file, not in the order you tick them: with Nom and Marque (the name and the brand), this gives Bol | Grès | Bleu Atelier Démo, because the name column comes before the brand column.
• To choose the order yourself, use Compose from other columns: it writes, in the chosen column, a text you put together. On the Nom column, {Marque} - {this} puts the brand, a dash, then the current content of the cell: Atelier Démo - Bol | Grès | Bleu. The names between curly brackets must be written exactly like the column names.

Keep the last level of a category

To keep Bols from Cuisine > Bols, use Keep the Nth part with > as the separator and -1 as the number: the part kept loses the spaces around it. Keep what comes after a text works too, but it keeps the space that follows the > ( Bols): in that case, add Clean spaces just after it.

Change only some rows

Below the fields of the transformation, the Only for rows where line limits the transformation to the rows that meet a condition. It has three parts: a column ((this column) by default), a comparison, and a text. The possible comparisons are contains, is exactly, starts with, ends with, is empty and is not empty. Capital letters do not matter: déco and Déco are the same.

Example: you want to write Atelier Démo Déco as the brand, only for decoration items. Choose Fill with a value on the Marque column, type Atelier Démo Déco in Value, then set the condition: column Catégorie, starts with, déco. Only the vase changes. The condition exists for the first four groups, but not for the recipes or the Columns group.

Remove or rename a column

Delete the column removes the column before the conversion: nothing of its content reaches the exported file. Rename the column only changes its name. This is useful when the original name does not say what the column contains: with a clear name, the Converter recognises it better.

Touch up the file that will be imported

Scroll down to the Preview of converted rows box. Just above its table, the Edit the output file box has its own button, Edit the output. It opens the same window, but with the columns of the target platform.

Example for Shopify: some products have no brand, and their Vendor column is empty. Choose Fill empty cells on the Vendor column, type Atelier Démo in Value, then set the condition: column Title, is not empty. Why this condition? In a Shopify file, each variant of a product has its own row, and only the first row of the product carries the title. With this condition, the brand is filled in on the rows of the products, not on the rows of the variants.

Fix a single cell

In the Preview of converted rows table, click a cell, type the new value, then press Enter. If you made a mistake, press Esc to get the old value back. The fix is added to the list of the Edit the output file box under the name Cell edited by hand, and the Remove button on its row cancels it.

Keep your transformations for the next file

Transformations apply to the current conversion. To replay them in one go on the next file, save a template with the Save as template button, at the top of the Mapping step. A template also keeps the changes to the data and to the output file, and where your custom fields go. The number on a card only counts the saved mappings. Everything is explained in Mapping templates.

Check

1. Look at the Preview of converted rows table and the First row as Shopify will receive it box: they already show the result of your transformations, like the file that will be exported.
2. Click Continue to review, at the bottom right. The Review step tells you whether a problem still blocks the export.
3. To see every transformation, what it asks for and its spreadsheet equivalent: All the transformation formulas.