[IM] Tips for Using the Import Module

Below are some general tips that may be helpful when using the Import module.

1. A collection of online training videos is available through the Viewpoint Academy. If you are new to importing, these may be very helpful: Introduction to Vista Imports

2. Before starting a new import template, spend some time reviewing how you enter data into that form manually. That is, if you are importing AP Invoices, manually enter an invoice into the AP Transaction Entry form and think about the following:

   1. Fields required during manual entry will also be required when importing, so be sure that you provide for all required fields.

   2. Fields that default in the form should also default when importing.

   3. If unsure, it is usually safe to set a field to 'Use Viewpoint Default' (on the Template Detail tab) when setting up a template in IM Template.

   4. For Batch Month fields, it is often best to set the field to 'Prompt on Import' rather than rely on date fields in your file to set the batch month.

3. CSV files (comma separated values) are easier to work with within Microsoft Excel, but you should be aware of how Excel can make unintended changes in your data.

   1. Excel will strip leading zeroes from numbers - which can cause problems for Jobs, Phases, GL Accounts, etc. where leading zeroes are common.

   2. Numbers will sometimes be converted to dates or scientific formats.

   3. Make sure the file is closed in Excel when importing or you will receive an error.

   4. A .csv file is simply a text file, and can be opened with Notepad. Instead of opening the file with a double click, right-click on the file and Open with... Notepad. Notepad will never strip your leading zeroes and will display the data exactly as it will be imported; with no additional formatting applied.

4. A common problem when importing .csv files is having extra commas in your columns (cells). Vista imports will always treat commas as delimiters when the import template has "Comma" selected as its delimiter. Using quotes to escape them does not work.

   1. Open the file in Excel to Find (Ctrl-F) extra commas. If found, replace them with a space or simply remove them. Any commas that are not intended to separate a data column need to be removed, otherwise, your columns will be in the wrong positions.

   2. When reviewing a .csv file in Notepad, you can often spot extra commas because the field will be "escaped" by double quotes. (This may explain why you see quotes in imported data).

5. When importing for the first time with a new template, use fewer records in your import file. Errors are common and reducing the number of records will speed up your testing and make it easier to resolve problems.

6. If you receive errors when importing or uploading, read the full error. Computer-generated errors are not always helpful, but if you look closely you might be surprised. For example:

   1. "Error converting varchar to numeric" or "Error converting varchar to datetime" generally means the import is reading text for a field that should be a number or a date. Review the IM Work Edit program for number or date fields containing incorrect or unusual values.

   2. A stray comma in your .csv file can shift fields out of place, as referenced in section 4 above.

7. If you receive an error about "...insert duplicate key row in object...", try changing the Direct Import Type to "Update Only" on your import template.  You'll find that option on the Info tab on your import template in IM Template.