Make sure there are no completely empty rows or columns. Though sometimes hard to see, the clear command in Excel can help with this. A text editor program like BBEDit can also expose blanks and make them easy to delete.

Make sure each column of data contains the same type of data (otherwise the import may end up with last names First Name, addresses in Company Name, etc).

Add meaningful column headers. * Use the appropriate special column names where relevant, especially for the column being used to match records and replace data in the database.

Remove unintentional or extra spaces (often these are hidden spaces at the front or end of cell values.

Remove hidden formatting. * This can be more easily done with a text editor like BBEdit to search for invisibles like carriage returns.

If you want a default for a particular column (e.g. if there is no country listed for some records and you want a default), then fill down columns with missing data.

If appropriate, add new columns to create defaults for imported data. For example, if you have new patron information from attending a corporate event, the original document may be missing company name and work address, information you could complete with relative ease and accuracy.

Reformat data for consistency. Here are some general guidelines for common fields:

• Patron Number
  • If you are using patron number as the matching field (say for address correction purposes), then you must include the patron number with each record (row) and the field (column) heading must be MKT_C_SEQ.
• Salutations
  • Ensure all values have a period where appropriate (e.g. Mr., Mrs., etc).
  • Ensure all "and" values are either and or & (e.g. Mr. & Mrs.).
  • Ensure all values are set to the proper mixed case (e.g. Mr. instead of mr.).
• First Name
  • Ensure all "and" values are either and or &
  • Ensure that salutations are moved out of this field and into the Salutation column (e.g. John & Mary or John and Mary instead of Mr John Norris and Mary).
• Last Name
  • Move all company names into the Company Name field.
  • Move all suffixes included here to the Suffix field.
• Suffix
  • Ensure all values have a period where appropriate (e.g. Jr. instead of Jr).
  • Ensure all values are set to the proper mixed case (e.g. Sr. instead of sr.).
• Address
  • Update address information to contain the Post Office address guidelines for mailings (e.g. St isntead of street, PO BOX instead of P.O. Box, etc). Here's the USA guidelines and the Canadian Guidelines for quick reference.
  • Make sure the street address information is broken up into two separate fields (Street Address Line 1 and Street Address Line 2) if needed (e.g. 1 State St and Apt 12 instead of State St Apt 12). If you have more than 2 address lines, the information either needs to be combined into two fields or, more likely, some of it belongs in a different patron field.
• State/Province
  • Ensure all values are consistent with a 2-letter state/province code.
  • Ensure that all the 2-letter codes are accurate.
• Country
  • All country values must match those that are in Code Tables >> Country. Those values that do not match will be ignored.
• Phone Numbers
  • To your best ability, ensure that phone numbers are properly placed into separate columns as Home Phone, Work Phone, Fax, or Other Phone.
  • Depending on time availability, telephone number extensions should be standardized to start with a 'x' (e.g. x111 instead of ext. 111).
  • If area codes are missing from phone numbers and you wish to have all the missing area codes set to something, please contact Artsman prior to the data being imported.
  • If the phone number being imported needs to become the primary phone number on the patron record, use the special column header of gPrimaryPhone.

Once the data is clean, save the file as either tab-delimited (.txt, recommended) or comma-separated (.csv). These are the best format for importing accurately.