Loading Data into CapacityDB via CSV

Background

CapacityDB is a Capacity-hosted database that is tightly integrated with the rest of the Capacity stack.  Think of it as a fuel source for the rest of the platform.  For more background on the product, see https://support.capacity.com/article/493294/capacitydb.

Organizations have several options for loading data into CapacityDB.  Via

  • A workflow
  • A guided conversation
  • SQL statements executed in the SQL editor
  • Adding records one-by-one in the web console
  • Adding records in bulk, as a one-time load using a CSV file.

This article addresses the last method.

What is a CSV?

Basics

CSV stands for "Comma-separated values".  True to the name, a CSV file is a text file, with values separated by commas.  It looks something like this:

Id,First Name,Last Name,Location,FTE

123,John,Doe,New York,No,456,Jane,Doe,Chicago,Yes

The first row consists of the columns, and the subsequent values match 1:1 to those columns.  With this data, records can be added for each series of values.  In the example, two records (also called "rows") are represented.

How to Create a CSV

There are several ways to generate a CSV file.  You can use:

  • Microsoft Excel
  • Google Sheets
  • A text editor (e.g., Notepad, TextEdit)

Excel and Google Sheets let you export a standard spreadsheet as a CSV.  Files generated from text editors as TXT files may be renamed with the extension CSV.

How to Import a CSV

Preparing Your File

Actually importing an appropriately-formatted CSV into CapacityDB is a one-step process.  However, your file needs to be prepared beforehand or the upload will fail.  Here are the basic rules:

  • The first row should be your column names
  • Each column name should perfectly match the fields you have set up in your table schema.  For example, if your first column in your Excel file and the first value in the CSV is "first_name", you'll need to ensure that you have a field in the database schema called "first_name" adhering to the rules for that field.  For example, if you have a "start_date" field, with a date type, the values in that column should be in the proper date format and not a number or string of characters.
  • The column names (and fields) must be in snake case (e.g., "first_name", not "First_Name")
  • Some words are reserved by the database. You can't use them as column names. A list of words is at https://mariadb.com/kb/en/reserved-words/
  • All non-nullable fields should have a value for each record

Importing Your File

Once you've prepared a file, you can now add its data to the table.  Click the "Import CSV" button, and select your file.

More Help

Please contact the Capacity Customer Success team if you need any help.













Was this article helpful?