In this article, we're going to delve into some best practices and guidelines designed to assist you in creating and managing CSV files for optimal integration and ingestion into Pathlight. Our main goal is to ensure you efficiently organize and prepare your data for seamless integration, minimizing errors and improving overall data accuracy.
CSV File Formatting
-
CSV Delimiter: The CSV delimiter should be a comma (,).
-
Text Qualifier: Please use double quotes (") as the text qualifier.
Headers
-
Include Headers: Headers are essential for identifying the data in each column of the CSV file. Your headers should be clear and descriptive, accurately reflecting the data contained in each column.
-
Header Formatting: Headers should have no spaces or special characters. Use an underscore (_) in place of spaces, and keep all text in lowercase. Ensure your column header length is less than 59 characters.
Primary Keys
-
Identify Primary Key (PK) Columns: Indicate which columns are Primary Keys. Please note that data will be overwritten based on the PK values as Pathlight will ingest data with upserts.
Data Guidelines
-
Required Columns: Every CSV file should at least include the following columns: Agent Email/ID, Date/DateTime.
-
Date and Time Formatting: Dates should be in UTC and follow the ISO format.
-
Validate Data: Ensure your data matches the following guidelines:
- Emails should have no spaces and be in lowercase letters.
- Columns of data type 'string' should have no leading or trailing spaces.
- Columns of data type 'boolean' should have only 'true' or 'false' as values.
- Avoid using the symbol '%' (char(37)) value. For columns indicating % values, only the numeric value should be sent.
File Naming Conventions
Depending on the frequency of data updates, follow these naming conventions:
-
Daily Updates: fileName_yyyymmdd_vNNN.csv
- Example: adherence_20232303_v001.csv
-
Hourly Updates: fileName_yyyymmdd_24hhmmss.csv
- Example: adherence_20232303_141500.csv
By following these best practices and guidelines, you'll set yourself up for a successful data integration process with Pathlight. Remember, effective performance management starts with well-organized and accurately formatted data. If you have any questions or encounter any challenges, don't hesitate to reach out to your Pathlight implementation or support team.