Data Import

Locating the Data Import feature

Anyone in a Role with the Validate Data Import privilege or the Run Data Import privilege can use the Data Import feature by opening the ADMIN menu and clicking on Data Import:
 

Figure 1: ADMIN >> Data Import

Steps to Complete a Data Import

Step 1: Creating a Data Import File
PowerClerk allows you to run a Report and download a .csv file with project data. Similarly, you can turnaround and utilize that -same- .csv file to import project data into PowerClerk. The .csv file will need to include certain column headers that are formatted in the way PowerClerk expects to receive information. The .csv file should be formatted so that the first row is the column header and each subsequent row is a different project’s data.
 
First, if you are modifying a project’s data field values, identify the data fields and their template tags. Navigate to Program Design>>Data Fields and select the data field you wish to include in your .csv file. The template tag will be exposed for you to copy/paste into your .csv file as a column header. Please see more information in the “Column Header Types” section, below.
 

Figure 2: Program Design >> Data Fields. To locate Template Tags

Figure 3: .csv file with Data Field column headers

Step 2: Validating a Data Import File
To validate a Data Import .csv file, the user must have the “Validate Data Import” privilege turned on for their Role. The successive privilege, called “Run Data Import”, gives users the ability to complete data imports that modify existing projects.
 
When you are ready to import your .csv file, navigate to the Data Import page. Select whether you wish to “Modify existing projects” or “Create new projects” and then upload the file.
 
The identify column is the column in your .csv file that PowerClerk will use to identify which project to target for each row of data. You may select whether you wish to put all projects included in the .csv file into a specific status. You may also select whether the data import file is meant to target a form. Please see the “Data Import Configurations” section, below, for more information.
 
Once you have your parameters set up, you will click the blue “Validate” button to process the .csv file. Validating is a required step prior to importing the data. PowerClerk will verify that the column headers are correct, and that the information is properly formatted based on the data field type.
 
If the validation returns no errors, you will see a green “Validation Succeeded” message from PowerClerk.
 
If the validation returns errors, PowerClerk will display which rows or columns have errors so you can easily correct the .csv file and the associated formatting. Once corrected, you will need to remove the originally uploaded file, re-upload the newly saved file, and click the “Validate” button again.
 

Figure 4: Example of Successful Validation

Step 3: Performing a Data Import
If you are modifying existing project data, and your Role has the “Run Data Import” privilege, you may perform the data import after a successful validation by clicking the “Import Data” button.
 
To create new projects in your Production environment, you will need to send a Support Request to the PowerClerk Support Team to process your import. Within a Test Environment, you can run a data import to create new projects. Please see more information in the “Data Import Configurations” section, below.
 
To send communications in bulk via a data import, you will need to send a Support Request to the PowerClerk Support Team to process your import. Please see more information in the “Column Header Types” section, below.
 
Data imported into the Test Environment will be deleted when the Test Environment is committed or discarded. By importing your own data into a Test Environment, this allows you create test projects. For the security and privacy of your customers, we strongly recommend that you do not import real customer data into non-production environments (i.e. Sandbox aka PCITrial).
 

Figure 5: Example of Successful Import

Data Import Configurations

Modify Existing Projects
If your data import is targeting existing projects, you may be able to validate and run the import yourself*. You can choose to Change Status or leave projects in their current status. You may also choose to Target a Form.
 
*Your Role must allow for validating and running data imports. To complete a bulk communication import, create a ticket in the PowerClerk Ticket System.
 
Create New Projects
In a Test Environment, you can create new projects on your own. In your live Production Environment, a CPR Admin must perform the import for you. To do so, create a ticket in the PowerClerk Ticket System.
 
There are some considerations to create new project(s) via data import:

1. The Data Import must target a Form.
2. The Data Import must either Change Status via the Data Import page or include the “{CurrentStatus}” tag in the .csv file.
3. The Project Owner should be defined (see the “Column Header Types” section, below).

New Projects have different options for project numbers.

