# This is an Awk program for reformatting
# the mercury.txt file.
#
# Put yourself in a folder that contains
# both mercury.awk and mercury.txt.
#
# Run this program by typing:
#  awk -f mercury.awk mercury.txt

BEGIN {
    # specify the field separator in the input.
    FS=", ";
    # specify the field separator in the output.
    OFS=",";
}
{
    # replace double quotation marks around the
    # name of the spacecraft with single quotes
    spacecraft = "'" substr( $1, 2, length($1) - 2 ) "'";
    
    launchDay = substr( $2, 2, length($2) - 2 );
    # divide the date into 3 parts: month, day, and year
    split( launchDay, launch, "/" );
    # reassemble the parts of the date in the order
    # that SQL requires
    # at the same time, replace double quotation marks around the
    # launch date with single quotes
    takeOff = "'" launch[3] "-" launch[1] "-" launch[2] "'";

    recoveryDay = substr( $3, 2, length($3) - 2 );
    # divide the date into 3 parts: month, day, and year
    split( recoveryDay, recovery, "/" );
    # reassemble the parts of the date in the order
    # that SQL requires
    # at the same time, replace double quotation marks around the
    # recovery date with single quotes
    splashDown = "'" recovery[3] "-" recovery[1] "-" recovery[2] "'";

    # replace double quotation marks around the
    # name of the destination with single quotes
    destination = "'" substr( $4, 2, length($4) - 2 ) "'";
    
    print "(" spacecraft, takeOff, splashDown, destination "),";
}