Import Data

Tool

Import data

Description

Import an Excel spreadsheet to:

• Create a new table (field names and types, and records)
• Create new database records in an existing table
• Update existing database records in an existing table
• Intelligently create and/or update records in an existing table

Please click here for an in-depth video demo of this feature, and/or review these helpful tips.

Notes

When it comes to the ability to work with multiple records at once, TrackVia's Excel integration is a powerful tool. Not only can TrackVia populate a new table with records from a spreadsheet, it can "auto-discover" the data model (appropriate field types and field names). Similarly, this feature enables you to use Excel's data editing features to update thousands of records quickly before bringing them back into TrackVia to apply the updates to the database records.  However, along with this power comes the risk of applying errant changes to thousands of records. Please be extremely careful when importing a spreadsheet!

Spreadsheet Requirements

Should your spreadsheet not meet these requirements, TrackVia will import it but you may see unexpected results.  If this occurs upon creating a new table, please delete the TrackVia table, adjust your spreadsheet per the requirements above, and then import it again.

When importing a spreadsheet to update records, DO NOT ignore any of the warnings TrackVia provides. Please carefully read each warning and decide how to proceed. Once you click to confirm the import, it cannot be canceled. You can navigate away from the page, but the import process will continue to run on TrackVia's servers in the background. The only options to correct data mistakes caused by errant imports are to correct the records by hand (time-consuming) or to pay for a data restore (expensive, and time consuming for our developers).

Create a new table
When you import an Excel spreadsheet to create a new table:

• TrackVia automatically creates the data model (field types and field names)
• The rows in the spreadsheet are populated as database records

After the table is created you may want to change one or more of the field types in the data model. For example, you may want to convert a drop-down list into a checkbox. When you do this TrackVia will ensure you're aware of any effects of a conversion (potential impacts on your existing records, etc.).

Create new database records in an existing table
When importing a spreadsheet to add records to an existing table you will have the option of mapping spreadsheet columns to table fields. That is, for each column in your spreadsheet, you can choose which TrackVia table field the data in that column should be inserted into.You cannot import data from Excel into a TrackVia document field, image field, or calculated field. In addition, before importing your new records, TrackVia will compare the data in each spreadsheet column to the type of field you have mapped it to. You will be warned of any problems. For example, if you are importing long sentences into a short answer field, only the first 200 characters will be imported. Similarly, if you are importing values into a TrackVia email address field, you will be warned if any invalid email addresses are about to be imported. For more on how to resolve these problems, see below.

Update existing database records in an existing table
TrackVia's Import Data tool can also be used to update existing records in your table. When you do this, you can specify how existing records in your table will be matched up with the data in your spreadsheet. For example, you could specify that existing records should be identified by the Customer Number field, or by the combination of the First Name and Last Name fields. You also can specify whether non-matching records in the spreadsheet should be ignored, or should be added to the table as new records.

Intelligently create and/or update records in an existing table
This is a "hybrid" option that tells TrackVia to look for matches to update existing records, and if no match is found then create that spreadsheet row as a new database record.

Convert field types
When adding or updating records in an existing table, TrackVia may warn you that some of the spreadsheet data cannot be imported into the TrackVia field you've chosen. The two most common problems of this nature are:

1. Unrecognized values going into a TrackVia drop-down or checkbox field.
2. Unrecognized dates going into a TrackVia date field.

The first problem would happen if you're importing a value of green into a drop-down field that only has defined choices of red, white, and blue. Check the spelling of the values in your spreadsheet (and note that capitalization must be consistent), and add any new values you need to the table drop-down field using the edit table page.The second problem can happen even when the data in your spreadsheet looks like valid dates. This is because Excel sometimes stores values like 03/25/2005 as text, rather than as intrinsic dates. It can be hard to determine when this is happening.The best fix is to use Excel's Text to Columns wizard. Here's how:

1. Select the Excel column you're having difficulty with by clicking on the column header (for example, the "D" above column D in Excel).
2. Under the Data menu in Excel, select Text to Columns.
3. The first two steps of the wizard specify whether your data is delimited, and by what character. You can usually skip through these steps by clicking Next.
4. The third (and final) wizard step is the most important: specifying the new format for your data. Choose the "Date" option, and make sure the "MDY" drop-down matches the format of the dates in your spreadsheet. (For example, "MDY" means month-day-year.)
5. When you click Finish, Excel will convert your text-based dates into real dates. TrackVia will then be able to load the data into a TrackVia date field.

For problems or questions about importing data into TrackVia, feel free to contact TrackVia Support at support@trackvia.com.