CSVfox
Leverage Your Data.
 English

±combine

Combines a new single CSV column out of two or more other columns

This command builds a new column from concatenating two or more existing columns, optionally replacing them at the same place.

The basic principle is simple:

±combine[New Column]@[Old Column],[Old Column]...
The given column names can be denoted as a list that is comma separated, and/or as a column name range (see below).

Combining Modes


There are three modes of combining. The combining mode is defined by the command mode.
  • combine, or combine/s. The denoted columns will be combined into a new column as described. The existing columns will be replaced and lost.
  • combine/l The new column will be inserted left to the leftmost of existing columns.
  • combine/r The new column will be inserted right to the rightmost of existing columns.

The separator

The separator string is defined by an optional command parameter after the "=". The separator string, if one is given, will be inserted between each of the new columns.
If there is no separator string given, the columns will be concatenated directly, without any separator.

Combining a new column from two other columns

This is most simple. Just name the existing columns, the new column name, and the desired separator that will be inserted, and the denoted columns will be replaced by the new one, if desired, or the existing columns will also be kept.

Pattern

±combine[New Column]@[Old Column],[Old Column] ±combine[New Column]@[Old Column],[Old Column]=Separator string

Usage examples:

This small CSV table will be used in the following examples:
firstlastnames.csv
CityFirst_NameLast_NameBirthDriver License
RomeAndreaBocelli1958y
Los AngelesLieutnantColumbo1927y
LondonHerculePoirot1910-
New YorkTheodorosKojak1922y
The following examples show how to combine two columns in various modes.
We will use the columns First_Name and Last_Name for this.
csvfox firstlastnames.csv -combine[Full_Name]@[First_Name],[Last_Name] fullnames.csv
First_Name and Last_Name shall be combined into the new field "Full_Name".
The resulting CSV table is written to fullnames.csv.
fullnames.csv
CityFull_NameBirthDriver License
RomeAndreaBocelli1958y
Los AngelesLieutnantColumbo1927y
LondonHerculePoirot1910-
New YorkTheodorosKojak1922y
Oops, we forgot the separator string!
-combine[Full_Name]@[First_Name],[Last_Name]=\s
The same as above, but this time a whitespace shall be inserted as a separator.
CityFull_NameBirthDriver License
RomeAndrea Bocelli1958y
Los AngelesLieutnant Columbo1927y
LondonHercule Poirot1910-
New YorkTheodoros Kojak1922y
-combine/l[Full_Name]@[First_Name],[Last_Name]=\s
The same as above, but we insert the new column left to the existing columns, because we want to keep those also.
CityFull_NameFirst_NameLast_NameBirthDriver License
RomeAndrea BocelliAndreaBocelli1958y
Los AngelesLieutnant ColumboLieutnantColumbo1927y
LondonHercule PoirotHerculePoirot1910-
New YorkTheodoros KojakTheodorosKojak1922y

 
-combine/r[Full_Name]@[First_Name],[Last_Name]=\s
The same as above, but this time we want to insert the new column right to the existing columns.
CityFirst_NameLast_NameFull_NameBirthDriver License
RomeAndreaBocelliAndrea Bocelli1958y
Los AngelesLieutnantColumboLieutnant Columbo1927y
LondonHerculePoirotHercule Poirot1910-
New YorkTheodorosKojakTheodoros Kojak1922y

Combining a column from more than two other columns

Description

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

Pattern

±combine[New Column]@[Old Column],[Old Column]-[Old Column] ... ±combine[New Column]@[Old Column],[Old Column]-[Old Column]=separator ±combine/mode[New Column]@[Old Column],[Old Column]-[Old Column]=separator,separator,separator ...

Usage Examples:

To be completed ...

ProductsWithCategories.csv
NameProductIDGenderLevel2Level3Level4Level5Price
Green Fashion JacketJACKET1WomenOutdoorJacketsTransitional220 $
Black Rain CoatRAINCOAT1WomenOutdoorCoatsRain200 $
Rubber Flubber RainbootsW4589356WomenShoesBoots100 $
Dragon Style Men's Blue JeansJ4534656MenTrousersJeansStonewashedUsed100 $

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
-combine[New]@[Old1],[Old2],[Old3],[Old4]=1.,2.,3. or
-combine[New]@[Old1],[Old2],[Old3],[Old4]=-,-,\s .
 

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

to be completed ....

Finally, here is an example how to use numeric splitting into more than two columns.
We have some keys together with alphanumeric codes that are 15 digits long, and want to split them into columns of five, six, and four digits.
codes.csv
KeyCode
Alpha1KGVDEBI6KGGA6Z
BetaKJGTUJ75HTDH87N
GammaKJHT654FNJ7FVPL
DeltaJZVV4390JGRDGBK

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"):
-combine[Code]@[Code1],[Code2],[Code3]

threepartcodes.csv
KeyCode1Code2Code3
Alpha1KGVDEBI6KGGA6Z
BetaKJGTUJ75HTDH87N
GammaKJHT654FNJ7FVPL
DeltaJZVV4390JGRDGBK

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

codes.csv
KeyCode
Alpha1KGVD(EBI6KG)GA6Z
BetaKJGTU(J75HTD)H87N
GammaKJHT6(54FNJ7)FVPL
DeltaJZVV4(390JGR)DGBK

This is how we would split the "Code" column instead, making use of the brackets:
-split[Code]@[Code1],[Code2],[Code3]=\(,\)
threepartcodes.csv
KeyCode1Code2Code3
Alpha1KGVDEBI6KGGA6Z
BetaKJGTUJ75HTDH87N
GammaKJHT654FNJ7FVPL
DeltaJZVV4390JGRDGBK

Under construction, coming soon