Leverage Your Data.

CSV Field Access

How to access CSV Columns and Fields

Data rows, fields and columns

All data of a CSV file is always accessed line-by-line.
"Fields" are the data units contained in a CSV line. Fields are retrieved line-by-line and can be manipulated with set, regex or other data field commands.
The term "Columns" applies to all fields that have the same ordering place in each CSV line. They might become inserted, renamed or splitted by column commands.

Fields or columns are always addressed in [Square Brackets].

Three ways of retrieving a single CSV Field

There are 3 ways to address a field or column:

1. If column names are supported (this is controlled by colnames and is active per default), then preferably use them.
This way, you do not have to think about on which position the column exactly is in the table. And if the table structure changes, it will still work as long as the column name remains the same. 
The column name is the field content of the first CSV line.
=> So you can just write [MyField]  if you want to adress a column that has the value "MyField" in the first CSV line.

2. Use it's column number.
Column numbering starts with 1 and counts upward.
This is a kind of absolute addressing, independently from how the columns are named. It is always available, no matter if column names are even supported.
Do not use a thousands separator when writing the column number.
=> So you can also write [3] if you want to address the third column.

3. Use the "Excel style".
This is how common spreadsheet software like Excel, OpenOffice, LibreOffice etc. enumerate the columns.
Excel-style numbering starts with "A" for the first column. Then follows "B", "C" and so on, until "Z". This covers the columns no. 1 to 26.
After "Z", it goes on with "AA", "AB", "AC" etc. These values correspond to columns no. 27, 28, 29 etc.
Using this might come in handy when you have usually worked with the table in a spreadsheet application, and immediately only know how the columns are titled there.
=> So you can even write [C] if you want to address the third column.

autumncolorful leaveslimousine92
wintersnowy twigssled90
In the table above, you can address the contents of the third column in the following ways: as [Vehicle], as [3] or as [C].
Each of them will return "bicycle" for the first data line, then "cabriolet" for the second line, then "limousine" and at last "sled".


There might occur the case that columns exist in the CSV table which have names that can be interpreted in various ways.
There might be a column named "AB" or another one named "12". So, trying to access a column named [AB] using it's name might also mean "access the excel-style AB column"  (which is the 28th column). Similarly, when calling the column [12] simply may define "access the twelfth column" instead.

In this case, the rule is:
If the CSV file uses column names, the column name will always tried first.
Only if there is no column with this name (or when column names are not supported), the column number or excel-style name are tried.
Therefore, if there is a column named "12" it can be acessed with [12]. However, the twelfth column cannot be accessed with its index anymore, so it must be acessed with its column name or, using Excel Style, with [L].

Special characters in CSV field names

There is a small possibility that the column name contains brackets itself.
In this case, any brackets that are part of the column name must be specially marked (escaped).
Escaping means to put a backslash "\" in front of the bracket inside the name.
So if you want to access a column named "a[b]", you cannot just write "[a[b]]".
Instead, you will have to use "[a\[b\]]".
CSVfox will then identify "\[" as "[", and "\]" as "]", and both will be seen as part of the name.

Special fields

These field-like tags can not be part of the command (left to the "="), they are only valid at the right side (expression) of a command.

[*] The current content of "this" field (the current field the command refers to, used in Expressions and their Resolving)
(#) The current CSV row number or data set index.
[#] Field name of the current column number, starting with 1 (and what about [#] field?)
Under construction, coming soon