Data Import

If you have a lot of data stored in a spreadsheet, you can use the data import process to add it into Smartabase.

Data can be imported into an event, profile or database form using a CSV (comma separated value) file. CSV files store data in a tabular format with each value separated by a comma or semicolon, depending on your region. These can be created or viewed in software such as Microsoft Excel or text editors like Notepad and TextEdit. Smartabase will not accept other file types when importing data - you must use a CSV file.

This method can be used for many records but only for one form at a time. It is very important that your data is formatted in a way that's consistent with how Smartabase will use it.

It is possible for a non-professional user like an athlete to import records for themselves, but generally only professional users (coaches, supervisors, staff, etc.) will use the import tool. Anyone who wants to import event data will need to have the system permission called Import data in their role, or Import profile data for importing data into profile forms. No specific system permission is needed to import into a database, but you need at least Write data permission to the database form, as well as to a form which references the database.

This article explains how to:

• Import data into an event form.
• Import data into forms with tables.
• Import data into forms with Multiple selection fields.
• Import data into forms with body area fields.
• Import data into forms with OSICS diagram fields.
• Import data into forms with Entered by and Entered on calculations.

This article also describes the steps to import event data into Smartabase.

Import data into an event form

In the example above, we’re importing data into a blood pressure event form. Remember that you can import data for one person or many people. But to keep this example simple, we have data on blood pressure for just one person (Jamie Anderson) over a period of time.

The import data tool allows you to specify which event form you want to import data into, shows you the fields that are available in this form and lets you generate an Excel template if you need one.

The template includes all available fields, which are the ones you can manually enter data into - calculation fields aren’t included as available fields. The template also includes all of the options for any option fields and, if set, the minimum and maximum values for numeric fields. The option lists are for reference only and you should clear them before you upload the file to Smartabase.

The template for the form you select can be viewed and filled out using Excel. Smartabase provides the template to help you make sure that the data is correctly formatted. You can also import your own CSV file instead of using a template, but you'll need to manually map any columns to the appropriate Smartabase fields if your column headers don't exactly match the form's fields. Once you've entered your data into the CSV file, it can be uploaded to Smartabase.

A helpful way to check how a template should look when it is filled in is by downloading a report and looking at how the existing data for the event form is structured.

When adding event data to an import file, it's important to sort the data in chronological order (i.e. oldest at the top, newest at the bottom). This is particularly important for event forms that contain history calculations. When Smartabase imports the data, each record will check for historical data to determine the correct values for any history calculations, so importing in reverse-chronological order takes more time to complete.

Smartabase will match the person in the import file with an person's user account in Smartabase and check that the data in the import file is correctly mapped and formatted before the data is imported. If you haven't used a template and your column names aren’t the same as the relevant form fields, you can manually map any columns on this page.

When importing data, it's important to make sure the date format used in your CSV file is mapped correctly. Beside the Date field (and any other date fields in your form) on the data mapping page, you can search for the format that matches the format in your file. To confirm the data will map correctly, you can Test to check that the values convert to the correct dates with the selected format.

In cases where you have exported data from Smartabase, be aware that if you open the file in Excel, it may automatically reformat some date columns to a different format than the raw export. Be sure to check over your data and test the date formats carefully to ensure these date fields import correctly.

If your import file contains records that already exist in Smartabase, you can choose to import them by ticking the box on the mapping page to Import data that appears to be a duplicate of an existing record. If this box is not ticked, the duplicate records will be skipped during the import process.

If Key fields are enabled in the form, records that already exist in Smartabase will have a unique, composite key made up of the user ID, the form ID and any Key field values. Any imported records that have the same composite key as a record that already exists in Smartabase will be considered a duplicate record. These duplicate records will be imported into Smartabase but will override any data saved in the original record. This may be useful if you need to update a large number of records in Smartabase without deleting the existing records or creating duplicates.

Before the data is imported, Smartabase gives you the option to trigger any performance alerts based on the form. This is an important consideration because importing a lot of historical data may trigger many unnecessary alerts.

Finally, Smartabase will confirm how many new records have been created from your import file.

Import data into forms with tables

Forms that have one or more tables can be challenging to import into. If you’re importing data into a form with tables, there are a few additional checks to make sure your data imports correctly. In your CSV import template, rows of data will represent rows of the table, so you need to help Smartabase to understand which rows belong in which record.

• Athlete, date and time: regardless of whether a particular data row represents an entire record or a single table row, any row in your file that is part of the same record must have the same value in the Athlete (or ‘About’) and Date columns. If you are importing into the Time field, this must also match in each row. If these values don't match, the rows won’t be treated as the same record.
• Row ordering: rows that are part of the same record must be grouped together in your file. Even if those rows have the same athlete, date and time, if there are other rows between them they will import as separate records. The rows within a record will also import in the order that they’re listed in the file, so make sure you’ve added them in the right order.
• Column ordering: it’s also important for the columns representing table fields to be in the correct order in your import file. If you’ve generated a template from Smartabase, this will automatically be the case. If your data columns are in the wrong order, it may cause records to be split incorrectly and inaccurate table calculation results.
• Before finalizing an import containing tabular data, you must treat all records for the same user, on the same day as a single record. You will find this setting at the bottom of the page where you map your columns to the Smartabase fields. If this setting isn’t ticked, each row will import as a unique record.

Import data into forms with multiple selection fields

A multiple selection field is a field type that allows you to choose more than one option from a list of values. When this type of information is exported from Smartabase, it will not be in the same format as it needs to be if you want to import it. Specifically, values will be separated by commas and contained within square brackets.

In the export example below, the Symptoms of illness column references a multiple selection field.

