Excel file import tricks for the file-weary

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).

STUFF: MISCELLANEOUS TOPICS ALL VAGUELY RELATED TO SCIENCE by Luke Miller. Change default text import origin type in Excel.

(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:

Allen Wyatt’s EXCELTIPS (Ribbon Interface). Faster Text File Conversions.

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.

Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: