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

How to validate a Data Import

PowerClerk allows you to export project data to a .csv file (see Reports), and also allows you to import such .csv files with your own project data.  This is done through the Data Import page:
 

Figure 2: Data Import dialog

 

For example, you might want to add data to projects in a certain status for a subset of projects. This can be done by producing a .csv file that contains the target Data Fields with the Template Tag as column headers. An example of this is shown in Figure 3 below:
 

Figure 3: .csv file with Data Field column headers

 
Please note: For Multiple Choice and Drop-down List Data Fields the import values need to match the Choice values exactly and are case-sensitive.

In Figure 3 above the first column designates this particular program’s project numbers. You also will have to select a particular Form to import your .csv data to in case you are creating new projects (e.g. in your Test Environment):
 

Figure 4: Data Import dialog referencing a target Form

 

 

 
Once you have prepared and selected the import, click the Validate button to have PowerClerk verify your data import, ensure your .csv is properly configured and target the appropriate Data Fields and Forms.
 
If your validation returns no errors, you’ll see a “Validation Succeeded!” indication from PowerClerk:
 

Figure 5: Data Import dialog showing a successful import

 
Please note: The Validate Data Import privilege grants users the ability to Validate .csv files to be imported only. The successive privilege, Run Data Import gives users the ability to complete data imports that Modify existing projects.
 
To create new projects via import in your PowerClerk production environment, please ensure your import file passes validation and send a support request to the PowerClerk Support Team to proceed with your import. Do not send any import file that includes customer personally-identifiable information (PII) as an email attachment. Please allow up to 5 business days for this process to complete.
 
Within a Test Environment you can validate and run your data import completely on your own. Data imported into the Test Environment will be deleted when the Test Environment is committed or deleted. By importing your own data in a Test Environment, this allows you create a test data file you can import whenever you need a new Test Environment. For the security and privacy of your customers, we strongly recommend that you do not import production customer data into non-production environments.
 

Targeting forms with Data Import

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 visible elements, it cannot have any elements with eSignatures configured, and it cannot have ePayments enabled. Any required fields on the target form must appear as columns in your import file, and any readonly fields on the form cannot appear as columns in your import file.
 
When PowerClerk processes each project 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”. If you do have any concerns about these options when sending over your data import to CPR then please point out your concern 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.
 
 

Figure 6: Data Import dialog

 
When targeting a specific Form with your data import, it will show in the Previous Forms section under the View/Edit screen.
 
Please note: you will have to provide *all* required data fields of a form to be able to target it, 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.
 
 

Import Modes

You can use a PowerClerk data import to modify existing projects or to create new projects.
 
When running an import to create new projects, you will need to specify a target form to be submitted for each newly-created project. If you would like to create new projects in a production PowerClerk program, you will need to submit a support request to PowerClerk Support to finalize your import after completing validation. You can run a create-mode import within a Test Environment on your own without submitting a support request.
 
When running an import to modify existing projects, you will need to tell PowerClerk how to match each row in your import file to the existing project you want to modify. You will do this by specifying an identity column in your import file. The identity column must contain a value that uniquely identifies a specific project in your program for every row in your import file. The most common option is to include a column with the header “{ProjectNumber}” where the value in each row matches the PowerClerk Project Number of the corresponding project, but if you have a different field with a unique value for each project such as “Account Number,” you could also specify an alternate identity column with the column header “{data: Account Number}.” You cannot modify the same project twice with different rows in an import file. When modifying existing projects, submitting a form for each modified project is optional.
 
 

Satisfying Deadlines with a Data Import

Data Imports can be used to bulk satisfy any given Deadline in PowerClerk. To do so, please create a spreadsheet with the project numbers you wish to satisfy the deadline on with data field {ProjectNumber} in column A and the projects listed beneath. In Column B you will use {deadline: “deadline definition name”} as shown below to target the correct deadline and enter as values for this column the keyword satisfy:
 

Figure 7: Satisfying Deadlines with a Data Import

 
Importing this kind of .csv file will satisfy the respective Deadline for all listed projects. In turn you can also set new Deadline expiration dates in bulk by following this same schema and entering the future expiration date as shown below, or use keyword default to set the default Deadline expiration date as configured for this Deadline:
 

Figure 8: Setting a Deadlines with a Data Import

 
Please note: to avoid setting duplicate Deadlines you may want to consider to have Deadline option Allow only one instance for each project checked, or ensure that you satisfy existing Deadlines first before setting a new Deadline. Also, you can only set a Deadline with a future expiration date as any Deadline with a past expiration date obviously would have expired already.
 

Bulk Communications via Data Import

To trigger a bulk Communication targeting the project owner email addresses of a subset of projects via Data Import, please format your import .csv file as shown in the example below.
Please note: this kind of Data Import will have to be finalized by a CPR Admin, which means that you can request the import of such bulk Communication .csv file by opening up a new support ticket in the PowerClerk Ticket System:
 

Figure 9: Bulk Communications via Data Import

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 Excel sheet 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: When creating projects, can I provide Current Status and Current Status Timestamp data via the csv Data Import file (instead of selecting a Status from the Drop-Down List) on the Data Import page?

A: Yes. Use {CurrentStatus} to dictate which Status you want to put the project into, and use {status_date: } to dictate what date is plugged into the Project Status History timestamp. The methodology is the same when updating existing projects as well.

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.