Importing a spreadsheet is one of the most useful tools in TrackVia — it lets you turn a plain old Excel spreadsheet into a TrackVia application. A few minutes spent preparing your file will prevent most import errors. Once your spreadsheet is ready, see How to Import a Spreadsheet to Create a New Table or How to Import to an Existing Table.
Before you start: Only administrators can import data into TrackVia.
File requirements
- Your file must be in .xlsx (Excel 2007 or newer) or .csv format. Other file types such as .xls or .mdb will not import and must be converted first. You can also copy and paste data directly into the import wizard, as long as it's comma- or tab-separated.
- The file cannot be larger than 40 MB. If your file is bigger, split it into smaller files and run multiple imports.
- In a workbook with multiple worksheets, only the first (leftmost) worksheet is imported. To import several worksheets from one workbook: move the desired worksheet to the leftmost position, re-save the file, import, and repeat as needed.
Structure requirements
- The spreadsheet must be a single, "flat" table with one — and only one — header row. Remove any merged cells, groups, totals, and subtotals.
- The header row becomes the field names of the new table. When importing to create an app, the header row is the first row in your spreadsheet where each column contains data.
- Cell formatting (colors, borders, number formats, date formats, etc.) is ignored — only the cell values are imported.
Include the Record ID
Every TrackVia table has a Record ID — the field, or combination of fields, that uniquely identifies each record. TrackVia uses the Record ID to match rows in your file to records in the table, so your spreadsheet must contain a column for every component of the Record ID. To learn more, see How to create a unique ID.
- Auto Counters are calculated by TrackVia. If you are adding records, leave the Auto Counter column blank (or omit it). If you are updating existing records, the Auto Counter column must contain the correct values as they appear in TrackVia so rows can be matched.
Linking to a parent table (relationship fields)
If the table you're importing into is linked to a parent table, your spreadsheet must also contain all components of the parent table's Record ID as additional columns. TrackVia uses these values to link each imported record to the right parent. See How to Import to an Existing Table to learn how to map relationship fields during the import.
Example: The Company Region table is a child of the Locations parent table. The Locations table's Record ID is composed of City and State:
To link each Company Region record to its parent Location, the import file must therefore include both a City column and a State column:
Field formatting
- Date fields must be in the format mm/dd/yyyy.
- Date/Time fields must be in the military time (24 hour) format mm/dd/yyyy hh:mm.
- Checkbox fields: if a cell contains multiple checkbox options, separate them with commas (e.g., Option 1, Option 2). Each value must exactly match an option configured on the field.
- Location fields must be latitude and longitude in decimal degrees, e.g., 39.7392, -104.9903. [VERIFY: single "lat, long" column vs. two separate columns]
- Calculated and triggered fields are not imported. They populate automatically after the import completes.
Comments
0 comments
Please sign in to leave a comment.