Skip to content Skip to main navigation Skip to footer

Import Field Mapping

Learn how to map fields for import.

Patron Fields

Item Fields

Textbook Fields

Alexandria and Textbook Tracker support importing tab-delimited files. You can either set up the data with special headers to specify what the column is, or you can use field mapping on the import window to line up your columns with the Alexandria data.

Alexandria also supports importing item records in MARC and MicroLIF formats; these formats do not require headers or field mapping.

The easiest way to get Alexandria headers is to first perform an export with all of the fields you need. This will give you a pre-formatted file that you can then edit for import.
Table of Contents

For valid import files with headers, Alexandria will automatically sort, organize, and match the information to the correct, specified fields. If the import file is missing a header—such as when you’ve received data from an outside source—it will not be recognized. The relationships between fields and attributes of different names and types may not match and have to be mapped. Alexandria will require you to set your patron and item field mapping in order to proceed.

Field Mapping is required for all tab-delimited import files that do not have a header.

Use the Field Mapping dialog to specify where to import the data by mapping the fields in your database to the fields in the source data you’re importing. Data from the import file is shown on the left of the window, and Alexandria or Textbook Tracker field names are shown on the right.

Data is imported in sequential order; use the up and down arrows to correctly align the fields of the import file. Click on the icon on the left of a field to have it included in the import (a green checkmark) or not included (red X).

When you’ve finished matching Alexandria fields to the information in your file, click Set.

If you have a patron with a combined first and last name (one that has not been separated by tab delimitation), use the Patron Full Name field to split the name into First and Last Name during import.

FTP Field Mapping

When you use FTP, you can’t specify the mapping for an unknown file, so you must set the mapping beforehand. We highly recommend using Alexandria headers. Field mapping is required for all files that do not have headers. In the File Source tab of the Import window, use the Field Mapping to order the Alexandria fields to match the fields in your import file.


About Computerized Records

Before going further, it helps to understand some basic concepts about computerized records.

Before going further, it helps to understand some basic concepts about computerized records. In order to exchange information between different programs (or even between different computer systems), one needs to get the information into a format that many different programs can understand. The standard, called ASCII, is widely used to exchange information between different programs and operating systems. Sometimes an ASCII file is called a text file (.txt) because all it contains is textual information.

ASCII files contain two types of characters: standard visible characters such as numbers and letters of the alphabet, and special characters that are called Control Characters. Control Characters are used for special purposes to control the display or interpret the information in the file. If you have computerized data, you may be familiar with the <tab> and <return> control characters that are used to make text more legible.

When dealing with computerized information systems, you often hear the terms field, record, and file. A field is the smallest unit of information stored. An example of a field is a book’s title. A record is a related group of fields. Many records of the same type saved together are called a file. For example, in a file of names, a record consists of the fields “First Name” and “Last Name”. A more complex file may include records with other fields such as “Phone Number” and “Address”. In other words, records contain fields filled with information, and files contain many records.

In the library world, there is a fairly well-defined method of exchanging bibliographic information between computers. This format is called MARC (for MAchine-Readable Cataloguing). Another format of the MARC is called MicroLIF (for Microcomputer Library Information Format). Alexandria has been programmed to recognize both MARC and MicroLIF files automatically during import. Alexandria can also export in both formats.

In addition to importing and exporting MARC and MicroLIF records, Alexandria is capable of importing and exporting files in a tab-delimited format. This format can be used to exchange information with other programs that utilize more complex MARC standards. Most database, spreadsheet, and word-processing programs on personal computers support files in a tab-delimited format.

A very common method of exchanging record information between programs is the tab-delimited ASCII file. In the tab-delimited format, fields are separated by the <tab> control character and the end of the record contains the <return> control character.

John <tab> Smith <return>
Bill <tab> Jones <return>
My First Name <tab> My Last Name <return>

Using tab-delimited patron import files, you can transfer information about patrons stored in other computer systems. For example, a school can use this capability to transfer student information from the school administration computer system into Alexandria (or transfer library information back to the administration system).

