1. Start Excel or your spreadsheet program of choice
2. Select "open" and open your data file. It should have a .txt file extension.
3. The text import wizard will prompt you to indicate if the file is delimited or fixed width. Choose "delimited" and click on "next" if you do not need to deal with multiples of the same field, e.g. 020 or 500. This is the buggy part of working with the data. The utility in MarcEdit smooshes multiples of the same MARC field together.
4. Select "tab" as your delimiter and then click "next"
5. If you are working with numerical data like ISBNs, format that column as TEXT so that the data doesn't get read as a formula in Excel. Click "finish"
6. Open file. Edit. Use "replace" to clean up data if you need to remove quote marks and other punctuation that came over in the export. Save as Excel spreadsheet.
7. If you need to separate multiple 020s, 500s, etc.:
a. instead of opening the file in Excel as "delimited," choose "fixed width."
b. Create link breaks as needed to separate your fields that repeat so they go into discrete columns.
c. Format text as needed (see step 5 above). Select "finish"
d. Clean up data as described in step 6 above.
Formatting Hyperlinks for Awesome Table:
- Open Word document and enter the following data:
2. Add a merge field for the URL and a title field. Example: <a href="URL" target="_blank">TITLE</a>
3. Merge as a letter - each formatted URL will be printed on a seperate page.
4. Use Find and Replace tool, type in the following:
NOTE: If creating titles with hyperlinks is too cumbersome, you can always add 2 columns in your Magic Table: one for title and one for the actual link. Formatting links with titles greatly reduces the 'clutter' of the catalog page.
Merging Subject Fields
- Create or designate a blank column to hold the combined subject heading tabs (ie. 600, 610, 650). Insert this formula in the first row of the designated column: =F1&G1&H1&I1&J1
- Hover the mouse over the bottom right corner of the cell containing the formula. The cursor will look like a cross. Drag the cross to the last row in this column - this copies the same formula for each row (i.e. it will add the subject headings in each row into a single cell).
Merging Title & Subtitle
At your discretion, you might want to merge the title and subtitle fields (245$a and 245$b respectively). If so, follow the same steps as merging various subject headings. Be sure to delete the original columns containing the separated sub fields.
Thanks again to Monica Berger who originally published this tutorial on CUNY Libraries wiki page and allowed me to re-print it with permission!
Be sure to watch my tutorial on how to set up a library catalog using Awesome Table and Google Sites to make a fully searchable online library catalog.