You are here

Preparation

Subscribe to Syndicate
Data needs to be prepared before it is imported. You need to:
  • Look at the data to import in a tool (like Excel) and adjust the data so columns are consistent.
  • Add column headers for each data item.
  • Use special names for those column headers, especially if the column can be used to match records during the import and replace data in the database.
  • Fill down columns with missing data, 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 entering a country in the empty fields in that column).
  • Add some fields of your own, if appropriate, to create defaults for imported data.
  • You can import the Excel spreadsheet in a number of formats. You can
    • Import the columns you want from the Excel spreadsheet in either .XLS or .XLSX format, even with calculations in it. TM only looks at the content of the column and ignores the actual calculation.
    • Save the Excel document as a TEXT file in either TAB or COMMA delimited format. (TAB delimited is recommended - it seems easier to use).
    • Other tools that will be help are Text editors like BBedit. Opening the prepared text file with an editor can help with hidden formatting like columns, carriage returns, and more. See View, Text Display, Show Invisibles.

Sometimes MS Excel documents have unwanted extra lines and formatting that can increase the record count when imported. To prevent this from happening,
  • Excel Format: Make sure there are no empty lines or columns by using the 'clear' command in excel to ensure no additional empry rows or columns in the spreadsheet. They are hidden and not easy to see
  • TEXT Format: Open the TEXT document in a text reader program like NotePad++ or BBedit. The text reader program will expose the additional unwanted lines. Delete the blank lines. Save the TEXT file. Now it's ready for a clean import.