The system offers time-saving methods for populating your system with participant and attendance data. This can eliminate significant data entry time and reduce data entry errors.
This article describes how to import participation data directly into a site's Participation section.
Participation data can be imported for only one site at a time. You must be connected to the site for which you want to import data. To learn more about Switching Access, please see this article.
Summary of the Import Process
- When connected to the site where participation data is to be uploaded, go to Tools -> Import Data -> Participation.
- Download the template to your computer.
- You can enter participation data for this site into the downloaded template by typing in or copying and pasting from a different spreadsheet file. This can be done in any program that can open an Excel sheet and save data as an Excel or CSV file. Excel is not required.
- When done adding data to the template, return to the Import Data -> Participation page
- Click Start Import.
- On the Upload a file screen, click ⊕ in the center of the screen; browse to the template you just populated with participation data, select it, and click Open.
- The system will upload the file and analyze the data quality, looking for and displaying invalid data issues.
- Columns with invalid data issues will have red indicators, and a corresponding data issue box will appear at the right – these need to be addressed.
- When there are no more red invalid data indicators/data issue boxes, click Import. The import may take some time, depending on the amount of data in the file and how many other customers are uploading files at the same time.
- When the status “Success” displays, your participation data will be imported into the site's Participation section.
- Repeat for each site for which you want to upload participation data.
Select Tools in the left-hand navigation menu.
Then click Import Data.
Then click Participation.
Downloading the Data Template
To facilitate the data importation process, it is best to convert your data into the format of the provided data template.
To begin a new import, click the Download Template button. Save this file with a clear name to easily identify it among your other files.
Open the file on your computer using Excel or another application that can open Excel files and can save the data file in an Excel or CSV formatted file (e.g Google Sheets). Excel is not required.
Six of the ten importable data fields are mandatory: Last Name, First Name, ParticipantID, ActivityAndSession, AttendanceDate, and AttendanceStatus.
Add your participation data so it fits into the template format. You can do this by typing values into the renamed template or copying and pasting from your own spreadsheet. Be sure not to change the template column heading labels or the order of the columns.
The data in the Excel sheet doesn’t need to be perfect. For example, 'P' in AttendanceStatus can be quickly replaced with 'Present' during import. Similarly, ActivityAndSession names can be adjusted, like changing "Robotics" to "STEAM/Robotics," to match system values.
Cleaning data in the import tool can be easier than using the template. Plus, replacements (like 'P' for 'Present') are saved and applied automatically for future imports by the same user, saving time.
Uploading Your Participation Data File
On the Import Participation Data screen, click Start Import.
Once your data is ready, start by dragging your file onto the screen or by clicking +.
Select the correct file from your file explorer.
The file will get uploaded into a workspace where data errors in the file can be addressed. Depending on the file size, this may take a few minutes.
Reviewing the Uploaded File for Issues
The analyzed results and any issues found will appear on the Review & Finalize screen.
This spreadsheet-like tool can automatically fix some validation errors or let you manually edit individual cells.
At the top left of the Review & finalize screen, you'll see a summary showing the number of clean rows and rows with issues. If many rows have problems, consider editing your file in Excel and re-uploading it.
The data fields in the uploaded file are below this summary of clean rows and rows with issues. A scroll bar at the bottom of the screen allows you to move the list to the left to see additional columns hidden to the right.
The right side of the Review & Finalize screen lists all the issues. In this case, there are issues with the FirstName, ParticipantID, and AttendanceDate fields. This section helps you identify and resolve problems quickly.
Data Issue Summary Boxes
In the Issues section on the right, each field with problems is shown in a box. In this sample file, issues appear in FirstName and AttendanceDate.
These issue boxes share some common features:
- The green box displays the number of cells in the column with issues; in this case, one cell requires attention.
- The orange box indicates each instance of invalid data that needs addressing. In this case, "sally" is not an acceptable value. When hovering your mouse over the invalid data "sally," the words "See column" will appear as noted in the red box; clicking this will move the data column (in this case Firstname) into view at the left.
- The blue box highlights a red warning box that indicates the number of cells with issues. Clicking this box displays the column with the issue and takes you to the first data cell that needs fixing.
Fixing Formatting Issues
The Fix All button uses system intelligence to address issues. In this case, it will only fix the formatting error of the FirstName field where the Fix formatting errors button is showing. If other such buttons were present, the Fix All button would also attempt to fix them.
Clicking either the Fix all or Fix formatting errors button will change the lowercase "joe" to "Joe."
Fixing an Invalid Data Cell
To address invalid data, first, bring the cell with the data issue into view using the blue or red feature above or by moving the scroll bar at the bottom to the right until the column appears.
When you click on the cell, a message box will appear describing what is wrong. Read this carefully. In this example, the message is "This is an invalid value," meaning the database will only accept certain values.
The data in the cell ("seven") is not an accepted value for this data field. A red cell indicates an issue that must be addressed before the file can be imported into the system.
The message box asks, "Would you like to replace it [seven] with a valid value?" and provides a dropdown list of valid values for the grade field.
In this case, select "07" and some additional options will appear. In this case, our sample uploaded file only has one row with one invalid GradeLevel value, so we would click the Replace cell button.
If there are additional invalid values of "seven," we could use the Replace all similar cells button to quickly replace them all.
To review each invalid value and replace it individually, choose the Review all replacements button. The following screen will appear, where you can pick from the list of valid values for each cell with an issue. Click Done when finished.
After choosing a correct value for GradeLevel to replace "seven," some updates occur on the Review & finalize screen.
- The red issue button in the GradeLevel field has turned green.
- A message states, "There are no more errors in the GradeLevel column."
- The GradeLevel issue box at the right has disappeared.
You must continue addressing each issue box on the right before you can import the participant file. When all issues have been addressed, a message saying "This field is ready to be imported" will appear.
Fixing Empty Cells
As mentioned above, the ParticipantID field is required for every imported participation record. As you can see in this example, this field needs attention as it was empty in the uploaded file.
To address this, click on the red warning box, which will take you to the first problematic cell.
Per the instructions above, click in the cell to view a menu of options to address the issue: "This cell must be filled."
As this is a required field, you must enter a ParticipantID by clicking in the ParticipantID field and entering a value.
After entering a value for missing values in the ParticipantID field, some updates occur on the Review & finalize screen.
- The red issue button in the ParticipantID field has turned green with a zero. If there is more than one issue with the ParticipantID field, the red warning number would remain, but be reduced by one.
- A message states that "There are no more errors in the ParticipantID column."
- The ParticipantID issue box at the right has disappeared.
If the missing value was for a field that only allowed certain options, such as ActivityAndSession, a list of acceptable choices would appear along with some follow-up options.
After picking the correct Activity/Session value for this record, in this case 'Recreation | Daily Recreation,' choose one of the four buttons as shown below.
- Replace cell - this replaces the empty cell with 'Recreation | Daily Recreation'
- Replace all similar cells - this replaces any other empty cells for this field with 'Recreation | Daily Recreation.'
- Review all replacements - this will open a form where each empty cell can be reviewed and a Activity/Session value can be picked for each one individually (most time-consuming option).
- Use a different action - this area will rarely be used. Never select Remap this column as it will prevent the successful use of the provided template. Note these options are also available by clicking the V to the right of the column heading.
After choosing the appropriate value for Activity/Session to replace any empty cells, some updates occur on the Review & finalize screen.
- The red issue button in the ActivityAndSession field has turned green with a zero. (Note if there are more than one issue with the ActivityAndSession field, the red warning number would remain, but be reduced by one number)
- A message states that "There are no more errors in the ActivityAndSession column."
- The ActivityAndSession issue box at the right has disappeared.
Continue addressing each issue box on the right.
Importing the Fixed Data File
Once there are no more data issues, the following message will appear on the right side of the screen where the issue boxes had been previously.
Click the Import button, and the system will work on importing the finalized data into the Participation section of this site.
The Import Participation Data table will display a new row with a Status of Initiated, displaying the date and time the import was started.
To see an updated status, refresh the list by clicking Tools in your left-hand navigation menu, then Import data, and then Participation.
Possible statuses are:
- In Progress - The system has retrieved the data file and is starting to import data into the database.
- Partial Success - The import process was unable to import all of the data due to an import exception, such as the participant already being in the system or the school not being found.
- Success - The import process was completed without any errors.
- Failure - The import process failed. This could be a system error or an error in all import records because some system value/ID changed and was not visible to the user.
Contact your system administrator or Pathwise customer support for assistance with any errors.
Here is an example listing with a few different import statuses.
The Failure record above shows that 0 of 8 records/rows were imported successfully. To see the unsuccessful records, click the eye icon.
The error record's participant name, ID, and error reason will display. This will help in tracking down the issue and re-importing the record(s) or adding the participation data manually if necessary.
Click the Export Error Rows button to get the CSV file of the error rows exported to your computer if needed.
A successful data import can be verified by going into the site's Participation section. Select the activity, session, and date of imported participation. The imported people will be listed with their imported status.
To import Participation data for a different site, use the Switch Access functionality to switch to that site and then repeat the processes above.