Skip to main content
Sign in

What are best practices when creating a data source using CSV?

  • Updated

For the easy and clean import of a CSV file, use the following guidelines:

  1. When creating the data source, go to the Advanced Configuration and:
    1. Select the "Import fields by name" if possible for your situation. If this box is not checked, the file is imported by column order. Importing by column order is more risky because Openprise does not validate data as it is importing and if the columns are out of order, the results can be unpredictable.
    2. Check the box "Skip File Import If the Schema is different from the Configured". This protects your data source from getting corrupted if the format of the CSV file changes.
  2. When creating the CSV file:
    1. Avoid html escape sequences (these often show up in notes fields).
    2. Double quote your double quotes: ie. his “gear” should be his “”gear””.
    3. Avoid angle brackets <>.<></></>
    4. Replace all tabs with commas (if a field separator) or blank (if the tab is part of a text string field).
    5. TSV files sometimes do not have double quotes around string values and it is best to do so. Double quotes should be around every non-numeric value.
    6. Remove all control characters. One method of doing this is to load the CSV file into Google Sheets and save it as a sheet. Google Sheets usually strips the control characters for you.
  3. Avoid opening CSV files using Excel because by default Excel does not handle multibyte characters properly. Excel also has a propensity of stripping leading zeros on all numeric fields (such as zip codes). If you want to preview the data using Excel, use the data tab and the "(import) From Text/CSV" option.

When should you use CSV files as the input source file type? When should you use Google sheets? 

Google Sheets has a few advantages over CSV:

  • It’s easy to update the file from your browser without going through the save-as-CSV step.
  • Google Sheets handles multibyte, international characters better than CSV.
  • You can put in formulas to do calculations or additional operations.

However, if your sheet contains over 10,000 rows of data, Google and Openprise may not be able to open the file. In this case, switch to the Google Drive - CSV file input source file type. 

 

Related articles