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.
Read the file warnings
Section titled “Read the file warnings”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.
Dates have the wrong order
Section titled “Dates have the wrong order”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:
- Open the column menu from the column name.
- Choose Date order.
- 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.
Numbers appear as text
Section titled “Numbers appear as text”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%.
One value is written in different ways
Section titled “One value is written in different ways”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
trueorfalse. - 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.
Blank cells mean the value above
Section titled “Blank cells mean the value above”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.
Rows repeat
Section titled “Rows repeat”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.
Some cells do not convert
Section titled “Some cells do not convert”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.
Rows have too few or too many values
Section titled “Rows have too few or too many values”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.
Formula results can be out of date
Section titled “Formula results can be out of date”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.
Combined files lose a column
Section titled “Combined files lose a column”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.
Accented characters look wrong
Section titled “Accented characters look wrong”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.
- Select the file name in the header to open File settings.
- Choose the encoding that the file uses.
- Select Apply. The importer reads the file again.
- 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.
A cell shows null
Section titled “A cell shows null”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.
Dates and timestamps differ
Section titled “Dates and timestamps differ”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.
The hosted importer does not start
Section titled “The hosted importer does not start”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.
A rule cannot run
Section titled “A rule cannot run”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.
A large file exceeds browser memory
Section titled “A large file exceeds browser memory”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.
- Close other tabs that use a lot of memory.
- Use a desktop browser, not a phone or a tablet.
- Look at the row count while the file opens, to see whether the importer still makes progress.
- If the file still does not open, split it and import each part.
Report another problem
Section titled “Report another problem”Describe the steps that caused the problem. Support names the contact. Do not send customer files or credentials.