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:
- Your schema name should not have spaces, and must be in snake case (e.g., "salaried_employees", not "Salaried Employees")
- 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
Note: You do not need a value for the capacity_id field. Capacity_id is a unique identifier for each row that is set automatically upon uploading data.
Be careful when you're typing in the column names. One minor misspelling can result in an entire failure to import data.
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.
The CSV import function does not currently support the merging of records. Don't re-import the same records after they've been added to the table. Instead, only include new records each time you upload a file. If you need to update records, use the edit function on that record.
More Help
Please contact the Capacity Customer Success team if you need any help.