24

How do you add a column to the end of a CSV file with using a string in a variable?

input.csv

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

output.csv

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

awk.sh

#!/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.

awk.sh result

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!

Updated awk.sh

#!/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
Improve this question
2
  • Isn't it 'ManhattAn' rather than 'ManhattEn'? Commented Feb 29, 2012 at 21:30
  • Probably is, I was just quickly typing it for an example. Commented Feb 29, 2012 at 21:42

4 Answers 4

Reset to default
28

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks @Birei. I wasn't sure what OFS was but it makes sense now. Tried your code and it worked.
Do you know how I can add the date through a variable instead of the hard coded timestamp? I'll add my revised code above to see it better.
@Birei, I found you code is useful , I have packaged your script and uploaded as #sparrow plugin so that everyone could reuse it - sparrowhub.org/info/csv-add-column
@Birei, nice one thanx. How can anyone include the quotes in the string to be inserted? I wanted to add the string "2.4.0" (inluding quotes) in the second column of a .csv file, but the code below does not work: awk -F"," 'BEGIN { OFS = "," } {$2="\"2.4.0"\"; print}' test.csv > output.csv
...and the following works but removes the last dot from my 2.4.0 string awk -F"," 'BEGIN { OFS = "," } {$2="\""2.4.0"\""; print}' test.csv > output.csv
|
19

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
Improve this answer

1 Comment

or: awk -v date="2012-02-29 16:13:00" -v OFS=, '{print $0, date}'
4

You can set the OFS (output field seperator):

awk -F"," 'BEGIN { OFS = "," } ; {$6="2012-02-29 16:13:00" OFS $6; print}' input.csv >output.csv

which gives me:

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,
Improve this answer

1 Comment

Thanks Jörg Beyer. I removed the OFS $6 using @Birei example below to get rid of the trailing comma. I'm just trying to figure out how to add a variable in place of the hard coded date. I updated the question above with an example.
-1

If anyone wants to create csv file through shell with column names: where first input stored in variables from_time, to_time.

example:insert two timestamps with from_time and to_time as column names with respective values -

CODE-

FROM_TIME=2020-02-06T00:00:00
TO_TIME=2020-02-07T00:00:00
{ echo -e "$FROM_TIME,$TO_TIME";}>input1.csv
{ echo -e "from_time,to_time"; cat input1.csv;} > input.csv

first line storing the values second line responsible for adding column name

Improve this answer

1 Comment

You should edit your answer, it's not clear and it contains errors (second echo has a unmatched double quote, for example)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.