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. 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.
EXAMPLE: IMPORTING DATA TO A BLOOD PRESSURE EVENT FORM
In this example, we’re importing data into a blood pressure event form (shown above). Remember that you can import data for one athlete or many athletes. But to keep this example simple, we have data on blood pressure for just one athlete (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 questions 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 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 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.
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 athlete in the import file with an athlete’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.
As 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.
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.
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.
TIP: 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.
IMPORTING 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 finalising 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.
IMPORTING DATA INTO FORMS WITH BODY AREA FIELDS
You can export body area data as a CSV file in Excel from the reports module 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.
EXAMPLE: 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.
EXAMPLE: IMPORTING 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 module, 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 a Fusion Sport representative to help guide this process.
IMPORTING DATA INTO FORMS WITH ENTERED BY AND ENTERED ON CALCULATIONS
Before importing data into a form containing an Entered By or Entered On calculation, there are some important things to consider.
The Entered By calculation records the user 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 type 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 users 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.
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 different which is different to the import date. It’s not possible to import a different date value into an Entered On field.
EXAMPLE: RESULTS OF IMPORTING DATA INTO AN ENTERED BY FIELD
Each Smartabase record has metadata. The metadata for a record includes:
- The date of the record.
- The user account for whom the record was created.
- The user account responsible for the record’s creation.
This data appears in the reports table under the Date, About, and By columns. It is important to remember that the By information for a record can be different to result of an Entered By calculation when you’ve imported data into an Entered By field.
This example shows a report run on a purchase order event form. We created this form so that coaches can track orders for athlete’s training gear requests. However, we wanted to add data into this form that we had stored in a spreadsheet, so we imported the data shown above.
When we run a report on the purchase order form, the About column displays the athlete who the order is for. The name of the person responsible for importing the records appears in the By column. The Entered By column shows the person who made the original request for the athlete.
Notice that the person in the By column is not the same person shown in the Entered By column for the most recent three records. The person in the Entered By column created the record for the athlete. In contrast, the person in the By column imported the record into Smartabase.
HOW TO: IMPORT EVENT DATA
- Sign in to Smartabase to view the home page.
- Using the navigation bar or the button on your home screen, select the Import Data option.
- Select the event form that you want import data into.
- Click the Generate Template button.
- Use Excel to open the CSV file that has been generated.
- Fill in the template according to how many records you want to import.
- Ensure that the first name and last name columns are consistent with the account information of the users you want to import data for.
- Add the date and time columns using consistent formatting.
- Order the data in chronological order, with the oldest record at the top and newest at the bottom.
- Check that you have filled in all required fields as the import will fail if these are empty.
- Ensure that the data you are entering is consistent with the question type for the column.
- For option questions, use the options provided in the template.
- For numeric questions, ensure the values you enter use the correct units of measurement and are within the expected range of values for each variable.
- Save this file, keeping it in CSV format.
- Return to the Import Data tool in Smartabase.
- Select Choose File and pick the file you have just saved.
- Click the Upload button.
- If the users in the file are not automatically matched to their usernames, match the users: 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.
- Typing their name in the Athlete To Import Data For box, or
- Click Next.
- 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.
- Confirm that the date formatting is correct and select Date Test to double check.
- Click Next.
- Map any options in the uploaded data that have not been recognised automatically.
- Choose whether to disable performance alerts which may be triggered when the imported data is saved.
- 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.