Tab-Delimited Import File Format

In Alexandria, we’ve enhanced the tab-delimited format by adding special information on the first line of the file, used to interpret the data in the file. This first line is called a header because it’s at the head (or beginning) of the file. The header allows programs to figure out where the data in the file belongs. Alexandria headers look like this:

###*FileCode/FieldCode/FieldCode/FieldCode/ <return>

The ###* doesn’t mean anything, but it is a rather unusual character combination, unlikely to be found at the beginning of most files. If Alexandria discovers ###* at the beginning of a file, it assumes that the file is in a familiar format.

The Field Code numbers (shown above, such as 1000 for patron barcode) don’t have any special meaning other than acting as unique identifiers for each of the fields that can be imported or exported.

The header is optional. If it’s present, Alexandria will automatically sort and organize the information and place it into the correct, specified fields. If there is no header, Alexandria displays a Field Mapping window, which allows you to specify the order and type of data getting imported. A header is automatically created for all export files.

Since the header is automatically created on export, the information can be transferred to another copy of Alexandria and automatically imported without the user knowing anything about the file structure. This makes it easy for central administrators to transfer information to Alexandria operators. In order to be consistent, Alexandria assumes that the first line of any import file is a header. However, it only knows how to process headers in the format stated above.

Some fields allow multiple lines of information (such as patron notes). Any <return> located in a field is converted into the ‘\’ character on export and on import, the ‘\’ character is restored to a <return>. Generally, you don’t need to know about this; however, if you were to examine an exported file, you’d see these characters. Also, if you wanted to add a <return> into a file that is getting imported, you can use the ‘\’ character to make it happen.

Import File Example

A sample patron import or export file might look like this:

###*PT01/1000/1007/1006/ <return>
1100000 <tab> Bill <tab> Smith <return>
1100001 <tab> John <tab> Jones <return>

In the example above, the header field codes “1000, 1007, 1006” specify that the fields are ordered by barcode, first name, and last name. An item record will be in a similar format, except that different field codes are used and different types of field information are entered (e.g. title or author of the item).

A simple way to study these formats is to export a few records, then examine the export file with a text editor.

Importing Transaction Scripts

Transaction scripts are used to enter transactions using text files rather than typing them in manually. Portable barcode readers create transaction files for import into Alexandria. Transaction files can be imported by dropping them onto the Circulation window if they have a recognized header. When Alexandria is asked to import files with these headers, it will think it’s a transaction file and start processing the contents as transactions.

Import Files from Vendors

Librarians frequently ask us what type of records they should receive and what type of barcode they should use when filling out book order forms from vendors.

Although this can be situational depending on the particulars of your environment, the general response is:

  • For MARC records, choose whichever option lists 852 holdings.
  • For barcode symbology, choose “3 of 9” or “code 39” with no mods and no check-digits.
Deleting Information with an Import

You can delete information from existing patron, title, or copy records with an import, just as you can add and update information. To do this, simply type %%DELETE%% into the field you want to delete in your import file. When the file is imported, the information in that field will be removed from the existing record. Required fields—last name, title, barcode—can’t be removed with %%DELETE%%.

Blank information never replaces existing information (unless you are using the ‘Replace MARC Records on Title Match’); to remove data, use the %%DELETE%% command as described above.

When importing patron records, Alexandria looks for a header that contains a file designation of PT01 to specify patron information.

#Limit designates the character limit of that field.

