This article will detail the steps to correctly format your .CSV user files for transfer. Correct formatting is imperative to having a completely functional platform. Incorrect formatting can result in long delays and in severe cases can cripple your platform functionality. Here's a user file template for your reference.
- Format
- Headers and Fields Formatting - Minimum Required
- Headers and Fields Formatting - Optional
- Notes on Required and Optional Fields
- Regional Settings for CSV Files
Format
Whether you send your user file through a manual drop-off or automated transfer through SFTP or API, you will need to format your user file according to the following guidelines:
- Files must be formatted as a .CSV
- If your users have special characters in their names, such as "ü" or "ç", the file must be sent in a CSV UTF-8 format.
-
Complete user files must be uploaded in full each time.
- This ensures that the Awardco platform can account for terminated employees and reporting hierarchies.
- Column headers from your HRIS reports must match the exact text found in the sample .CSV below.
Headers and Fields Formatting - Minimum Required
| Employee Id | A unique identifier for each employee and the primary login credential. Typically a number combination but could be email or letters if no other unique identifier exists. (Note: ID's are case sensitive and but have a max character limit of 100) |
04686 2749218 FLast@company.com |
| First Name | Employee’s first name. Limit is 100 characters. | James |
| Last Name | Employee’s last name. Limit is 50 characters. | Hendricks |
| The employee’s unique email address. This can be modified by the employee, used as a login credential and primary method of communication. While the system will not throw an error if this field is missing, it is a required field for a seamless user experience. Limit is 100 characters. | FLast@company.com |
Headers and Fields Formatting - Optional
* - strongly recommend as included field
| Preferred First Name | The Preferred First Name will show if available on all employee facing features of the platform (including emails). The Admin side of the platform will continue to use the First Name column. Limit is 100 characters. | Jimmy |
| Secondary Email | The employee’s personal, non-work email address. This can be modified by the employee. If you are using the Point Auto Redemption feature, this is the email where the prepaid card will be sent when the employee is terminated. This field does not need to be filled out if your company is not using the Point Auto Redemption feature. Limit is 100 characters. | FirstLast21@gmail.com |
| Phone Number | The employee's work or personal phone number. If you have difficulty entering the phone number in the correct format (+11234567890), speak with your Awardco point of contact. | +11234567890 |
| Username | Optional username designation to be used in accessing the platform. User ID and email can be used to access the platform so username is rarely needed. Limit is 100 characters. | FLast FirstLast FL0730 |
| Supervisor ID* | Supervisor ID is used to build the platform hierarchy. All entries must match an ID in the Employee ID column to properly assign users to a supervisor. |
04686 2749218 FLast@company.com |
| Country Code* | ISO 2 letter country code (must be in ISO2 format) Limit is 2 characters. | US, CA, GB, FR |
| Password | Preassigned login credentials for each employee. Unnecessary when SSO is configured. | uw54gw84fs |
| Hire Date* | The date on which an anniversary recognition trigger will be made. (Use hire year, NOT current year.) | YYYY-MM-dd, MM/DD/YYYY, M/D/YYYY, DD-MMM-YY |
| Birth Date | The date the employee was born (Year is optional. It may be included, but will be trimmed) | YYYY-MM-DD, MM/DD/YYYY, M/D/YYYY, MM-DD, M-D, MM/DD, M/D, 0000-MM-DD |
| Language | The preferred language of the employee. This is important for transactional emails. Must be in a 2 letter language code format. Limit is 2 characters. | The language codes can be found in the multi-language email article. I.e. "en" for "English" |
| # | Metadata tagged by a "#" should match the same text that is generated from your user file. Limit is 200 characters for header and 200 character for the value. | #Location, #Address, #Language |
| @ | When configuring approvals for recognition programs, you can use associated metadata to link a user to other users aside from their manager. To set up an association, add a new column to the import file with the column header title beginning with an "@" symbol. Note: associated metadata won't be successful if using an existing metadata column. Please always create a new column in the user file; changing the "#" to "@" will not work. See Configuring User File Metadata for more information. | @Department VP |
| ^ | If running a corporate gifting program through Awardco (physical items like plaques, acrylics, printed certificates, numerals, or bonus boxes), shipping fields need to be included in the user file and each column will require a caret in order to process the data. Data in columns with a ^ will be uploaded to the platform, but will be hidden - the data will not be visible within the platform to admins or users. See Configuring User File Metadata for more information. | ^Supervisor First Name |
Notes on the Required and Optional Fields
-
Removing Fields: None of the following field values can be removed by importing a blank or null value in the user file; the platform will retain the existing data. The only exception is Username, which can be set to null:
- Employee ID
- First Name
- Last Name
- Preferred First Name
- Secondary Email
- Birth Date
- Hire Date
- Phone Number
- Country Code
- Email Field: While the platform can process a user file without an email column, it is considered a required field. Including an email address for each employee is critical for a functional and successful platform, as workarounds for missing email addresses can lead to a poor user experience.
- Supervisor ID: Any individual listed in any supervisor field will be assume a hierarchy of all their direct reports. Any individual not listed as a supervisor will be considered an employee. Any individual listed as a supervisor will be assumed part of a hierarchy of all their direct reports. Therefore, if the supervisor ID used for an employee is not part of the platform, that supervisor ID will be ignored. If the employee's supervisor ID is ignored, they will be assumed to have no supervisor. The user file does not error if supervisor IDs are not listed as employees.
- Passwords: If employees have email accounts, Awardco strongly suggest using SSO (single sign on) or an Login Code to access the Awardco platform. If employees do not have access SSO access or email accounts, generic usernames and unique passwords can be used to log in to the Awardco platform. Unique passwords are easily communicated values, while generally protecting the password. E.g. last four digits of SSN. Uniform Passwords are NOT suggested. Uniform passwords would include generic passwords such as “Password”, “awards”, or “Company2018”.
- Single sign on is Awardco’s preferred verification method. SSO is an authentication method that allows a user to log in with a single ID and password to any of several software systems. If SSO is being used for your company’s login method, the username and password columns can be omitted or left blank.
- Login Code is the preferred method when SSO is not available. Employees will be emailed a unique Login Code upon program launch. This unique link will grant the employee initial access and prompt them to create a password for future use. Emails are required for all users using an Login Code. The username and password columns can be omitted or left blank.
- Unique Passwords are suggested for companies unable to utilize either SSO or Login Code via Email. Unique passwords are easily communicated values, while generally protecting the password. E.g. last four digits of SSN.
- Uniform Passwords are NOT suggested. Uniform passwords would include generic passwords such as “Password”, “awards”, or “Company2018”.
- Language: Adding the preferred language of the employee is helpful when you wish to send transactional emails in a different language. The language codes can be found in the multi-language email article.
- Metadata: For a custom field to be included in the Awardco system, the column header MUST start with a ‘#’. Any additional columns added to the file without a ‘#’ on the header will be ignored by the import. Additional fields are used for reporting purposes and will be available to filter off of in reports. Values are limited to 200 characters.
- Corporate Gifting: Awardco may request additional fields for the purpose of managing corporate gifting items.
Regional Settings for CSV Files
CSV files can sometimes display incorrectly in Excel due to differences in regional settings. Specifically, the default list separator differs by region: in the US, it's a comma (,), while in many other countries, it's a semicolon (;). This can cause all data to appear in a single column when opened in Excel.
To ensure your CSV files open correctly in Excel regardless of regional settings, follow these steps:
Option 1: Change Regional Settings in Excel
- Open Excel.
- Go to File > Options > Advanced.
- Scroll down to the Editing options section.
- Check the Use system separators box (if it isn't already checked).
- Ensure that the Decimal separator is a period (.) and the Thousands separator is a comma (,).
Option 2: Change List Separator in Regional Settings (Windows)
- Open the Control Panel.
- Go to Clock and Region > Region > Additional settings....
- In the Customize Format dialog, find the List separator box and change it to a comma (,).
- Click OK to save the changes.
Option 3: Manually Changing Columns After Opening
If changing the regional settings is not preferred, you can manually specify the delimiter in Excel:
- Open Excel.
- Go to Data > From Text/CSV.
- Select your CSV file and click Import.
- In the Import Wizard, choose Delimited and click Next.
- Select Comma as the delimiter and click Finish.
Note: Manually changing columns using this method is not recommended for Awardco reports. This is because the data often includes commas and semicolons, which can cause incorrect column splits and inaccurate reports.
By following these steps, you should be able to view the CSV files correctly in Excel, ensuring smooth data management and accurate reporting.
If you have any questions, please contact Awardco Admin Support.