Skip to content
demuddler

Fix file problems

Correct date formats, number formats and text encoding. Find the causes of file errors and browser memory limits.

This page lists common problems in a file and how to correct them in the importer. For the meaning of one message, see Import messages.

Some problems are not visible in the data. The importer lists them as file warnings at the top of the Problems tab, which shows from the Fix values step. Each warning gives its numbers and the next step.

A warning about rows with too few or too many values, or about values that do not fit their column type, has Show these rows. Select it to show only those rows. To show all rows again, select N rows shown above the table.

To dismiss a warning, select its close button. A dismissed warning stays hidden until you open another file. To show it again, select Show N dismissed warnings at the end of the list.

File warnings explains each warning.

03/04/2024 can mean 3 April or 4 March. A value such as 25/12/2024 shows that the dates are day-first. A month name also shows the order.

If the values do not show an order, the importer reads the column month-first. It then marks Date order as unclear in the column menu.

To change the order:

  1. Open the column menu from the column name.
  2. Choose Date order.
  3. Choose Year-month-day, Day-month-year or Month-day-year.

The importer reads the file again. The check mark shows the order in use, and detected marks the order that the importer found.

A column that mixes day-first and month-first dates stays text. To read it as dates, choose a date order. Values that do not fit that order become problems that you can correct.

A text column with dates in different forms also gets Standardise date values in Fixes. The fix writes each date as 2024-03-25 and keeps the column as text. The preview counts the values that it cannot read, and Show these rows shows them. The same fix is in Transform in the column menu, where you choose the date order.

1.234,56 and 1,234.56 can both mean 1234.56. A value such as 1,234 alone can mean 1234 or 1.234. When a column is not clear, the column menu marks Decimal comma as unclear.

The importer reads Indian digit groups. For example, 1,00,000 means 100000.

For 1.234,56, open the column menu and turn on Decimal comma. For 1,234.56, turn it off. If the column is still text, choose Type and then Decimal number or Whole number. Then look at the values that do not fit.

A text column of numbers with currency signs or separators also gets Standardise numbers in Fixes. It writes $1,234.50 as 1234.50. It does not change values such as n/a pending.

A percentage such as 12.5% has the numeric value 12.5. When numbers in the file have percent signs, the Import step shows Keep percent signs for CSV downloads. It starts on. When it is on, CSV writes 12.5%. When it is off, CSV writes the number 12.5. An Excel download stores the number 12.5 and shows 12.5%.

A column can hold Active, active and actv for one status, or Y, yes and TRUE for one answer. Fixes often suggests Use one spelling or Fix rare spellings for such a column. To choose each value yourself, open the column menu, then Transform:

  • Change values lists each value with its number of rows. For each spelling to change, type the value to use in New value.
  • Standardise true and false values writes true or false.
  • Standardise country codes writes country names as two-letter codes.

Each fix checks the complete column first. Values left unchanged counts the values that the fix cannot read. Show these rows shows them, so that you can correct them by hand.

A report export often writes a group name once, with blank cells below it. In the column menu, choose Fill blank cells from above in Transform. Each blank cell then takes the value above it.

Open the Import step. Under Repeated rows, choose the columns to compare and the copy to keep, and turn on Leave out repeated rows. See Export the results.

The importer marks cells that do not fit the column type, and you can edit them. A file warning counts these values. Its Show these rows shows only those rows. Correct the values in the table. If the column type is wrong, choose Type in the column menu instead.

A converted value can lose its formatting, such as a currency sign or trailing zeros.

If a selected value cannot convert to the type of its field, the export stops. Correct the value, or exclude the row.

A row with fewer values than the header gets empty cells at the end. The importer marks each of them. A row with more values than the header loses the extra values. A file warning counts these rows. To see them, select Show these rows.

The usual cause is a comma inside an unquoted value, or a missing quotation mark. Correct the rows in the file and open it again. If the whole file uses another separator, choose it in File settings. The importer then detects the quotation mark and the header row again with that separator.

An Excel file stores the last result of each formula. The importer reads that result. It does not calculate the formula. A file that another program wrote, or that nobody saved after a change, can hold old results or zeros. A file warning counts the cells that contain formulas.

Open the file in your spreadsheet app, recalculate it and save it. Then open it in the importer again.

The importer cannot read a sheet that has a cell with a newer Excel error, such as #SPILL! or #CALC!. It opens the next sheet that it can read, and a file warning names the sheet that it skipped. Correct the formula, or paste the values in place of the formulas, and save the file again.

You can open several files together. The first file sets the columns. The rows of each other file match them by column name.

  • The importer does not import a column that only a later file has. A file warning names the file and the columns. Add the columns to the first file, or import that file on its own.
  • A column that a later file does not have is empty in the rows of that file. A file warning names these columns.
  • A value that does not fit the column type of the first file is empty. A file warning counts these values for each file.

Text such as é in place of é can mean the wrong character encoding. If the file has a byte-order mark, the importer uses it. If not, the importer estimates the encoding and reports text that looks wrong.

  1. Select the file name in the header to open File settings.
  2. Choose the encoding that the file uses.
  3. Select Apply. The importer reads the file again.
  4. Look at the values before you continue.

File settings shows the encoding in use first, then common encodings. To enter any label from the WHATWG Encoding Standard, choose Other. In that standard, ISO-8859-1 is a label of windows-1252.

If you build an import and you know how the files of your users are written, set fileSettings.

null means an empty value. These values are empty, with any capital letters: an empty cell, NULL, N/A, NA, NaN, None, nil, <NA>, -, \N and the Excel errors #N/A, #VALUE!, #REF!, #NAME?, #NUM!, #NULL! and #DIV/0!. In a text column, NA, NaN and None stay as written, because they can be data. For example, NA is the country code of Namibia.

A value with a time, such as 2024-03-14T09:30:00Z or 2024-03-14 09:30:00, keeps its time in the export. A column of dates without times exports dates such as 2024-03-14. In a column where some values have a time, a value without a time gets midnight. A time keeps a zone only when the file states one: a value with a zone exports in UTC with Z, and a value without one exports with no zone.

This is a fault in the page that embeds the importer, not in the file. Tell the owner of the page. Troubleshooting lists the causes.

An unavailable rule did not run, and it does not count as a pass. The message on the rule gives the cause. See Messages about a rule.

The importer can use at most about 4 GB of memory for the parsed file, the edits and the exports. The memory of the device can stop a file sooner.

Before it opens a CSV or TSV file of 512 MiB or more, the importer reads a sample. From the sample, it estimates the memory that the whole file needs. From about 2.5 GB it warns: “This file needs about … of memory. This browser tab can run out of memory.” From about 3.5 GB it does not open the file and says: “This file needs about … of memory. A browser tab can use at most 4 GB. Split the file, or save it with fewer columns.” Performance lists the files that we measured, with the time and memory that each one needed.

  1. Close other tabs that use a lot of memory.
  2. Use a desktop browser, not a phone or a tablet.
  3. Look at the row count while the file opens, to see whether the importer still makes progress.
  4. If the file still does not open, split it and import each part.

Describe the steps that caused the problem. Support names the contact. Do not send customer files or credentials.