How to handle CSV files

Our platform uses CSV files for importing data. These files are quite common, but if you've never worked with them before, they can pose a bit of a challenge.

This guide will teach you:

1. What are CSV files
2. Create formulas
3. Saving a spreadsheet to a CSV file

1. What are CSV files

CSV stands for "Comma Separated Values". These files have the extension .csv and are basically flat-text files. You can open them using multiple software tools such as:

• Spreadsheet editors
  • Microsoft Excel
  • Numbers
  • Google Sheets
  • ...
• Text editors
  • Notepad
  • TextEdit
  • ...

There's 2 types of CSV files that are common. The only difference is the type of separator:

• Comma as a separator (popular in the US because of using a point as a decimal separator)
• Semicolon as a separator (popular in the EU because of using a comma as a decimal separator)

Our platform can handle either, depending on the way we set up your account. We can either configure you're account to work with one or the other. 

2. File structure

CSV files are always structured the same way. However, you'll only come into contact with it if you're opening the file in a text editor. There's a few key takeaways you need to keep in mind:

• The first row contains the headers. We provide exports of example CSV's with the correct headers. If anything changes here, the file will not be compatible anymore.
• Each following row contains 1 item, like a contact or response. The example files will contain 1 example row.
• Each column is separated by a comma or semicolon.
• Each row is separated by a new-line.
• If data in one column is complex, it is possible to encapsulate the data in double quotes. This way your text or numbers can contain commas and semicolons.
• You can leave columns empty, but it is possible it is required by the platform. In that case you'll get an error message.
• After the last value on a row, there is no separator.

Example:

Column A;Column B;"Long name here, really long";Column D
Row 1A;Row 1B; Row 1C;Row 1D
Row 2A;Row 2B; Row 2C;Row 3D

3. Saving a spreadsheet to a CSV file

In Numbers, click on File, Export To and then CSV....

Numbers will pick a comma or semicolon as separator based on the settings of your Mac. 

In Google Sheets, click File, Download and then Comma-separated values (.csv).

Note that Google Sheets only exports with commas as separator. 

A trick to get Google to do what you want, is to create a new Sheet within the same file. In this new sheet, add the following formula in cell A1:

=TEXTJOIN(";";0;Sheet1!A1;Sheet1!B1;Sheet1!C1;Sheet1!D1)

Similar workarounds will work for all spreadsheet editors, but the formulas may differ a bit.