Skip to content
demuddler

Clean a file

Open a file, map columns and correct values in the browser. Export the reviewed rows as CSV or Excel.

In the demo, you can open and correct a spreadsheet without an account. The file stays in your browser. An embedded importer works in the same way.

Drag a file onto the importer, or select Choose a file. To paste a range from a spreadsheet, copy the cells, then press Ctrl+V or Cmd+V on the start screen.

The importer opens CSV, TSV and Excel (.xlsx and .xls) files. The memory of your browser limits the file size. Free and Starter limit the rows in an export, not the rows that you can open. See Plans and limits.

The importer detects how the file is written:

  • The separator, the quotation mark and the header row
  • Whether a backslash escapes a quotation mark, as in "say \"hi\""
  • The character encoding
  • In an Excel file, the first visible sheet that has data
  • A row of categories above the column names. A message tells you to set Header rows in the file settings, so that these rows join into one header.
  • The type of each column, the date order and the number format

If the values do not show the date order or the number format, the column menu marks Date order or Decimal comma as unclear. Choose the correct value there.

To change how the importer reads the file, select the file name in the header. It opens File settings, which holds these settings:

  • CSV: the encoding, the separator, the quotation mark, whether a backslash marks a quotation mark, the header row, the number of header rows, the rows to skip above the header, and extra words that mean an empty cell
  • Excel: the sheet, the header row, the number of header rows, the rows to skip, and whether merged cells fill every cell that they cover

Apply reads the file again. The importer detects each setting that you did not change again.

The table marks each cell that has a problem. Point to a cell to read the problem. The row overview beside the table shows red marks for rows with problems. Select a mark to go to those rows.

The sidebar on the left of the table has the Fixes, Problems, History and Repeated rows tabs. Problems lists each problem with its rows. To show only some rows, choose Filter this column in a column menu. On a screen narrower than 1,024 pixels, the sidebar opens as a sheet from the button above the table.

To change the Type, Date order or Decimal comma of a column, open the column menu from the column name. The importer then reads the file again, so you must undo your edits first.

The importer suggests fixes from the values of each column. This works also for a file without a schema. The fixes for a column with problems show on its card in Problems. Every fix shows in Fixes. In the column menu, Suggested fix: and the name of the fix opens it. Each fix changes one column:

  • Remove extra spaces and Remove repeated spaces remove spaces at the ends of a value and repeated spaces between words.
  • Remove spaces from email addresses removes each space in an address. A value that is not an address after this, such as Jane Doe, does not change.
  • Write email addresses in lower case changes Ann@Example.com to ann@example.com.
  • Use one spelling changes values that differ only in capital letters or spaces, such as new york and NEW YORK, to their most common spelling. Spaces alone are left to Remove repeated spaces.
  • Fix rare spellings changes a rare value that is one letter away from a common value, such as Pendng, to the common value. It works in a column with a short list of values, such as a status.
  • Standardise date values writes dates as 2024-03-25. If the values do not show the date order, choose Day / month / year or Month / day / year.
  • Standardise numbers removes currency signs and thousands separators, and writes a decimal point. A percent sign stays.
  • Standardise true and false values writes true or false for answers such as Y, no and TRUE.
  • Standardise country codes writes country names as two-letter codes.
  • Write phone numbers in one format writes a number with its country code, such as +44 (0)20 7946-0958 or 0044 20 7946 0958, as +442079460958. A number with no country code does not change.
  • Fill blank cells from above gives each blank cell the value above it, as merged cells and grouped rows need.
  • Match an allowed value and Fix a likely spelling mistake change values to the allowed values of your schema.

Select Preview to check the fix on every row of the column, also rows that filters hide. The preview shows the number of values to change, examples before and after, the problems that it fixes and the new problems. The match labels, such as Strong match, describe the evidence. They are not probabilities.

Values left unchanged counts the values that the fix cannot change, such as n/a pending in a column of numbers. Select Show these rows to see them, then correct them by hand.

Apply changes the whole column in one step. A fix cannot apply when it causes new problems, or when the importer cannot check some rules. If the preview needs too much memory, it stops and changes no value. Rows that you left out stay out. Undo restores every value that the fix changed.

Transform in the column menu holds these tools:

  • Trim spaces, Uppercase, Lowercase and Title case apply at once.
  • Fill blank cells from above gives each blank cell the value above it. Blank cells above the first value stay blank.
  • Merge columns adds the values of the Column to add to the end of this column, with the Separator between them. The other column does not change. A row where it is blank does not change.
  • Change values lists up to 500 values of the column, with the number of rows for each. Type a New value for each value to change. A value matches only as a whole, with the same capital letters.
  • Standardise country codes writes country names, such as Deutschland, as codes, such as DE.
  • Standardise date values writes dates as 2024-03-25. If the importer can read the values in two ways, choose Year-month-day, Day-month-year or Month-day-year first.
  • Standardise true and false values writes true or false.

Each of the other tools shows a preview, as a fix in Fixes does. Select Preview or press Enter, then Apply.

To edit one value, double-click its cell. Split column… splits a column at a separator. Undo restores each change.

Replace text… in the column menu replaces text in every row of the column, also rows that filters hide.

  1. Type the text in Find.
  2. Type the new text in Replace with. To remove the text, leave it empty.
  3. To match capital letters exactly, turn on Match case.
  4. To replace only values that are exactly the text, turn on Match the whole value.
  5. Select Preview or press Enter, then Apply.

To replace text in some cells only, select them and press Ctrl+H or Cmd+H.

When a file opens without a schema, as in the demo, the importer makes one field from each column. To set the rules of a field, select Rules… in the column menu. Rules check values. They do not change values.

  • Every row needs a value refuses empty cells.
  • Each value appears only once refuses repeated values.
  • Format checks for an email address, a web address, a phone number or a country.
  • Allowed values takes one value on each line. Capital letters do not matter. Add values from this column lists the values of the column, so that you can select them. Add values from a file adds the first column of a CSV file, or each line of a text file. The file stays in your browser.
  • Number range sets a minimum, a maximum or both.
  • Number of characters sets the fewest, the most or both.
  • Pattern takes a regular expression. As you type, the importer tests it on the first 10,000 rows and shows values that do not match. You cannot save a pattern that is not valid.
  • Compare with another field compares dates or numbers, for example an end date at or after a start date.
  • Required when another field has a value requires a value when another field has a given value, for example a card number when the plan is paid.

Save sets a rule, and Remove deletes it. The problem counts change at once. Undo also restores a rule change.

When the importer reads the file again, or you split a column, it makes the fields again. Each column keeps its rules, matched by column name. A type that you chose stays while the column reads as before. If a column is no longer in the file, the importer removes its rules and the rules that compare with it. A message names those columns.

In an embedded importer, your application sets the schema and the rules, so the column menu has no Rules…. See Schema.

Select Continue in Fix values to open the Import step. Choose CSV or Excel. The export contains your changes. Leaving rows out is optional. The export controls show the number of rows to export.

Repeated rows finds rows that repeat another row:

  1. Choose Same values in every column, or choose one column.
  2. To compare more columns, select Add a column to compare.
  3. Choose Keep the first copy or Keep the last copy.
  4. To leave the other copies out of the export, turn on Leave out repeated rows.

A row repeats another when every compared column has the same value. Capital letters from A to Z and spaces at the ends of a value do not matter.

If numbers have percent signs, Keep percent signs sets whether a CSV file writes 12.5% or 12.5.

Demuddler adds no mark, column or row to the exported data.

When you add a file to an open file, columns match by name. The importer reports a column that one file does not have. See Combined files lose a column.

Fix file problems explains common problems. Import messages explains each message.