Order Exports That Open Garbled in a Spreadsheet

The year's orders, unreadable

The year has closed, and the accountant wants every online order from it. The shop exports a CSV file from its shop system and opens it in a spreadsheet to check it before sending.

The order references read 1.23457E+15. Product codes that began with zeros have lost them. A customer in Quebec has odd symbols in the middle of her name, and some codes have turned into dates.

It looks as though the export is broken. Usually it is fine, and the damage happened in the second it took the spreadsheet to open it.

A file with no instructions

A CSV file is plain text: one row per line, values separated by commas. It carries no record of the character set it was written in, or of what kind of value each column holds.

Open it directly and the spreadsheet decides both for itself, using its defaults. Those defaults suit a file typed in the same office, not an export from a web shop.

The guesses are sensible for the spreadsheet's everyday work. For an order export, nearly every one of them is wrong.

The guesses, one by one

• Long numbers become scientific notation, so a 16-digit reference shows as 1.23457E+15.
• Digits past the fifteenth become zeros, because the spreadsheet keeps only 15 significant digits.
• Leading zeros vanish, because 00412 read as a number is 412.
• Accented letters become pairs of symbols, because the file is read with the wrong character set.
• Some codes become dates, because a value like 3-12 looks like March 12.

None of these shows up as an error. The cells look tidy, and if the file is saved, the changes are written into it for good.

Why long numbers change

A spreadsheet treats anything made only of digits as a number. In its usual format, a very long number is shown in scientific notation, which on its own is only a change of display.

The real loss goes deeper. The common spreadsheets keep 15 significant digits, so a payment reference of 16 digits or more is rounded, and its last digits become zeros.

Formatting the column afterwards does not bring the original digits back. Save the file, and the rounded number replaces the real one.

Why the accents break

Most web shops write their exports in UTF-8, a character set that stores an accented letter as two bytes. The common spreadsheets on Windows assume an older set that uses one byte per letter.

Read that way, each of the two bytes becomes its own character. An e with an acute accent turns into a capital A with a tilde, followed by a copyright sign.

Some spreadsheets look for a short invisible marker at the start of the file, called a byte order mark, that announces UTF-8. A file without it is read with the older set.

Exporting so nothing changes

Check the shop system's export settings. If it offers UTF-8 with a byte order mark, sometimes labelled as a format for spreadsheets, choose that.

Export only the columns the accountant needs, for the dates they asked for, and put that date range in the file name.

Then leave that file alone. It is the record; anything opened and saved afterwards is a copy.

Opening the file the careful way

Instead of opening the file from the email or the downloads folder, open a blank spreadsheet and use its command for importing a text file.

The import asks three questions that matter here. Choose UTF-8 as the character set and the comma as the separator, then set the columns holding order references, product codes and other numbers that are really labels to text.

Text columns are shown exactly as written. The references keep every digit, the codes keep their zeros, and nothing turns into a date.

Compare a few rows with the orders in the shop's admin, then save the result in the spreadsheet's own format rather than back to CSV.

A worked example

A trading card shop in a suburban strip mall sells single cards, sealed boxes and supplies in the store and online to players across Canada.

Last January the owner opened the year's export, tidied a few columns and saved it before emailing it to the accountant. The payment references in it had lost their last digits.

The accountant could not match orders to the payment company's deposits. Product codes, which the shop pads with zeros, no longer matched the stock list, and several customers' names were garbled.

This year the export is set to UTF-8 with the marker, and the original file goes into a dated folder unopened. The owner imports a copy with text columns and sends both.

A one-page note beside the computer lists the import steps, so whoever does it next January follows the same route.

Sending it to the accountant

Send the untouched original together with the prepared spreadsheet, and say in the email that the CSV is UTF-8. The accountant can then open it the same careful way.

Leave out columns nobody needs. An export with customers' addresses, emails and phone numbers is personal information, and the fewer copies of it that travel by email, the better.

The counter-case

A small shop with short order numbers, no leading zeros and no accented names may open its export directly and find nothing wrong.

For that shop, the careful import is a habit rather than a repair. Its file still carries no instructions, and a new product code or a customer with an accented name can change the picture.

Keeping the original export untouched costs nothing, and it protects any shop from one careless save.

Before the file goes to the accountant

1. Export the orders as UTF-8, with a byte order mark if offered.
2. Keep that file untouched, named by its date range.
3. Open a blank spreadsheet and import a copy.
4. Choose UTF-8 and the comma as separator.
5. Set reference and code columns to text.
6. Check a few rows against the shop's admin.
7. Save the copy in the spreadsheet's own format.

Step two keeps every mistake reversible. As long as the original export exists unopened, any mistake made in the spreadsheet can be undone by importing it again.

Words that software cannot read as words cause a different problem on social media, one that social posts that are only a picture of words takes up for screen readers.

Frequently asked questions

West Coast Media Solutions Inc. provides web design, web development, hosting, digital marketing, and business consulting to organisations across Canada, drawing on more than twenty-five years in the field.