fieldheadertype#Limitnotes
Barcode1000Import/Export15
First Name1007Import/Export25
Last Name1006Import/Export25
Middle Name1008Import/Export25
Nickname1070Import/Export25
Policy1028Import/Export4Uses policy short code as specified in Policies.
Status1026Import/ExportInteger indicating Status: 1=Active, 2=Card Lost, 3=Suspended, 4=Transferred, 5=Inactive, 6=Other
Government ID *1002Import/ExportTerminology field, often named SSN
Location *1004Import/ExportTerminology field, often named Homeroom
Sublocation *1005Import/ExportTerminology field, often named 2nd Location
Level *1010Import/ExportTerminology field, often named Grade
Community ID *1001Import/ExportTerminology field, often named Student #
Site1050Import/ExportSite code as specified in Site Management
Optional Date *1048Import/Export12Terminology field, often named Graduation Date
Address1011Import/Export100
Address 288Import/Export
City1012Import/Export100
State1013Import/Export100
Postal Code1014Import/Export100
Country1016Import/Export100
Telephone1017Import/Export25
Date of Birth1045Import/Export12
Sex1032Import/ExportInteger indicating sex/gender: Unspecified=0 or U, Male=1 or M, Female=2 or F
Contact Notes1020Import/Export1000
General Notes1021Import/Export2000Only the first general note MARC tag will be exported
2nd Phone Number *1018Import/Export25Terminology field, often named Mobile or Fax
Primary Email1019Import/Export100
Other Emails1054Import/Export100
Categories1025Import/Export1000Separate category terms with \
Patron Accession Date1046Import/Export12
Additional Contact *1049Import/Export100Terminology field, often named Parent/Guardian
Username1052Import/Export100
Lexile Table1060Import/Export300All grades contained in single string, rows separated by // and cells by II (e.g. Patron ID<tab>//gradeIIscoreIIsourcelldate …repeated as necessary)
Password – Encrypted1063Import/Export30This option is used when importing a password that is already encrypted MD5
Password – Plain TextImport only
Account Expiration Date1047Import/Export12
Lexile1051Import/Export4
Reading Level *1053Import/ExportTerminology.
Patron GUID1200Import/Export100
Alert Notes1022Export only
Pending Holds Count1037Export only
In Stock Hold Count1039Export only
Reservations Count1038Export only
Reserves Count1040Export only
Balance (Library)1030Export only
Balance (Textbook)1130Export only
Total Library Payments1031 Export only
Total Textbook Payments1131Export only
Credits1061Export only
Last Use Date1042Export only
Lifetime Usage (Library)1033Export only
Total Library Items Out1034Export only
Total Textbook Items Out1134Export only
Lifetime Overdue (Library)1035Export only
Overdue Library Items1036Export only
Overdue Textbook Items1136Export only
Patron Barcode3000Export onlyExport Patrons Fines
Patron Name3001Export onlyExport Patrons Fines
Type3002Export onlyExport Patrons Fines
Description3003Export onlyExport Patrons Fines
Total3004Export onlyExport Patrons Fines
Charge Balance3005Export onlyExport Patrons Fines
* Terminology


While importing, Alexandria looks for a header that contains a file designation of FT01 to specify item information.