1. Follow the next sequential project number, based on the newest project number that currently exists in your program. In this case, do not assign project numbers in the data rows within your .csv file and PowerClerk will automatically assign sequential project numbers.
2. Have their own prefix. For example, legacy projects may choose to have the prefix “HIST” to differentiate between non-legacy projects. Alternatively, you can maintain the same prefix as the other projects in your program, but assign legacy project numbers to a completely different data field you’ve created in PowerClerk. Surface that legacy data field on the Project List Columns, next to PowerClerk’s existing “Project Number” column on the Home Page.
3. You may have opted to reserve a certain subset of project numbers when your program launched for legacy projects. For example, you may have started your program at Project # INT-00200 and reserved the first 200 projects for legacy projects. In this case, you would start your 200 project legacy data import file at INT-00001.

To streamline the creation of -legacy- projects, we suggest creating a (backend) Form with a singular checkbox and setting up a .csv file that targets this form. For example, the checkbox could be “Is Legacy”, as this helps one to identify (and report on) legacy-specific projects in the future. Once the CPR Admin has created these projects via data import, you will be able to ‘modify existing projects’ and import the project data associated with these newly created projects without being constrained to a specific Form’s data fields. This will also keep data more secure as you will not need to pass a file containing PII to CPR-Admin.
 
Please do not send personal identifiable information (PII) through email. If required, we will provide a secure link to upload the file.
 
Change Status
If you wish to change the status of all the projects included in the file through the Data Import page, select the status you wish the projects to enter. The date that the data import is performed will be the Status Change date for these projects when reporting.
 
When the imported projects are moved into the target status, any automations triggered by that status change will run. This includes automations* triggered by projects exiting their current status, transitioning between specific statuses, and entering the target status. If you would like the automations to be ignored, you will need to include the status change in the .csv file instead of through the Data Import page (see the “Column Header Types” section, below).
 
*Automations will be processed, but any “Send Communication” action rules will be ignored.
 

Figure 6: Data Import Changing Status

 
Target Form
You can configure a data import to submit a form for every project in the import file. We recommend choosing the simplest form possible for your import. There are a few special restrictions on which forms are eligible to be submitted by a data import:

• The form cannot have any conditionally visibility configured
• The form cannot have any elements with eSignatures configured
• The form cannot have ePayments enabled
• Any required fields on the target form must appear as columns in your import file
• Any read-only fields on the form cannot appear as columns in your import file

When PowerClerk processes each project (i.e. row) in your import file, it first saves any imported data, then attempts to submit the target form, processes any automations associated with that form submission*, and finally changes the project status (if configured). If the form submission automation contains a validation rule that fails for a given project, the project’s data will still be updated based on the values in the import file, but the form submission and any associated automations will not occur.
 
If you specify a target status for imported projects in addition to a target form, that status change will occur after the form has been submitted for the project. Therefore, if the form submission triggers an automation which changes the project status, you do not need to additionally select the desired status, and you can leave the selector at the default value: “- None – leave in present status”.
 
When targeting a specific Form with your data import, that submitted form will show in the Previous Forms section under the View/Edit screen.
 
If you have any concerns about these options, please submit a ticket to the PowerClerk Ticket System and we will investigate your import scenario before finalizing your data import. If you select a target status for projects processed by the import, any automations associated with the status change will occur in addition to any automations associated with the form submission.*
 
*Automations will be processed, but any “Send Communication” action rules will be ignored.
 
Please note: to import into a specific form, PowerClerk will require you to provide *all* data fields inside your .csv file that are found on that form, otherwise the import validation will notify you that a required field is missing from your import file. Even if you are running an import to modify existing projects and the project has existing data for all the required fields, every required field on your target form must be explicitly specified by a column in the import file. This is another reason we recommend using the simplest form possible as a target for data imports.
 

Figure 7: Successful Data Import Targeting Form

Column Header Types

Project Number: Most often, the Project Number is utilized as the identifying column for PowerClerk to target a project for each row of the .csv file. However, there other options, as described below.
 
Data Fields: Data Fields must be formatted in the way that PowerClerk is expected to receive the information. For example, if the data field is an “Integer” type, then the .csv file column will accept digits only (not letters). The template tags for the data fields can be found on the Data Fields page to use as column headers (refer to Figure 2, above).

• Text Data Fields: freeform text values
• Numeric Data Fields: numeric values as expected
• Multiple Choice & Drop-Down: values need to match the Choice values exactly and are case-sensitive
• Date Fields: values should follow short format MM/DD/YYYY for programs in English language
• User Selector: value should be the user’s email address
• Please note the PV System and ESS Form Elements cannot be imported into. Use PowerClerk’s API instead; specifically the SetProjectData method reference.