To import into this field, values must instead be separated by the vertical bar ('|') symbol. You can convert this quite easily from the export above using Excel’s replace all feature to remove the brackets and replace commas with vertical bars. If you’re importing from scratch, you’ll simply need to make sure your multiple selection columns are formatted this way.

In the import example below, the Symptoms of illness column will successfully import into Smartabase.

If you import into a multiple selection field with the wrong formatting, the information may still display in Smartabase reports. However, it will not technically be saved to the form and will disappear as soon as you open or save the record.

Import data into forms with body area fields

Before importing data into a form containing a simple body area or body area summary field, you must first format the data in Excel.

You can export body area data as a CSV file in Excel from the reports tool in Smartabase. Once exported to Excel, the body area data appears in a single row. If multiple body areas are selected, they will be separated by semicolons. Before importing the data back into Smartabase, you need to replace the semicolons with vertical bars (the pipe symbol). Make sure you don’t leave any spaces either side of the vertical bar, as this will cause the import to fail.

Modifying exported body area summary data for import

The exported body area data in Excel contains semicolons separating each body area. Smartabase cannot distinguish between each body area if the semicolons remain on import.

Ensure there is a vertical bar between each body area to separate the values before importing the data. The vertical bars ensure that each body area imported into Smartabase will appear on the body area diagram in the form.

Import data into forms with OSICS diagrams

When importing into any type of OSICS diagram field, it’s essential to have your injury or illness data formatted correctly. When viewed in the Smartabase reports tool, injury values are displayed with commas between the injury side and body area, the OSICS code, and the diagnosis of the injury.

However, when you export this data from Smartabase, it will not contain these commas. If you’re re-importing data that was exported from Smartabase, or adding new data from elsewhere, it must be formatted precisely as [Side] [Aspect] [Body Area] [OSICS Code] [Diagnosis]. Each of these values must exactly match data from the OSICS diagram field. Certain selections will not include a side or aspect (such as lumbar spine or knee), in which case these values must be omitted.

If you want to import into an OSICS diagram with multiple injuries selected, each injury will need to be specified on a separate row of the import file. Note that this is different to other body area diagrams, where multiple selections are separated by commas or vertical bars.

Importing into an OSICS diagram field can be quite complex and often involves sensitive data which must be managed carefully. Therefore we recommend that you contact your Smartabase consultant to help guide this process.

Import data into forms with Entered by and Entered on calculations

The Entered by calculation records the person responsible for creating and saving a record. The Entered on calculation records when a record was first saved. The results of both of these calculations are associated with the process of creating a record. When you import data into with these types of calculations you are creating new records, which affects how these calculations work.

If you import a record into an event form containing an Entered by calculation, your name will appear in the calculation result. However, it is possible to import the names of other people into an Entered by field. Since the Entered by field is a calculation it does not appear as a column when you generate a CSV template for an event form. Instead, you can manually add it as a column to the CSV file. When you do this, you must enter the name of each person who should appear in the Entered by field for each record. When you import this data, the name you added to the import file will override your name and appear as the result of the Entered by calculation in the imported records.

It is not possible to import a different date value into an Entered on field. If you import a record into an event form containing an Entered on calculation, the date of the import will appear as the calculation result. This is important to be aware of if you have imported a record that has an event date which is different to the import date. 

Results of importing data into an Entered By field

Each Smartabase record has metadata, including the user account last responsible for saving the record (By). It is important to remember that, in contrast, an Entered by field displays the name of the person who created a record and it does not change if the record is edited by someone else later on. When a record is imported into Smartabase, the person who imported the data will be shown in the By column as they were the last person to save the record. If the imported record contains an Entered by field and a value is not imported for that field, the person who imported the data will be listed there too, as the record creator. As such, it may be important to include the actual record creator in the imported data set and ensure that it is correctly mapped to the Entered by field.

Steps to import event data

1. Sign in to Smartabase to view the home page.
2. Using the navigation bar or the button on your home screen, select the Import data option.
3. Select the event form that you want import data into.
4. Click the Generate template button.
5. Use Excel to open the CSV file that has been generated.
   1. Fill in the template according to how many records you want to import.
   2. Ensure that the first name and last name columns are consistent with the account information of the people you want to import data for.
   3. Add the date and time columns using consistent formatting.
   4. Order the data in chronological order, with the oldest record at the top and newest at the bottom.
   5. Check that you have filled in all required fields as the import will fail if these are empty.
   6. Ensure that the data you are entering is consistent with the field type for the column.
      • For option fields, use the options provided in the template.
      • For numeric fields, ensure the values you enter use the correct units of measurement and are within the expected range of values for each variable.
6. Save this file, keeping it in CSV format.
7. Return to the Import data tool in Smartabase.
8. Select Choose file and pick the file you have just saved.
9. Click the Upload button.
10. If the people in the file are not automatically matched to their usernames, match the users by either:
    • Typing their name in the Athlete to import data for box; or
    • Selecting Add identifier to choose a column from the file that can be used to match users to usernames.
11. Click Next.
12. View the preview of the mapped upload data to confirm that the column names in the uploaded data match the available fields in the event form.
13. Confirm that the date formatting is correct and select Date test to double check.
14. Click Next.
15. Map any options in the uploaded data that have not been recognized automatically.
16. Choose whether to disable performance alerts which may be triggered when the imported data is saved.
17. Click Next.

Once the progress bar has completed, you should see a message saying “X out of X records successfully imported”, where X is the number of records in the uploaded data.

If any records were not imported successfully, there will be an explanation for why and you can choose the option to download a log file identifying records that failed to import and the reason why.