LCvista Connection for CSV files

Create accounts in LCvista from a CSV file

Introduction

CSV File Best Practices

Authenticate Connections

Configure Recipe

Introduction

LCvista connections powered by Workato provides customers the ability to configure, define and automate data connections in a self-serve manner.  The default recipe provided by LCvista is configured to process a CSV file delivered through an SFTP connection.  The recipe will look for a new file every 5 mins and process the data once a new file is uploaded to the SFTP site. 

LCvista recommends sync'ing data using CSV file connections only when there isn't an option to connect directly to your HRIS or Identity Management platform.  CSV files require additional management from customers.  The customer will need to create and manage a process that extracts data into a CSV file from your HRIS platform and the process to transfer the files to an SFTP site.

 

 

 

CSV File Best Practices

Follow these best practices when creating a process to extract data from your HRIS platform for a CSV sync.

Incremental / Delta Files

When using the CSV file connection, it is important that data is limited to records that have been recently modified.  This is important to limit the number of tasks that your account consumes.  Each record processed consumes 1-3 tasks depending on the complexity of your recipe.

The ability to create delta files is dependent on your processes that creates the CSV file.  Some options for creating a delta file is to only generate a file when records are modified or created in your system.  Another option is to have a limited look back of records modified or created in the past 24 hours.

File Naming 

LCvista recommends customer's use a unique file name to provide ability to trace imports to a specific file.  However, when using Workato's recipe a unique file name is not necessarily required. Typical naming conventions follow a pattern that describes the data being updated and includes a timestamp such as YYYYMMDD_HHMMSS in the filename to ensure the file name is unique.  For example, a user account filename could be "USER_ACCOUNTS_20240101_103000.csv".

File Format 

LCvista recommends using a standard Comma Delimited format known as "CSV".  The csv file is a text format where each column is separated by a comma and double quotes are often used to wrap each field. 

File Fields

Some best practices to follow when defining the extract for your CSV file:

1. Restrict the data in the file to recently modified or created records
2. Do not send duplicate data; create one row per account that was created or modified 
3. Limit the fields in your file to the fields that you are sync'ing in LCvista (remove extraneous fields that are not used in LCvista. 
4. The CSV file must include column headers or field names. 
   1. Column headers can be named as you like and will be mapped to the appropriate LCvista field name in the recipe 
   2. While there is no restriction on specific column names, it is recommended that you only use alpha-numeric characters, dashes and underscores when defining your field names in the CSV file
5. Date of birth: Certain jurisdictions require the birth month and odd/even birth year.  A common approach to masking the PII date of birth field is to default the day to "01" and the year to "2000" or "2001".  For example a birth date of July 4th, 1970 would be masked as "2000-07-01"..

At a minimum LCvista requires the following data to create an account:

• username
• first name
• last name
• email address
• date of birth (only required if the account is associated to specific jurisdictions like California)

Authenticate Connections

In order to process a CSV file, you'll need make your file available to Workato.  This is done typically via SFTP site.  Once you create an SFTP site and you have credentials to access the files follow these steps.

SFTP

1. Go to your Workato account and open the provided recipe.  

2. From the recipe details page, click on the Connections tab.

3. Click on the SFTP connection and enter the required fields.  Typically accept all default settings.  Please work with your internal IT administrator if your company has specific SFTP settings that differ from the standard default.

The Workato SFTP connection offers a number of settings including Authentication type.  The most common Auth type is a username and password.  However, you can work with your internal IT administrator to configure the connection per your specific requirements.

LCvista Connection

1. Generate or copy your LCvista API Key from the Organization > Settings menu.

2. Go to your Workato account and open the provided recipe.  

3. From the recipe details page, click on the Connections tab.

4. Click on the LCvista connection and enter the required fields.

 

Configure Recipe

After authorizing connections, the next step is to configure the recipe to map fields from the CSV file to the fields in your LCvista tenant.

Configure Trigger Action

1. Go to your Workato account and open the provided recipe
2. From the receipt details page, click on edit recipe
3. Edit the trigger for the recipe; This will be the first action box and is typically named something similar to "New/Update CSV file in directory on SFTP Server"
4. On the Edit Trigger, you'll need to configure the following settings:
   1. Directory - Specify the directory on the SFTP server where the file is located
   2. Column Names - Upload a sample file to generate the file names defined in the CSV File.  Anytime you modify the CSV file you'll need to perform this step.
   3. Column delimiter - Specify how the CSV file is delimited (typically comma)
   4. Batch size - Set this value to 1 which will indicates to the recipe to process each individual record as an individual job
   5. Expected encoding - Typically set to UTF-8; However you can work with your IT administrator to confirm the file encoding
   6. Ignore missing file - Set to Yes
   7. Keep track of columns by - Column order (It is assumed once the file is defined the column order will not change)
   8. Quote character - Double quote

Set Trigger Condition

Use this setting to restrict the trigger to only initiate action on files that contain a certain name.  This will ignore any file transferred to SFTP site that doesn't match the expected file name.

The following example will only process files where the file name contains "USER_ACCOUNT".

Configure the LCvista Action

The LCvista action maps the fields provided in the CSV file to LCvista fields.  This action if named something similar to "Create or Update an account in LCvista".  For this action, simply click into the available fields in LCvista and map a Data Pill from the available output data pills of "New/updated CSV file in directory".

For more complex mapping such as translating a value in the CSV file to a different value in LCvista you can use Workato formulas.  The example above demonstrates the formulate that converts the value of "Yes" in CSV field "Deactivated?" to the LCvista account status value of "Active".  See Workato documentation for more information on Data Pill Formulas.

Archive File

The CSV Recipe provided by LCvista contains a step to archive files once they are processed.  This is an option step.  Workato will automatically track files that have been processed.  If you'd like to archive files to an archive directory modify the step "Rename/move file in SFTP server" as needed.

Save and Schedule Recipe

Now that recipe is configured, click save and exit.  Then click on the Start Recipe button.  Once recipe is started you can manually transfer a sample file to confirm it is processed as expected.