Tables: A .csv file can be set up to add rows to a Table Data Field. Only one row per project may be targeted within the same Data Import, which means each import can only add one row to the table.
 
Each column of the table has its own data field template tag, which follows this schema:

• {data: <TableName> <ColumnName>}

For more information on tables, please visit our Data Fields page.
 
Note: If you require the ability to modify existing table rows or add more than one row at a time, you may utilize the PowerClerk API.
 
Status Changes: The current status of a project can be selected in the data import UI configuration menu or included within the .csv file itself. To include the Current Status in the .csv file, utilize the following column headers:

• {CurrentStatus}
• {status_date: StatusName} 

If you have different projects going into multiple statuses, you will need a status date column for each defined status.
 
Note: When the target status for imported projects is specified with a {CurrentStatus} column, no automations will be triggered by the status change. If you would like the automations to run, select the current status through the data import UI configuration menu instead (see section above).
 
Project Owner Change: You can reassign the project owner of projects via a data import. Please ensure that the new project owner is a user registered to your program. If the user is not currently registered to your program, you can add them under Admin>>User Administration.

• {ProjectOwnerFirstName}
• {ProjectOwnerLastName} 
• {ProjectOwnerEmail}

Note: If the user is not registered to your program, the Project Owner will still be assigned and PowerClerk will create a new account for the new PO, generate a random password for their account, and assign the user a non-Admin role in your program. Carefully consider this experience when changing the project owner of projects.
 
Sending Communications in Bulk: To trigger sending a Communication in bulk, across a subset of projects via Data Import, please format your import .csv file as shown in the example below.
 
You will need to set up the .csv file such that it successfully validates. Then, please reach out to PowerClerk Support to request the import of the bulk communication .csv file. Please do not send personal identifiable information (PII) through email. If required, we will provide a secure link to upload the file.
 

Figure 8: Bulk Communications via Data Import

 
Deadlines: You may SET or SATISFY a deadline via data import.
 
To satisfy a deadline, please format your import .csv file as shown in the example below, utilizing the tag {deadline: DeadlineName} and “satisfy” in the project rows: 
 

Figure 9: Satisfying Deadlines with a Data Import

 
To set a deadline and its expiration date, please format your import .csv file as shown in the example below:  
 

Figure 10: Setting Deadlines with a Data Import

 
You can choose to set an expiration date of your choice or utilize the keyword “default” to set the default deadline expiration date as configured in Program Design>>Deadlines. Set deadline expiration dates must be a future date as past dates would have expired already.
 
Please note: to avoid setting duplicate Deadlines you may want to consider configuring the Deadline option “Allow only one instance for each project” option under Program Design>>Deadlines, or ensure that you satisfy existing deadlines first before setting a new deadline.

 

Automatic Data Imports via SFTP

PowerClerk can automatically data import a .csv file if you can design a way to drop said file into CPR’s Secure File Transfer Protocol (SFTP) site hosted in Amazon Web Services (AWS). The .csv file will need to be formatted in the same way manual data import .csv files are. You may choose to target a form or change a project status. If you target a form, the same rules apply, and the automations associated with the form submission will run.
 
Watch our Video Guide on SFTP Automatic Data Import’s below.
 
PowerClerk will attempt to import the .csv file within an hour of it being placed in your designated program folder. If the .csv file was successfully imported, the file will be removed from the folder. You may check for a successful import by navigating to the Data Import History tab:
 

Figure 11: Data Import History Tab

 
The ‘user’ that performs automatic data imports utilizes the alias “PowerClerk Internal Processing” within the PowerClerk UI.
 
An automatic data import may fail to process for one of three reasons: the validation failed, the data import failed, or the file was unprocessed*. In the case that a file fails to process, PowerClerk will rename the file with the issue suffixed (I.e., failed validation). Your Support Alias (Program Design>>Program Info) will receive an email notification and you will be able to navigate to your designated SFTP folder to review your file.
 
*PowerClerk will not accept a file whose last write timestamp was more than 24 hours prior to the import scan. For example, a file may be in the queue for longer than 24 hours if other files are being processed to that program. Our policy safeguards projects from receiving outdated and no-longer-valid data as you may have uploaded a newer file with updated information to be imported as well. If this happens, PowerClerk will rename the file with the suffix “unprocessed” and your support alias will be notified.
 