fieldheadermarctype#Limitnotes
Copy Barcode2000852_pImport/Export15
Copy Site Code2023852_aImport/Export8
Copy Location *2024852_bImport/Export8Terminology
Shelving Location *2027Import/Export32Terminology
Copy Accession Date2009852_1Import/Export12
Copy Inventory Date2008852_1Import/Export12
Copy Volume2064Import/Export10
Copy Call Number2025852_hImport/Export50
Copy Policy Code2013Import/Export4
Copy Serial #2441Import/Export50
Copy Condition2017852_1Import/Export4
Vendor2016852_1Import/Export50
Purchase Cost2012852_9Import/Export7Up to $99,999.99
Must include decimal (5.00 NOT 5)
Replacement Cost2011Import/Export7Up to $99,999.99
Must include decimal (5.00 NOT 5)
Special Funds *2022Import/Export45Terminology
Copy Notes2018852_xImport/Export1000
Copy Alert Notes2019852_zImport/Export1000
LCCN2010010_aImport/Export
ISBN2020020_aImport/Export
Title Volume2026092_vImport/Export30
Primary Author2100100_aImport/Export
Title2245245_aImport/Export
Sub Title2246245_bImport/Export
Statement of Responsibility2247245_cImport/Export
Medium2248245_hImport/Export
Edition2250250_aImport/Export
Publisher Place2260260_aImport/Export
Publisher2261260_bImport/Export
Publication Year2262260_cImport/Export
Extent2300300_aImport/Export
Other Physical Details2302300_bImport/Export
Dimensions2303300_cImport/Export
Accompanying Material2304300_eImport/Export
General Note2500500_aImport/ExportOnly the first general note MARC tag will be exported
Content Notes2501505_aImport/Export
Summary2520520_aImport/Export
Target Audience2521521_aImport/Export
Review Source2522521_bImport/Export
Lexile Value2757521_aImport/ExportThe term Lexile will be automatically added to 521_b
Lexile Code2758521_aImport/ExportThe term Lexile will be automatically added to 521_b
Fountas and Pinnell Value2761521_aImport/ExportThe term Fountas and Pinnell will be automatically added to 521_b
Guided Reading Value2762521_aImport/ExportThe term Guided Reading will be automatically added to 521_b
Study Program Name2750526_aImport/Export
Study Program Interest Code2751526_bImport/Export
Study Program Reading Level2752526_cImport/Export
Study Program Point Count2753526_xImport/Export
Study Program Test Number2759526_zImport/Export
Study Program Have Test2754526_9Import/Export
Study Program Holding Code2755526_5Import/Export
First Subject2651650_a
650_x
650_y
650_z
Import/Export
Second Subject2652650_a
650_x
650_y
650_z
Import/Export
Third Subject2653650_a
650_x
650_y
650_z
Import/Export
Fourth Subject2654650_a
650_x
650_y
650_z
Import/Export
Fifth Subject2655650_a
650_x
650_y
650_z
Import/Export
Bibliographic Term2040653_aImport/ExportSeparate each term by \
Genre2442655_aImport/Export
Curriculum Term2041658_aImport/ExportSeparate each term by \
Series2440830_aImport/Export
URL Description2531856_yImport/Export
URL2530856_uImport/Export
Call Number2063900_aImport/Export
Don’t Show Title in Researcher2042917_aImport/Export
Policy Code2052Import/Export
Copy Status2015Export only
Copy Borrowed Date2001Export only12
Copy Days in Circulation2003Export only
Copy Check Out Count2002Export only
Copy Last Modified Date2004Export only12
Copy Last Modified By2005Export only12
Copy Transit History2006Export only300
Copy Usage History2007Export only300
Accession Date2047918_bExport only
Last Use Date2049Export only
Last Modified Date2050918_cExport only
Last Modified User ID2051918_oExport only
Last Validation Date2054Export only
Full Title2240Export only
Copy Count2043Export only
Available for Check Out2044Export only
Title Life-to-Date Usage Count2045Export only
Checked Out To2055Export only
* Terminology

While importing, Textbook Tracker looks for a header that contains a file designation of TT01 to specify textbook information.

#Limit is the character limit for that field.

FieldHeaderType#LimitNotes
Barcode800015
Copy Modified Date8004Export12
Copy Inventory Date8008Export12
Copy Accession Date8009Import/Export12
Replacement Cost8011Import/Export7Max: $99,999.99
Purchase Cost8012Import/Export7Max: $99,999.99
Copy Policy Code8013Import/Export4Policy code as specified in Policies.
Copy Status8015Export1Integer representing Status.
Vendor8016Import/Export50
Copy Condition8017Import/Export4Condition code as specified in Preferences.
Copy Notes8018Import/Export500
ISBN8020Import/Export15
Funding Source8022Import/Export45
Copy Location *8024Import/Export32
Volume8026Import/Export50
Copy Shelving *8027Import/Export32
Copy Assigned Patron8028Export
Site Code8029Import/Export8Site code as specified in Site Management.
Title Last Modified Date8050Export12
Title Policy8052Import/Export4Policy code as specified in Policies.
Title ID8063Import/Export30
Title Created Date8098Export12
Title Record Number8099Export
Author8100Import/Export500
Title8245Import/Export500
Edition8250Import/Export50
Publisher8261Import/Export250
Publication Year8262Import/Export4
Extent8300Import/Export500
Serial #8441Import/Export50
Stock #8442Import/Export30
Department8443Import/Export50
Budget8444Import/Export30
State Adoption Date8445Import/Export12
District Adoption Date8446Import/Export12
General Note8500Import/Export3000
Materials Note8501Import/Export3000
* Terminology

