Importing a text validation table into Excel

A well-known issue with Excel is that way it drops leading zeroes when importing a textfile. If we drag and drop a text table onto Excel, it appears to import the column and row structure perfectly–-however leading zeroes are removed, resulting in a corrupt table from our MARC point of view.

The workaround is to drop into Excel's Text Import Wizard¹⁾.

Select File|Open, click Browse, change the filename filter to Text Files (*.prn,*.txt,*.csv), then select one of the validation tables described on the linking page.

A screen like the following should display.

“Delimited” should already be selected.

Check the box labelled “My Data has headers”.

Press “Next”.

Make sure “Tab” is selected as the delimiter.

Then press “Next”.

This is the tricky part.

First, select all of the columns in the “Data preview” (so that they are all colored black, as in the snap).

This can be somewhat unwieldly as the columns scroll out to the right. (FYI: The label of the last column is “EXP”).

When all of the columns are selected, select the Column data format bullet labelled “Text”–you should see all of the column headers in the preview flip from “Generl” to “Text” when you do this.

Once that's done, press “Finish”

When the import completes, save the file in Excel format-–this will lock in the 'Text' import performed above. You will then be able to open the Excel format file in the future and not have to worry about the MARC Tags being truncated.