shell / bash script - Add value to a single filtered row in csv - awk / sed

Question

sorry my english. Im working with a script for the onboarding process for new employees, something like a "zero-touch" and im facing some issues manipulating a csv with shell bash script.

Sample of my input csv tmp.csv:

SN,HOSTNAME,OWNER,MODEL,USER
C02G1,,aaa@aaa.la,MacBook Pro,
C02G71X,HOSTNAME0001,bbbb@bbb.com,MacBook Pro,
FVFGH0S,,cccc@cccc.com,MacBook Pro,
PC24Q,,ddd@ddd.com,Macbook Air,

Desired output (could be a new file or editing the input file):

SN,HOSTNAME,OWNER,MODEL,USER
C02G1,,aaa@aaa.la,MacBook Pro,
C02G71X,HOSTNAME0001,bbbb@bbb.com,MacBook Pro,username
FVFGH0S,,cccc@cccc.com,MacBook Pro,
PC24Q,,ddd@ddd.com,Macbook Air,

My script :

#!/bin/sh
# Get the current device's serial number
SERIAL=$(system_profiler SPHardwareDataType | awk '/Serial/ {print $4}')
# File name
tmpFile=$'tmp.csv'

# Download the CSV from Google Drive, file must be set to Shared With Anyone with Link (or Shared with Anyone)
curl -L -o $tmpFile 'https://docs.google.com/spreadsheets/d/XXXXXXXX/export?format=csv'

# Delete unwanted characters
sed -i'BAK' "s/$(printf '\r')\$//" $tmpFile

# Get username MAC
user=$(id -un)

while IFS=, read -r SN HOSTNAME OWNER MODEL USER
    do
      if [ "$SN" == "$SERIAL" ]
        then            
            hostname=$HOSTNAME
            awk -F, -v old="$USER" -v new="$user" '{gsub(old,new,$5);print $0}' $tmpFile > tmp3.csv
            fi
done < $tmpFile

sudo scutil --set HostName $hostname
sudo scutil --set LocalHostName $hostname
sudo scutil --set ComputerName $hostname
dscacheutil -flushcache

etc etc etc......

Output with my script:

SN,HOSTNAME,OWNER,MODEL,USER
C02G1,,aaa@aaa.la,MacBook Pro,username
C02G71X,HOSTNAME0001,bbbb@bbb.com,MacBook Pro,username
FVFGH0S,,cccc@cccc.com,MacBook Pro,username
PC24Q,,ddd@ddd.com,Macbook Air,username

SUMMARY: So what i need is to add $user on the column USER of the csv only in the row that matches SN with $SERIAL

THANKS IN ADVANCE, if someone feels that this code may help i could send it when I finished it!

If your USER is always the last column, you could do: `sed /"${sn}"'/s/,[^,]*$/,'"${user}"/ tmp.csv` — phranz, Jan 26 '22 at 08:25
You should copy/paste your script into http://shellcheck.net and fix the issues it tells you about. — Ed Morton, Jan 26 '22 at 12:19

score 1 · Answer 1 · answered Jan 26 '22 at 01:04

First, I don't know how you got the output with , as separators. I have to explicitly set it, otherwise awk is using space.

But back to the question, you can use two rules:

one that matches the row and updates 5th field,
another that matches everything and prints the whole line

$1 == sn { $5 = user }
{ print $0 }

In the script it will look like this:

#!/usr/bin/env bash

SN_TO_MATCH="C02G71X"
USER_TO_ADD="foobar"

awk -F, -v OFS=, -v sn="${SN_TO_MATCH}" -v user="${USER_TO_ADD}" '$1==sn {$5=user} {print $0}' tmp.csv

The output:

SN,HOSTNAME,OWNER,MODEL,USER
C02G1,,aaa@aaa.la,MacBook Pro,
C02G71X,HOSTNAME0001,bbbb@bbb.com,MacBook Pro,foobar
FVFGH0S,,cccc@cccc.com,MacBook Pro,
PC24Q,,ddd@ddd.com,Macbook Air,

Upvoted but please read [correct-bash-and-shell-script-variable-capitalization](https://stackoverflow.com/questions/673055/correct-bash-and-shell-script-variable-capitalization). — Ed Morton, Jan 26 '22 at 12:17

aborruso · Answer 2 · 2022-01-26T07:49:58.133

It's not awk, but it could useful for you. I'm using Miller

It' a simplified example

#!/bin/bash

serial="C02G71X"
user="tom"

mlr --csv put 'if($SN=="'"$serial"'"){$USER="'"$user"'"} then {$USER=""}' input.csv >output.csv

+---------+--------------+---------------+-------------+------+
| SN      | HOSTNAME     | OWNER         | MODEL       | USER |
+---------+--------------+---------------+-------------+------+
| C02G1   |              | aaa@aaa.la    | MacBook Pro |      |
| C02G71X | HOSTNAME0001 | bbbb@bbb.com  | MacBook Pro | tom  |
| FVFGH0S |              | cccc@cccc.com | MacBook Pro |      |
| PC24Q   |              | ddd@ddd.com   | Macbook Air |      |
+---------+--------------+---------------+-------------+------+

that gives to you

shell / bash script - Add value to a single filtered row in csv - awk / sed

2 Answers2