Calcapp offers a variety of formats for you to choose between when sharing field values through email or direct download buttons. One of the most useful formats is CSV, which stands for Comma-Separated Values, which we introduced back in 2016.
CSV files are just what the name implies—a number of values separated by commas:
"Screen 1:" "Field 1","Field 2","Field 3" 1,2,3 "Screen 2:" "Field 4","Field 5","Field 6" 4,5,6
This simplicity means that an abundance of tools can import data from CSV files, including spreadsheets like Microsoft Excel and databases.
Unfortunately, there is no real CSV standard, meaning that some tools can have trouble working with the CSV data sent from your apps. When we introduced CSV support, our main goal was to enable Microsoft Excel to easily import data sent from your apps. Since then, we have realized that our customers use a variety of tools to work with our CSV files, which does not always go smoothly. With our new release, our goal is to solve these issues.
When you add a new report button and instruct it to use the CSV format, there is a new section in the inspector with all the new options:
Setting the delimiter and decimal separator
The delimiter is the character that separates values in the CSV file. Before this release, Calcapp always used a comma.
You can now set the delimiter to a tab character, in which case the resulting CSV files are often known as TSV files (short for Tab-Separated Values). We have learned that some apps and services can only work with TSV files, and not CSV files, which means that these apps and services can now finally work directly with TSV files produced directly from your apps. You also have the option of using a space character as a delimiter.
Software used outside of the English-speaking world often uses a semicolon to separate fields. The reason is that they typically use a decimal comma as a decimal separator, and not a decimal point. You can now explicitly set the delimiter to a semicolon.
Microsoft Excel uses the language settings of the host operating system to determine how to handle CSV files. That means that a CSV file produced by Excel in Germany (which uses a decimal comma as a decimal separator and a semicolon as a delimiter) may not be readable by a US English version of Excel (at least not without using the Data > Text to Columns feature). That means that you must often be conscious of what software is used to read your CSV files, and the language settings used by these users.
Before this release, Calcapp always used a decimal point as a decimal separator, regardless of the language your app was configured to use. If Excel was configured to use a decimal comma, these values would be interpreted as text strings and not as numbers. Now, you can set the decimal separator explicitly.
CSV files can use a comma as both a delimiter and as a decimal separator. If you go that route, though, decimal numbers will be surrounded by quotes in the CSV file, which increases the file size.
The default delimiter and decimal separator
When you create a new email report button or download report button, you will find that both the delimiter and decimal separator are set to special values known as Default. When default values are used, the actual values used depend on the language settings of your app.
If the decimal separator is set to the default value, it follows the decimal separator used by the language the app is configured to use (a decimal comma for an app in German and a decimal point for an app in English, for instance).
If the delimiter is set to the default value, a semicolon is used if the configured decimal separator is a decimal comma. Otherwise, a comma is used.
We think that these default settings will produce the desired result for most users. Apps in German tend to produce CSV files which will be consumed by users using German language settings. With the new defaults, that scenario—and many like it—will hopefully just work.
Of course, if your app is in German, but files are sent to headquarters in the UK, you can manually set the decimal separator and delimiter to values which will work in the UK.
Existing apps created by older versions of Calcapp will continue to behave like before. In other words, they will continue to use a decimal point for a decimal separator and a comma as a delimiter, regardless of the language settings of the app. This behavior ensures that our new release does not break existing apps. If you want to change the behavior, simply make the desired changes in Calcapp Creator.
Excluding the delimiter line
There is a new option to “exclude the delimiter line.” Older versions of Calcapp always included this line as the first line in a CSV file.
The delimiter line simply specifies what delimiter is used:
sep=; "Screen 1:" "Field 1";"Field 2";"Field 3" 1;2;3 "Screen 2:" "Field 4";"Field 5";"Field 6" 4;5;6
Microsoft Excel uses it to correctly read CSV files, regardless of the language settings used by the host operating system. In other words, CSV files produced by previous versions of Calcapp were readable even by versions of Excel normally expecting a semicolon to be used as the delimiter.
(Unfortunately, there is no “decimal separator line” which can be used to instruct Excel what decimal separator is used. That means that decimal numbers in CSV files produced by older versions of Calcapp were likely read as text by Excel if the host operating system was configured to use a decimal comma as the decimal separator.)
Only Excel is known to recognize the delimiter line. If you import a CSV file into Google Sheets, for instance, it will show up as part of your data.
For that reason, excluding the delimiter line if Excel is not used to consume the file is the right course of action. You can also safely exclude it if Excel is used and you know that it is configured to use the same delimiter that your CSV files contain.
Finally, excluding the delimiter line often makes the resulting CSV files much smaller. The reason is that Calcapp can use a more space-efficient character encoding for the file if a delimiter line is not used than the character encoding which would otherwise have been used, because of long-standing issues with Microsoft Excel.
Laying out fields values horizontally or vertically
Since its inception, Calcapp has laid out fields and their values horizontally. Consider an app with two screens, Screen1 and Screen2, each containing three fields. Here’s the output when values are laid out horizontally, if all fields are included:
| Screen 1: | ||
| Field 1 | Field 2 | Field 3 |
| 1 | 2 | 3 |
| Screen 2: | ||
| Field 4 | Field 5 | Field 6 |
| 4 | 5 | 6 |
To instead lay out values vertically, choose Arrange values from top to bottom in the inspector. This is the result:
| Screen 1: | |
| Field 1 | 1 |
| Field 2 | 2 |
| Field 3 | 3 |
| Screen 2: | |
| Field 4 | 4 |
| Field 5 | 5 |
| Field 6 | 6 |
When you create a new button, values are laid out vertically by default. However, apps created with older versions of Calcapp will continue to lay out values horizontally until you change the option in Calcapp Creator.