5

I have many .csv files with customer information. In all these files I want to add an additional column FIRSTNAME right next to the column FULLNAME. The Firstname can be generated with grabbing the first word from FULLNAME.

There are no two-word firstnames like Jean Paul. In the last column a comma is used in the fieldtext

Input

COMPANY,FULLNAME,EMAIL,FUNCTION,CITY,INDUSTRY,COMMENT
Company name,Firstname Lastname,[email protected],Marketing Manager,New York,Health Care,"home, work"
Company name,Firstname infix Lastname,[email protected],Marketing Manager,New York,Health Care,"home, workhome, work"
Company name,Firstname infix infix2 Lastname,[email protected],Marketing Manager,New York,Health Care,"home, work"

Expected output

COMPANY,FULLNAME,FIRSTNAME,EMAIL,FUNCTION,CITY,INDUSTRY,COMMENT
Company name,Firstname Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, work"
Company name,Firstname infix Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, work"
Company name,Firstname infix infix2 Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, work"

How to do this with awk, sed or something else?

7
  • Can Company name contain a comma, e.g., My Company, LLC? How about in the other fields? Can we assume valid csv with appropriate quotes?
    – doneal24
    Oct 13, 2022 at 16:42
  • @doneal24 updated Oct 13, 2022 at 17:18
  • Note that the COMMENT field has been modified between the input and expected output, but that you don't mention this in the text. "home, workhome, work" in the second record becomes "home, work" in the output.
    – Kusalananda
    Oct 13, 2022 at 17:26
  • 2
    not all of these apply, but note that as someone with even something mundane and pretty universal as an umlaut in their name, I'd love you to read kalzumeus.com/2010/06/17/… Oct 13, 2022 at 18:28
  • 1
    @jubilatious1 "van der" in Dutch names, for example, as in "Mathieu van der Poel".
    – Kusalananda
    Oct 13, 2022 at 20:14

3 Answers 3

8

Using the CSV-aware utility Miller (mlr):

mlr --csv \
    put '$FIRSTNAME = sub($FULLNAME," .*","")' then \
    reorder -f COMPANY,FULLNAME,FIRSTNAME file

... which, given the data in the question, results in

COMPANY,FULLNAME,FIRSTNAME,EMAIL,FUNCTION,CITY,INDUSTRY,COMMENT
Company name,Firstname Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, work"
Company name,Firstname infix Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, workhome, work"
Company name,Firstname infix infix2 Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, work"

This use of Miller first creates a new field, FIRSTNAME, through a regular expression-based substitution that removes everything after the first space character in the FULLNAME field.

Since new fields are presented last, the fields are then reordered to ensure that the first few fields are COMPANY, FULLNAME, and FIRSTNAME, in this order. The remaining fields are left in their original order.

Instead of the put expression using sub(), you may use put with its splitnv() function to split the FIRSTNAME field's value on spaces and pick out the 1st generated string:

mlr --csv \
    put '$FIRSTNAME = splitnv($FULLNAME," ")[1]' then \
    reorder -f COMPANY,FULLNAME,FIRSTNAME file

For prettier output:

$ mlr --icsv --opprint --barred put '$FIRSTNAME = splitnv($FULLNAME," ")[1]' then reorder -f COMPANY,FULLNAME,FIRSTNAME file
+--------------+---------------------------------+-----------+--------------------------------+-------------------+----------+-------------+----------------------+
| COMPANY      | FULLNAME                        | FIRSTNAME | EMAIL                          | FUNCTION          | CITY     | INDUSTRY    | COMMENT              |
+--------------+---------------------------------+-----------+--------------------------------+-------------------+----------+-------------+----------------------+
| Company name | Firstname Lastname              | Firstname | [email protected] | Marketing Manager | New York | Health Care | home, work           |
| Company name | Firstname infix Lastname        | Firstname | [email protected] | Marketing Manager | New York | Health Care | home, workhome, work |
| Company name | Firstname infix infix2 Lastname | Firstname | [email protected] | Marketing Manager | New York | Health Care | home, work           |
+--------------+---------------------------------+-----------+--------------------------------+-------------------+----------+-------------+----------------------+
3

Using Raku (formerly known as Perl_6)

~$ raku -MText::CSV -e 'my @a = csv(in => $*IN);  \
                        my @b = [Z] @a>>[0..1], @a>>[1].map(*.words.[0]), @a>>[2..*];  \
                        @b = @b>>.[*;*]>>.Array; @b[0][2] = "FIRSTNAME";  \
                        csv(in => @b, out => $*OUT);'  file

This may be the simplest approach, should the OP desire double-quoted CSV output throughout (quoted fields where embedded commas and/or spaces occur, see RFC4180).

The Perl(5) module Text::CSV_XS is well-regarded, and a longtime author/maintainer of that module has developed the Raku Text::CSV module (H. Merijn Brand, personal communication). Here, Raku's Text::CSV functions are confined to IO operations, in particular, the quoted last column. Otherwise, the column manipulations above are done using standard Raku arrays. Still, this code has the advantage that a double-quoted file-path can be used instead of $*IN, which takes STDIN.

Above, the file is read-into @a array on the first line, in the second line the first word of zero-indexed column 1 is taken and assigned to zero-indexed column 2; this and the remainder of the rows are assigned row-by-row to @b array. Some housekeeping is done on the third line (flatten array, make @b elements mutable, and correct column header). Finally the file is output on the fourth line.

Sample Input:

COMPANY,FULLNAME,EMAIL,FUNCTION,CITY,INDUSTRY,COMMENT
Company name,Firstname Lastname,[email protected],Marketing Manager,New York,Health Care,"home, work"
Company name,Firstname infix Lastname,[email protected],Marketing Manager,New York,Health Care,"home, workhome, work"
Company name,Firstname infix infix2 Lastname,[email protected],Marketing Manager,New York,Health Care,"home, work"

Sample Output (from above):

COMPANY,FULLNAME,FIRSTNAME,EMAIL,FUNCTION,CITY,INDUSTRY,COMMENT
"Company name","Firstname Lastname",Firstname,[email protected],"Marketing Manager","New York","Health Care","home, work"
"Company name","Firstname infix Lastname",Firstname,[email protected],"Marketing Manager","New York","Health Care","home, workhome, work"
"Company name","Firstname infix infix2 Lastname",Firstname,[email protected],"Marketing Manager","New York","Health Care","home, work"

It's actually quite a bit more work to only requote the last column. Replace the last line csv(in => @b, out => $*OUT) above with the following:

.join(",").put for @b[0];  \
.join(",").put for [Z] @b[1..*]>>.[0..*-2]>>.join(","), @b[1..*]>>.[*-1].map(*.raku);'  

Sample Output (revised code example):

COMPANY,FULLNAME,FIRSTNAME,EMAIL,FUNCTION,CITY,INDUSTRY,COMMENT
Company name,Firstname Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, work"
Company name,Firstname infix Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, workhome, work"
Company name,Firstname infix infix2 Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, work"

https://datatracker.ietf.org/doc/html/rfc4180
https://github.com/Tux/CSV/blob/master/doc/Text-CSV.md
https://raku.org

2

Using sed

$ sed -E '2,$s/[^,]*,([^ ]*) [^,]*,/&\1,/;1s/([^,]*,){2}/&FIRSTNAME,/' input_file
COMPANY,FULLNAME,FIRSTNAME,EMAIL,FUNCTION,CITY,INDUSTRY,COMMENT
Company name,Firstname Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, work"
Company name,Firstname infix Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, workhome, work"
Company name,Firstname infix infix2 Lastname,Firstname,[email protected],Marketing Manager,New York,Health Care,"home, work"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .