Importing Data

The TSM Import Wizard provides an easy way to import data from other applications into TSM using a predefined profile or one that you create in the wizard.

The first screen you will come to is the ‘Welcome to TSM’s Import Wizard’ dialog box.

Select whether you want to import Parts or Customers. Click Next.

Import a New File
The incoming data is formatted according to the profile you define in the Import Wizard. You can save the profile for future use.

Import a new file using an existing profile
In the Select file to import dialog box you can select a profile you have previously saved.

Text file (TXT)
Specifies that the incoming data is in delimited text file format. A delimited text file formats data with field delimiters, field separators and record separators. In the delimited ASCII format, fields are referred to by their position in the record.

Text file (CSV)
As above but usually comma separated (Comma Separated Values)

Excel File (XLS)
Microsoft Excel spreadsheet.

Lotus Worksheet (WKS)
Lotus 123 Worksheet

Text files have each field separated by a character called a delimiter. Choose whether this delimiter is a comma or a tab character and click Next.

The first record in the import file can be data or a header record. Header records identify the name of each field in the data file and are not normally imported. Header records make it easier to match the fields in the data file to TSM field names. Choose Header Record or Data Record (if the data file does not have a Header record) and click Next.

TSM Checks for duplicate parts only when importing a Part Number field or duplicate contacts when importing a Customer ID. TSM will look for the existence of an imported Part Number (for parts import) or Customer ID (when importing customers) to determine whether a duplicate has been imported.

If a duplicate record is detected, you can choose to take the following action:

• Reject it so the item is not imported
• Update the existing record with the new item
• Update the existing record (no insert), ie update the existing record with the new item, but don't create a new item if it doesn't already exist

For parts import only, if you are importing on hand quantities then you need to decide whether the quantity imported will replace or add to the existing on hand quantity. For example, you would replace on hand quantities if you were importing a stock take data file but might add to on hand quantities if you were importing data for new stock brought into the warehouse.

Note: If you have multi-location stock control enabled and you do not bring in a warehouse location, imported parts will be automatically assigned to the default warehouse location. Refer to Utilities / Setup / Inventory - Default Location.

Click on … to browse for the file to be imported. Select the file and click Next.

As you configure a new import profile or modify an existing one, the Field Mappings dialog box allows you to map fields in your import file to TSM fields.

The Import Fields list displays each field you are importing. If your first record is a header record, this will be a descriptive list of fields from the import file. If the data imported does not contain a header record, this will display Field1, Field2 etc. corresponding to each field imported.

The TSM Fields list displays each field in the TSM data file (Parts or Contacts). Any fields that are required will have a 'Y' in the Required column.

Highlight the Import Field you are mapping, then select the TSM Field to which you are mapping the Import field. Click Map to TSM.

The Import Field name will appear next to the corresponding TSM field under the heading 'Matching Import Fields'.

Continue this process for each field you are mapping.

To delete an import field mapping, highlight the field mapping and click Remove.

You can also set a default value for a TSM field, even though that value is not in the import file. For example, you might be importing a contact file and you can set ‘Yellow Pages' as the default value for the TSM Source field.

If the field names in your import file are the same as the TSM field names, you can quickly match all corresponding fields by selecting the Match Names button. Any matching import fields names will automatically be matched to the TSM fields.

Selecting the Match All button will map each field in the import file with the corresponding field in field order. That is, field 1 in the import file will map to the first TSM field, field 2 to the second field and so on.

Clears out all field mappings.

This option allows you to optionally set a limit on the number of fields to import per record. Ensure the number entered is at least as large as the total number of fields being imported. It does not matter if it is larger but if it is lower the number of fields imported will be restricted and not all of your data will be imported. Setting too high a value will unnecessarily increase the time and memory required to process the import. Enter the Maximum no of fields you wish to import and click OK, or click on Cancel to exit without saving.

Save Profile

The Save Profile dialog box allows you to save your import settings as a profile that you can use when you are importing data into TSM in the future.

• On the Save Profile dialog box, select Yes.
• In the Profile Name text box, type a descriptive name for the new import profile.
• If you do not want to save the profile, select No.

When you are ready to commence importing your data file, select the Import button or select Cancel to abort the import.

TSM can import ASCII text files. The delimited ASCII format is the most commonly used file format. When viewed with a word processor or text editor, data saved in a delimited format might look like this:

“ABC Company”,“John Smith”,“123 Main Street”,“Anytown”,“NSW”,“2222”
“Joe’s Cleaners”,“Joe Turner”,“55 Ashley St.”,“Bondi”,“NSW”,“2030”
“Mr. T’s Ribs”,“”,“200 Eton Ave.”,“Redfern”,“NSW”,“2016”

Each field in the file is surrounded by a delimiter, which is usually the double-quote character (“). Adjacent fields are separated by a field separator, which is usually a comma (,). Each record is terminated by a carriage-return/line-feed combination. Carriage return or line feed characters cannot be embedded within the data.

A blank field’s position in the record contains empty quotes, as shown in the second field in the third record. In the delimited ASCII format, fields are referred to by their position in the record. For example, in the above example, company name is field 1, contact name is field 2, etc.

TSM can import data from a Microsoft Excel worksheet. Each column from the worksheet becomes a field in the table and each worksheet row becomes a record in the table. Worksheet files created in Microsoft Excel are given a .xls or file name extension.

TSM can import data from a Lotus 1-2-3 revision 1-A spreadsheet. Each column from the spreadsheet becomes a field in the table and each spreadsheet row becomes a record in the table. Worksheet files created in Lotus 1-2-3 revision 1-A are given a .wks or file name extension.