You can use an import to update study program information in MARC tags for titles already in your collection.

You probably wont have any trouble importing MARC or MicroLIF records provided to you by a vendor or data services company, but importing your own data may be a bit more complicated. Alexandria uses the import header to match the imported data to the correct fields. If your import file does not have a header, however, you will need to map your fields. This is crucial to ensuring that your data is imported correctly. 

Go through each tab above to see examples of how to format your tab-delimited file and map your fields to correctly import study program data.

Accelerated Reader
MARC Field

526 – Study Program Information Note

Subfields

_a     Program name
_b     Interest code
_c     Reading level
_d     Point count
_z     Test number
_9     Have test

Tab-Delimited Example

Format your import file as follows:

ISBN<tab>title<tab>study program name<tab>study program interest code<tab>study program reading level<tab>study program point count<tab>study program test number<enter>

0439678137      Gregor the Overlander      Accelerated Reader      MG      4.8      8.0     71754
New MARC Tag

Alexandria will use the data in the import file to match each each record to an existing title and update the MARC tag. In this example, the new MARC tag will look like this:

526_aAccelerated Reader_bMG_c4.8_d8.0_z71754^
Reading Counts
MARC Field

526 – Study Program Information Note

Subfields

_a     Program name
_b     Interest code
_c     Reading level
_d     Point count
_z     Test number
_9     Have test

Tab-Delimited Example

Format your import file as follows:

ISBN<tab>title<tab>study program name<tab>study program interest code<tab>study program reading level<tab>study program point count<enter>

9780142301937      Rapunzel      Reading Counts      K      3.8      2.0
New MARC Tag

Alexandria will use the data in the import file to match each each record to an existing title and update the MARC tag. In this example, the new MARC tag will look like this:

526_aReading Counts_c3.8_d2.0^
Lexile
MARC Field

521 – Target Audience Note

Subfields

_a     Lexile code and value
_b     Program name

Tab-Delimited Example

ISBN<tab>title<tab>Lexile code<tab>Lexile value<enter>

9780060835248      Scary Stories 3      BR      680

The program name does not need to be in the import file.

Alexandria automatically adds the program name to subfield _b when the Lexile import mapping option is set or it detects a field mapping header.

New MARC Tag

Alexandria will use the data in the import file to match each each record to an existing title and update the MARC tag. In this example, the new MARC tag will look like this:

521_aBR680_bLexile^
Guided Reading
MARC Field

521 – Target Audience Note

Subfields

_a     Reading program value
_b     Program name

Tab-Delimited Example

ISBN<tab>title<tab>Guided Reading value<enter>

0439531640      Inkheart      5.4

The program name does not need to be in the import file.

Alexandria automatically adds the program name to subfield _b when it detects Guided Reading in the import header or the Guided Reading Value field is mapped.

New MARC Tag

In this example, the new MARC tag will look like this:

521_a5.4_bGuidedReading^
Fountas and Pinnell
MARC Field

521 – Target Audience Note

Subfields

_a     Reading program value
_b     Program name

Tab-Delimited Example

Format your import file as follows:

ISBN<tab>title<tab>Fountas and Pinnell value<enter>

0689500297      The Grey King      6.2

The program name does not need to be in the import file.

Alexandria automatically adds the program name to subfield _b when the Fountas and Pinnell import mapping option is set or it detects a field mapping header.

New MARC Tag

Alexandria will use the data in the import file to match each each record to an existing title and update the MARC tag. In this example, the new MARC tag will look like this:

521_a6.2_bFountas and Pinnell^
Have questions? Reach out to our stellar support team at (800) 347-4942 · support@companioncorp.com for help!