How do you add a column to the end of a CSV file with using a string in a variable?
2012-02-29,01:00:00,Manhattan,New York,234
2012-02-29,01:00:00,Manhattan,New York,843
2012-02-29,01:00:00,Manhattan,New York,472
2012-02-29,01:00:00,Manhattan,New York,516
2012-02-29,01:00:00,Manhattan,New York,234,2012-02-29 16:13:00
2012-02-29,01:00:00,Manhattan,New York,843,2012-02-29 16:13:00
2012-02-29,01:00:00,Manhattan,New York,472,2012-02-29 16:13:00
2012-02-29,01:00:00,Manhattan,New York,516,2012-02-29 16:13:00
#!/bin/bash
awk -F"," '{$6="2012-02-29 16:13:00" OFS $6; print}' input.csv > output.csv
My attempt above in awk.sh added the string to the end but stripped all the comma separators.
2012-02-29 01:00:00 Manhattan New York 234 2012-02-29 16:13:00
2012-02-29 01:00:00 Manhattan New York 843 2012-02-29 16:13:00
2012-02-29 01:00:00 Manhattan New York 472 2012-02-29 16:13:00
2012-02-29 01:00:00 Manhattan New York 516 2012-02-29 16:13:00
Appreciate any help!
#!/bin/bash
GAWK="/bin/gawk"
TIMESTAMP=$(date +"%F %T")
ORIG_FILE="input.csv"
NEW_FILE="output.csv"
#Append 'Create' DateTimeStamp to CSV for MySQL logging
$GAWK -v d="$TIMESTAMP" -F"," 'BEGIN {OFS = ","} {$6=d; print}' $ORIG_FILE > $NEW_FILE
rm -f $ORIG_FILE
More installation instructions found in the readme. And you can pretend that AWK natively supports CSV files. ( You can use this same trick with other UNIX line-oriented tools. head , tail and sort don't understand CSV either, but if you wrap them in csvquote you will be able to handle delimited line breaks correctly.)
AWK can be invoked from the command prompt by simply typing awk . On the command line, it is all lower case. This would result in the matching lines from the /etc/passwd file being printed to the command line. This is fairly basic, and we are using the default behavior of printing the matches.
bash [filename] runs the commands saved in a file. $@ refers to all of a shell script's command-line arguments. $1 , $2 , etc., refer to the first command-line argument, the second command-line argument, etc. Place variables in quotes if the values might have spaces in them.
You may add a comma to OFS
(Output Field Separator):
awk -F"," 'BEGIN { OFS = "," } {$6="2012-02-29 16:13:00"; print}' input.csv > output.csv
Output:
2012-02-29,01:00:00,Manhatten,New York,234,2012-02-29 16:13:00
2012-02-29,01:00:00,Manhatten,New York,843,2012-02-29 16:13:00
2012-02-29,01:00:00,Manhatten,New York,472,2012-02-29 16:13:00
2012-02-29,01:00:00,Manhatten,New York,516,2012-02-29 16:13:00
EDIT to answer the comment of SirOracle
:
From awk
man page:
-v var=val
--assign var=val
Assign the value val to the variable var, before execution of the program begins. Such
variable values are available to the BEGIN block of an AWK program.
So assign your date to a shell variable and use it inside awk
:
mydate=$(date)
awk -v d="$mydate" -F"," 'BEGIN { OFS = "," } {$6=d; print}' input.csv > output.csv
I'd do:
awk '{ printf("%s,2012-02-29 16:13:00\n", $0); }' input.csv > output.csv
This hard codes the value, but so does your code.
Or you can use sed
:
sed 's/$/,2012-02-29 16:13:00/' input.csv > output.csv
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With