Round Tripping Exports Back Into Imports Without Drift

"Export, edit in a spreadsheet, import" is a workflow users expect to work. They download their customers, fix forty cells, upload the file and assume that the other 3,960 rows come back as they were. Whether they do is a property you can test, and the test most people write is the wrong one. They export twice and compare the files, find them identical and conclude the feature is safe. This article builds the right test, shows with a real run how the wrong one passes while the data changes and goes through the places where a CSV round trip loses information. It is one of the contracts discussed in CSV import and export features in applications, and the NULL question it exposes is the same one that handling null and empty strings in PostgreSQL examines from the database side.

The experiment

A source table with four rows, each testing a different hazard:

The steps: export the table to a CSV with fputcsv, import that file into a second, identical table, and export the second table. Then compare. The CSV that comes out of the first export is:

id,name,note,active,balance
1,"Acme Ltd",,1,120.50
2,"Beta, Corp",,0,0.00
3,"Cy ""Foods""","two
lines",1,15.00
4,"  Delta  ",x,1,007.10

Look at rows 1 and 2. The note is empty in both, and the file does not say which was NULL and which was an empty string. (PHP quotes Acme Ltd because it contains a space, a quirk of fputcsv, harmless but worth knowing when you compare against files from other writers.) The comma, the quotes, the line break, the padding and the leading zero in 007.10 are all preserved: quoting and doubling handle the first three, and the last two are plain text in a text column.

The comparison that lies

Compare the first export with the second:

second export identical to first: true

The files are byte for byte the same, and the test passes. Now compare the tables, row by row, looking at how each note was stored before and after:

rows that differ after the round trip: 1
  id 1  note before NULL         after ''
  id 2  note before ''           after ''

Row 1's note was NULL in the source and is an empty string in the copy. The round trip changed the data, and the second export, which writes NULL and empty string identically, could not show it. Any check that goes through the file format is blind to a difference that the format cannot express. That is the general lesson: compare the data in its native form, on both sides of the round trip, not the files in the middle.

The other rows came through intact, including the one with the multi-line note and the quotes. In a table where the distinction between NULL and empty matters (a nullable foreign key as text, an optional field where "unset" differs from "set to nothing"), the loss is real. Where it does not matter, it is a documented simplification.

NULL versus empty, your choice

There are exactly two readings of an empty cell, and each loses something. Read an empty cell as an empty string, and a NULL becomes '', which is what the test above did to row 1. Read it as NULL, and rows 1 and 2 both come back as NULL:

--- with empty -> NULL on import
  id 1  note before NULL         after NULL
  id 2  note before ''           after NULL

Row 1 is now right and row 2 is wrong. Neither policy round-trips both, because the information is not in the file. The ways out are the following.

Decide that the distinction does not exist at the application level: store NULL for "nothing" everywhere, and normalize on write, so the table can never hold an empty string in that column. Then reading empty as NULL is lossless. This is the simplest and best answer for most text fields.

Or make the file carry the difference. RFC 4180 has no rule for it, but a common convention is that an unquoted empty field is NULL and a quoted empty field ("") is an empty string, which PostgreSQL's COPY ... CSV follows (PostgreSQL CSV versus text format in COPY). The writer must then force quotes on empty strings, which PHP's fputcsv does not do, and the reader must distinguish quoted from unquoted, which many parsers hide. Or use an explicit marker such as \N or NULL, documented, with the risk that a real value NULL collides.

Or accept the loss and say so: "Empty cells are imported as empty text." That is honest, and it is better than leaving it to chance.

Other places a round trip loses data

The table above had four rows, and several other hazards can be tested the same way. Types: numbers written with trailing zeros (120.50) come back as 120.5 if the import parses them to floats and the export formats them again; keep decimals as decimals or as text and format explicitly. Dates and time zones: a timestamp exported in local time and imported as UTC moves by the offset; write ISO 8601 with an offset. Booleans: 1 and 0 out, true and false in, or the reverse; accept both on import and pick one for export. Whitespace: padding survived in the test, and many importers trim, which changes the data and deserves a rule of its own. Encoding: the writer's UTF-8 and the reader's default code page. Line endings: embedded line breaks may turn into \r\n after passing through a Windows tool. Order: unless the export sorts, the order of rows is unspecified, so a comparison by position fails and one by key does not.

Spreadsheets add their own. Excel opening the exported file and saving it can strip leading zeros from 007.10 if the cell was typed as a number, round long numbers to 15 digits, convert 1-2 to a date and rewrite line endings (opening CSV files in Excel and Sheets without damage). A round trip through a spreadsheet is a different test from a round trip through code, and it is the one your users do. Test both: write a file, open it in the spreadsheet your users use, save as CSV and import it.

The assertion

The test belongs in your suite, and it is short. Load fixture rows that hit each hazard into the source table. Export. Import the file into an empty target table. Compare the two tables, by key, column by column, with an equality that treats NULL as unequal to the empty string, and fails with the row and column of the first difference. Then assert the second export equals the first as a cheap secondary check, knowing it is secondary.

The fixture matters more than the code. Include a NULL, an empty string, a value with a comma, a quote, a line break, leading and trailing spaces, a leading zero, a long number, a date at midnight, a negative decimal, a non-ASCII name and a value that starts with =. If your system cannot round-trip one of them, the test should say so, and you decide whether to fix it or document it. Keep the fixture in the repository, add a row for every bug a user reports and run the test in CI. A round-trip test that checked only the happy path is a test that passes on the day before the incident.

When a round trip cannot be lossless

Some data cannot go through CSV unchanged. Binary values, nested structures, very large numbers and values with mixed types in one column all need an encoding that the file does not give you. A relation with line items does not fit in one flat table without a convention. For those, the honest design says which columns are exportable and which are not, and offers a different format (JSON, a zip of files) for the whole record. If the feature promises "edit and re-import", restrict it to the columns that survive, and state the limits in the template and the interface, since a user who loses a nuance in a note field will blame the tool and not the format.

Also be clear about the direction that is allowed to be lossy: an export for reading, such as a report, can drop information and format values for humans, and an export meant to be re-imported cannot. Build them as separate outputs, from the same column definitions, with names that tell users which is which. The behavior of the PHP functions is documented in the fgetcsv and fputcsv pages, and the format in RFC 4180.