±split

This command splits a column into two or more columns, by replacing it by the new columns at the same place.

The basic principle is simple:

Splitting Modes

There are four modes of splitting, and each one works from left or from right. The splitting mode is defined by the command mode.

Separating columns from left to right:

• split, or split/s. Splitting takes place at the first occurence of the separator string.(see below). The left section will be put into the first column, the remainder will be moved to the right. The separator string itself will be lost.
• split/l (split left). Ditto, but the separator string will be appended to the left column.
• split/r (split right). The separator string will be prepended to the right column.
• split/n=n (split at position). Split after the n'th character of the column (i.e., left column width is n).

Separating columns from right to left:

• split/-s Splitting takes place at the last occurence of the separator string. The last section will be put into the rightmost column. The remainder will stay at left. The separator string itself will be lost.
• split/-l (split left). Ditto, but the separator string will be appended to the left column.
• split/-r (split right). The separator string will be prepended to the right column.
• split/-n=n (split at position). Split before the n'th-right character of the column (i.e., right column width is n).

The separator

Splitting a column into two

Pattern

Usage examples:

csvfox fullnames.csv -split[Name]@[First_Name],[Last_Name] firstlastnames.csv

The separation takes place at the first whitespace (because this is the default split separator). That whitespace is lost.
The resulting CSV table is written to firstlastnames.csv.

firstlastnames.csv

City	First_Name	Last_Name	Birth	Driver License
Rome	Andrea	Bocelli	1958	y
Los Angeles	Lieutnant	Columbo	1927	y
London	Hercule	Poirot	1910	-
New York	Theodoros	Kojak	1922	y

-split/l[Name]@[Left],[Right]=o

The Name field is separated into two new columns at the first occurrence of the character "o". The "o" is appended to the left column.

City	Left	Right	Birth	Driver License
Rome	Andrea Bo	celli	1958	y
Los Angeles	Lieutnant Co	lumbo	1927	y
London	Hercule Po	irot	1910	-
New York	Theo	doros Kojak	1922	y

-split/r[Name]@[Left],[Right]=o

Again, the Name field is separated into two new columns at the first occurrence of the character "o". The "o" is prepended to the right column.

City	Left	Right	Birth	Driver License
Rome	Andrea B	ocelli	1958	y
Los Angeles	Lieutnant C	olumbo	1927	y
London	Hercule P	oirot	1910	-
New York	The	odoros Kojak	1922	y

-split/n[Name]@[Left\sPart],[Right\sPart]=5

The Name field is separated after the fifth character (i.e., the first column will be 5 characters wide). The new column names shall be "Left Part" and "Right Part".
Hint: For the "\s" as a placeholder for whitespace, see here: Character Escaping and Placeholders.
The placeholder might be required for using on the command line, but not inside a %job, %j file. You can use a plain whitespace there.

City	Left Part	Right Part	Birth	Driver License
Rome	Andre	a Bocelli	1958	y
Los Angeles	Lieut	nant Columbo	1927	y
London	Hercu	le Poirot	1910	-
New York	Theod	oros Kojak	1922	y

-split/-n[Name]@[Left],[Right]=5

A negative "n" means counting the position from the right end: The right column will consist of the last five characters of the Name column, the left column will get the (left-side) remainder.

City	Left	Right	Birth	Driver License
Rome	Andrea Bo	celli	1958	y
Los Angeles	Lieutnant Co	lumbo	1927	y
London	Hercule P	oirot	1910	-
New York	Theodoros	Kojak	1922	y

Splitting a column into more than two new columns

Description

This works exactly like before. You just provide even more new column names!
But because here more splitting takes place, there is the option to handle each splitting occurance differently (see below).

Pattern

Usage Examples:

Here is a product table "products.csv" that contains a nasty category path, which must be splitted into single category columns for further processing.

Each "\s" stands for a single whitespace as part of the separator.
You can leave whitespaces off in the separator string and instead define only the colon as separator, but then the content of all new columns should be shortened with ±trim afterwards. Additionally, a single embedded colon might be misunderstood as a category separator.

And this will happen if the mode "-s" (from right to left) is used:
-split/-s[Category\sPath]@[Gender],[Level2]-[Level5]=\s:\s

This might be useful if the hierarchy is ordered from right to left, and not from left to right as in the example above.
If the "Gender" content shall still always be placed into the first new column, a two-steps approach will be required:
-split/s[Category\sPath]@[Gender],[Levels]=\s:\s
-split/-s[Levels]@[Level2]-[Level5]=\s:\s

Using more separators for more fields

You can optionally define a series of splitting separator strings, one for each new column. In this case, the number of splitting separator strings must exactly correspond to the number of new columns minus 1, as each one will be used for separating one new field. These splitting separator strings must be concatenated by comma ,, like
-split[This]@[New1],[New2],[New3],[New4]=1.,2.,3. or
-split[This]@[New1],[New2],[New3],[New4]=-,-,\s .
When splitting on numeric positions, you can use the relative positions (i.e. lenghts) as separator parameter:
-split/n[This]@[New1],[New2],[New3],[New4]=-5,10,5  .

If the comma iself must be specified as separator, and it is the only separator, just write -split(...)=, .
But if the comma is part of a separator string instead, or included in the separator list, it must be escaped for clarity:
-split(...)=-,\,,- Here we have a list of three separators (hyphen, escaped comma, hyphen), for separating four new columns.

This is how we can split the "Code" column
(the remaining part after splitting at place 5, and then at place 6, will go into the third column "Code3"):
-split/n[Code]@[Code1],[Code2],[Code3]=5,6

But if the csv was already prepared with round brackets, like this:

Just do not forget to escape all brackets and "special" characters (see Placeholders).

Keeping the original column

If you also want to keep the original column together with the new ones, you should duplicate it before splitting.
-insert[ColumnToSplit]@[OriginalColumn]=[OriginalColumn]
-split[ColumnToSplit]@[NewColumn1],[newColumn2]...
This way, the column [OriginalColumn] keeps its full contents, while [ColumnToSplit] will be replaced by the new columns.