If you work a lot with library title lists in Excel, you may have noticed a couple problems in the course of using the Import Wizard:
- Diacritics get mangled, requiring multiple “find and replace” runs to fix the mess after the fact. Unless, of course, you remember to switch from ANSI to UTF-8 on the first screen of the Import Wizard.
- Long ID numbers get turned into scientific notation, losing their uniqueness and thus their value. Also ISSN numbers beginning with “0” may lose their leading zero. A holding statement of (1990) may translate into -1990. It’s a real pain to click on each column and select Text individually.
It turns out you can at least set Excel to default to UTF-8 (Unicode) and there’s a quicker way to turn all your columns into text format, eliminating the annoying treatment that Excel does to number-like strings that aren’t really just plain integers.
Set the default character coding to UTF-8: (this worked for me on Excel 2007. For files where you get gobbledygook in UTF-8, change it back to ANSI).
(Not for the faint of heart if you’re afraid to change registry values!)
Quick way to turn all columns into text while in the Import Wizard:
Basically, when you’re on the column screen, click on the first column while holding shift, scroll the columns all the way to the right so all the columns are highlighted, then select Text as the column data format. If you have some “real numbers” you can always change them back later.