How to Prepare a Spreadsheet for Importing

How to Prepare a Spreadsheet for Importing

Importing a spreadsheet is arguably the most useful tool within TrackVia!  It allows you to take a plain, old Excel spreadsheet and turn it into a TrackVia application. Please review the Related Import Help Articles below, after your spreadsheet is ready for an import.

Let's discuss a few things to keep in mind when preparing your spreadsheet for an import.

• Ensure that your spreadsheet is in either .xlsx (Excel 2007 or newer) or .csv format.  Other file types such as .xls or .mdb will not import and must be converted before proceeding.   
• The spreadsheet must be a single, "flat" table.  This means that there can be one, and only one, header row.  You must remove any groups, totals, and/or subtotals. 
• The header row will become the field names of the new table.  When importing to create an app, the header row will be the first row in your spreadsheet where each column contains data.

• In a spreadsheet that has multiple worksheets, only the first (leftmost) worksheet is imported.  To import multiple worksheets from one spreadsheet, successively 1) Move the desired worksheet to be leftmost, 2) Re-save the spreadsheet, 3) Import, and 4) repeat as needed.

• Cell formatting (colors, borders, number format, date format, etc.) is ignored.
• Your spreadsheet must contain all components of the Record ID.
  • Auto Counters are calculated fields. If you are Adding the data to the table, the Auto Counter column will be blank on your spreadsheet. If you are Updating existing data in the table, the Auto Counter column must contain the correct values in TrackVia.

• • Relationship Fields must also contain all components of the Parent table's Record ID. These will be additional columns on your spreadsheet. Please refer to How to Import to an Existing Table, to learn how to Map relationship fields when importing. 
    
    EX: The Company Region table is a Child table to the Locations Parent table. The Locations table Record ID is:

Therefore, in order to link to the Parent Location records, we must have all components of the Parent Record ID. Referring to the screenshot above, that means we must have a City column, and a State column in the spreadsheet. 

• Date fields must be in the format mm/dd/yyyy
• Date/Time fields must be in the format mm/dd/yyyy hh:mm (Military Time)

• Location fields must be in Lat/Long format. TrackVia utilizes a GPS format with Decimal Degrees. 

• Calculated/Triggered field do not get imported

Now that your spreadsheet is ready to be imported, let's take a look at the first way to accomplish this; importing it as a New application!

Step 2: Select Start using an Excel Spreadsheet

Step 3: Give your new application a name.

Step 4: Upload your Excel/CSV file, or you can Copy/Paste the data directly in the box provided.