Writing the output of a command to specific columns of a csv file, unix

Question

I wanted to write the output of command to specific columns (3rd and 5th) of the csv file.

#!/bin/bash
echo -e "Value,1\nCount,1" >> file.csv
echo "Header1,Header2,Path,Header4,Value,Header6" >> file.csv
sed 'y/ /,/' input.csv >> file.csv

input.csv in the above snippet will look something like this

1234567890 /training/folder
0325435287 /training/newfolder

Current output of file.csv

Value,1
Count,1
Header1,Header2,Path,Header4,Value,Header6
1234567890,/training/folder
0325435287,/training/newfolder

Expected Output of file.csv

Value,1
Count,1
Header1,Header2,Path,Header4,Value,Header6
,,/training/folder,,1234567890,
,,/training/newfolder,,0325435287,

Tangentially, you would generally prefer `printf` over `echo -e`, even in a Bash script. — tripleee, Nov 11 '21 at 07:27

score 3 · Answer 1 · answered Nov 11 '21 at 11:25

All the operations can be done in a single awk:

awk -v OFS=, -v pre="Value,1\nCount,1" -v hdr="Header1,Header2,Path,Header4,Value,Header6" '
   BEGIN {print pre; print hdr}
   {print "", "", $1, "", $2, ""}
' input.csv

Value,1
Count,1
Header1,Header2,Path,Header4,Value,Header6
,,i1234567890,,/training/folder,
,,0325435287,,/training/newfolder,

score 2 · Answer 2 · answered Nov 11 '21 at 10:43

With sed you could try following code. Which is using sed's capability of back reference.

sed -E 's/(^[^ ]*) +(.*$)/,,\2,,\1,/' Input_file

Explanation: Using -E option of sed to enable ERE(extended regular expressions) first. Then in main program using s option to perform substitution operation. In 1st part of substitution creating 2 back references(capability to catch values by using regex and keep them in temp buffer memory to be used later on while substituting it with in 2nd part of substitution). In 2nd part of substitution substituting whole line with 2 commas followed by 2nd capturing group\2 followed by 2 commas followed by 1st capturing group \1 following by ,.

BeardOverflow · Accepted Answer · 2021-11-11T07:18:18.500

1

You can use awk instead of sed

cat input.csv | awk '{print ",," $1 "," $2 ","}' >> file.csv

awk can process a stdin input by line to line. It implements a print function and each word is processed as a argument (in your case, $1 and $2). In the above example, I added ,, and , as an inline argument.

edited Nov 11 '21 at 07:18

answered Nov 11 '21 at 07:12

BeardOverflow

938
12
15

1

That's a [useless use of `cat`.](https://stackoverflow.com/questions/11710552/useless-use-of-cat) The same logic could trivially be implemented in `sed` just as well. – tripleee Nov 11 '21 at 07:24
@tripleee I don't know how to implement that logic using the `sed` command. – BeardOverflow Nov 11 '21 at 07:26
I think this is the easiest solution for me to understand. @BeardOverflow – Helium Nov 11 '21 at 11:28

score 0 · Answer 4 · answered Nov 11 '21 at 07:26

You can trivially add empty columns as part of your sed script.

sed 'y/ /,/;s/,/,,/;s/^/,,/;s/$/,/' input.csv >> file.csv

This replaces the first comma with two, then adds two up front and one at the end.

Your expected output does not look like valid CSV, though. This is also brittle in that it will fail for any file names which contain a space or a comma.

Writing the output of a command to specific columns of a csv file, unix

4 Answers4