[SOLVED] Update a CSV file to drop the first number and insert a decimal place in a particular column

Issue

I need help to perform the following

My CSV file looks like this

900001_10459.jpg,036921,Initiated
900002_10454.jpg,027964,Initiated
900003_10440.jpg,021449,Initiated
900004_10440.jpg,016650,Initiated
900005_10440.jpg,013929,Initiated

What I need to do is generate a new csv file to be as follows

900001_10459.jpg,3692.1,Initiated
900002_10454.jpg,2796.4,Initiated
900003_10440.jpg,2144.9,Initiated
900004_10440.jpg,1665.0,Initiated
900005_10440.jpg,1392.9,Initiated

if I was to do this as a test

echo '036921' | awk -v range=1 '{print substr($0,range+1)}' | sed 's/.$/.&/'

I get

3692.1

Can anyone help me so I can incorporate that, (or anything similar) to change my CSV file?

Solution

Using awk and with the conditions specified in the comment, you can use:

$ awk -F, '{ printf "%s,%06.1f,%s\n", $1, $2 / 10, $3 }' data
900001_10459.jpg,3692.1,Initiated
900002_10454.jpg,2796.4,Initiated
900003_10440.jpg,2144.9,Initiated
900004_10440.jpg,1665.0,Initiated
900005_10440.jpg,1392.9,Initiated
$

With the printf format string providing the commas, there's no need to set OFS (because OFS is not used by printf).

Answered By - Jonathan Leffler

Answer Checked By - Robin (WPSolving Admin)