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,,[email protected],MacBook Pro,
C02G71X,HOSTNAME0001,[email protected],MacBook Pro,
FVFGH0S,,[email protected],MacBook Pro,
PC24Q,,[email protected],Macbook Air,

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

SN,HOSTNAME,OWNER,MODEL,USER
C02G1,,[email protected],MacBook Pro,
C02G71X,HOSTNAME0001,[email protected],MacBook Pro,username
FVFGH0S,,[email protected],MacBook Pro,
PC24Q,,[email protected],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,,[email protected],MacBook Pro,username
C02G71X,HOSTNAME0001,[email protected],MacBook Pro,username
FVFGH0S,,[email protected],MacBook Pro,username
PC24Q,,[email protected],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
– phranz, Commented Jan 26, 2022 at 8:25
You should copy/paste your script into shellcheck.net and fix the issues it tells you about. — Ed Morton
– Ed Morton, Commented Jan 26, 2022 at 12:19

danadam · Accepted Answer · 2022-01-26 01:04:51Z

1

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,,[email protected],MacBook Pro,
C02G71X,HOSTNAME0001,[email protected],MacBook Pro,foobar
FVFGH0S,,[email protected],MacBook Pro,
PC24Q,,[email protected],Macbook Air,

answered Jan 26, 2022 at 1:04

danadam

3,47022 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ed Morton Over a year ago

Upvoted but please read correct-bash-and-shell-script-variable-capitalization.

Federico Peralta Over a year ago

Thanks a lot , both alternatives worked like charm !

aborruso · Accepted Answer · 2022-01-26 07:49:58Z

0

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   |              | [email protected]    | MacBook Pro |      |
| C02G71X | HOSTNAME0001 | [email protected]  | MacBook Pro | tom  |
| FVFGH0S |              | [email protected] | MacBook Pro |      |
| PC24Q   |              | [email protected]   | Macbook Air |      |
+---------+--------------+---------------+-------------+------+

that gives to you

edited Jan 26, 2022 at 7:49

answered Jan 26, 2022 at 7:19

aborruso

5,8723 gold badges27 silver badges49 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related