Figure 12: PROGRAM DESIGN >> Program Info Support Alias

 
If you would like to set up an automatic data import for your program, please submit a ticket to the PowerClerk Ticket System.

Video Guides

Data Import

This video guide will show you an introduction to automatic data import via SFTP.

 
A full list of all Video Guides can also be found here.

FAQs

Q: Do blank cells within my Data Import Excel sheet erase or overwrite existing data because they are blank?

A: Your .csv file can contain empty cells, which will not overwrite or erase existing data. To intentionally remove actual data field values you should denote the respective cell value as {null} (the word “null” in curly braces).

Q: I'm trying to import into a drop-down or multiple-choice Data Field to set the choice value to "Yes". My import fails, but I verified that my import cells say "yes". Why is it failing?

A: Please note that drop-down and multiple-choice Data Fields are configured by defining choice values and their accompanying choice labels, which means while you may wish to provide more context to your applicants by adding a more descriptive *choice label*, the *choice value* itself can be a more shorthand version. See the following example:

Choice value: Yes
Accompanying choice label: "Yes, please sign me up to receive further communication about this process"

In your reports this drop-down field would be emitted as "Yes" and also please note that when importing into such drop-down field, you would have to match the *choice value exactly* meaning importing a lowercase "yes" would fail, and only a proper choice value match of "Yes" will achieve the desired outcome of the import.

Q: Can I automate a Data Import?

PowerClerk offers Automated Data Imports via SFTP, and the steps below illustrate this process:

1. Please contact PowerClerk's Support Team by creating a new ticket in the Ticket System to request account credentials for PowerClerk's SFTP server. You can then use these credentials to auto-upload properly formatted import files to PowerClerk's SFTP server.
2. Once you have an SFTP account, you will find a “ProgramID” folder that will correlate to your PowerClerk program ID (e.g. there would be a folder like ‘ZEZ0C8KV4YH5’ that correlates to the ProgramID portion of the URL to your program e.g. https://support.powerclerk.com/MvcProjects/ProjectList?ProgramId=ZEZ0C8KV4YH5)
3. PowerClerk checks all program folders on the SFTP server every hour upon the hour, and if there is an import file present, then PowerClerk will go ahead and import that file to the respective program.
4. Successful automated imports will also trigger an entry visible under the Data Import >> Data Import History tab (the respective imports show as Imported by: PowerClerk Internal Processing).
5. Any failed import attempts are displayed under the Data Import >> Data Import History tab as well, and you can also receive an email notification upon such failures if you setup the notification portion visible under Admin >> Program Info within PowerClerk section ‘Other Support Settings’

Q: Is there a way to change the Project Assignee or Project Owner besides manually doing so on the Admin View page?

A: Data Imports allow to bulk edit projects that are listed in the import file to designate a new Project Owner or Project Assignee. To do so, please add a new column next to your list of projects ({ProjectNumber}) column and name it "{ProjectOwnerEmail}" or "{AssigneeEmail}" respectively:



As values for this column please enter the existing email addresses of valid PowerClerk accounts available in your program. Please note: For project ownership changes it is also mandatory to include column headers "{ProjectOwnerFirstName}"and "{ProjectOwnerLastName}" with their respective values. Once your import file is ready, please validate it and import your import file to bulk edit project ownership or assignee changes.

Q: What is the difference between providing the Current Status & Current Status Timestamp data on the .csv file or selecting a Status on the Data Import Page itself?

A: Providing the Current Status and Current Status Timestamp data within the .csv file will:

1. Allow you to dictate the Current Status project-by-project.
2. Offer the opportunity to provide different timestamps, under Current Status, for reporting purposes.
3. Will not run any Automation associated with this status change.

Selecting a Status on the Data Import Page itself will:

1. Change the Current Status of all the projects within the .csv file.
2. The timestamp of the Current Status will be the date & time the import was completed.
3. Will run any Automation associated with this status change, except for “Send Communication” action rules. This includes Automations triggered by projects exiting their current status, transitioning between specific statuses, and entering the target status.

Have additional questions? Contact us to nominate your FAQ and help others find answers to your own questions concerning this feature.

Create A Support Ticket

Not finding your answer here?  Submit a question to our support team at the PowerClerk Ticket System and leverage the PowerClerk team’s expertise.