Change AWK field separator on the fly

Question

I'd like to use AWK to take the following spread sheet where the first name and last name are in one column:

Peter Griffin, 31 Spooner St, Quahog
Homer Simpson, 732 Evergreen Terr, Springfield
Fred Flintstone, 301 Cobblestone Way, Bedrock

and output to a new spreadsheet where the first name and last name have their own columns:

Peter, Griffin, 31 Spooner St, Quahog
Homer, Simpson, 732 Evergreen Terr, Springfield
Fred, Flintstone, 301 Cobblestone Way, Bedrock

I've tried changing field separators on the fly doing something like:

awk '{print $1 "," $2} {FS=","} {print $3} {FS=" "}' spreadsheet.csv

but it doesn't seem to work that way, and I get a jumbled mess. Is this possible using AWK?

Answer 1

Just add a comma whenever a space is found in the first ,-based field:

awk 'BEGIN {FS=OFS=","} {sub(/ /, ", ", $1)}1' file
#                             ^    ^^
#               find a space...    ... replace it with , plus space

With your file:

$ awk 'BEGIN {FS=OFS=","} {sub(/ /, ", ", $1)}1' file
Peter, Griffin, 31 Spooner St, Quahog
Homer, Simpson, 732 Evergreen Terr, Springfield
Fred, Flintstone, 301 Cobblestone Way, Bedrock

This uses the function sub() to perform the replacement in the first field.

Answer 2

Replace the first space with a comma-space:

$ sed 's/ /, /' file.csv
Peter, Griffin, 31 Spooner St, Quahog
Homer, Simpson, 732 Evergreen Terr, Springfield
Fred, Flintstone, 301 Cobblestone Way, Bedrock

Here, s/ /, / is a substitute command. It replaces the first found with ,.

To change the file in place, use the -i option:

sed -i.bak 's/ /, /' file.csv