Visit Our UNIX and Linux User Community


Update field value on a csv file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Update field value on a csv file
# 1  
Old 08-28-2013
Update field value on a csv file

Hi
I have a job status csv file. I want to update the status of the job in the file.
Below is the csv file
Code:
 
1,jobname1,in_progress,starttime,somthing,somthing
2,jobname2,completed,starttime,somthing,somthing
3,jobname3,failed,starttime,somthing,somthing
4,jobname1,not_started,starttime,somthing,somthing

I want to update the status of the job in the file for a pattern "1,jobname1" from in_progress to completed. Basically i want to update the 3rd field value (what ever it may be) of the line which matches the pattern "1,jobname1" with "completed"

ie, after the update the file should look like
Code:
 
1,jobname1,completed,starttime,somthing,somthing
2,jobname2,completed,starttime,somthing,somthing
3,jobname3,failed,starttime,somthing,somthing
4,jobname1,not_started,starttime,somthing,somthing

can I do this with awk ?
Thanks
# 2  
Old 08-28-2013
Code:
awk -F, '/1,jobname1,in_progress/ {$3="completed"} 1' filename

This User Gave Thanks to krishmaths For This Post:
# 3  
Old 08-28-2013
Quote:
Originally Posted by krishmaths
Code:
awk -F, '/1,jobname1,in_progress/ {$3="completed"} 1' filename

Thanks for the reply

I can take only "1,jobname1" as the search string and i want update any column on that line.

ie, I would like to search with string "1,jobname1" and update 3rd column with "completed" and 5th column with date +"%Y%m%d%H%M%S"
# 4  
Old 08-28-2013
Code:
awk -F, 'BEGIN{"date +\"%Y%m%d%H%M%S\""|getline d} /1,jobname1/{$3="completed";$5=d} 1' filename

This User Gave Thanks to krishmaths For This Post:
# 5  
Old 08-28-2013
Quote:
Originally Posted by krishmaths
Code:
awk -F, 'BEGIN{"date +\"%Y%m%d%H%M%S\""|getline d} /1,jobname1/{$3="completed";$5=d} 1' filename


this is working fine for me... Thanks a lot

is it possible to pass the value in a variable. I tried the below

Code:
 
awk -F, '/1,jobname1/ {$3="completed";$5="${temp_date}"} 1' OFS="," file

but instead of assigning the value from variable temp_date, I'm getting the string "${temp_date}" in the result.
# 6  
Old 08-28-2013
You need to use awk variable like this:

Code:
awk -F, -v td=$temp_date '/1,jobname1/ {$3="completed";$5=td} 1' OFS="," file

This User Gave Thanks to krishmaths For This Post:
# 7  
Old 08-28-2013
Quote:
Originally Posted by krishmaths
You need to use awk variable like this:

Code:
awk -F, -v td=$temp_date '/1,jobname1/ {$3="completed";$5=td} 1' OFS="," file

Thanks, its working for me

---------- Post updated at 01:25 PM ---------- Previous update was at 05:45 AM ----------

Quote:
Originally Posted by krishmaths
You need to use awk variable like this:

Code:
awk -F, -v td=$temp_date '/1,jobname1/ {$3="completed";$5=td} 1' OFS="," file


is it possible to pass the pattern in a variable

Code:
pattern_str="1,jobname1"
awk -F, -v td=$temp_date '/$pattern_str/ {$3="completed";$5=td} 1' OFS="," file


Last edited by vgersh99; 08-28-2013 at 03:56 PM..

Previous Thread | Next Thread
Test Your Knowledge in Computers #953
Difficulty: Medium
A memory-resident virus installs itself as part of the operating system when executed, after which it remains in RAM from the time the computer is booted up to when it is shut down.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to update value in field of out file using contents of another Ask

In the out.txt below I am trying to use awk to update the contents of $9.. If $9 contains a + or - then $8 of out.txt is used as a key to lookup in $2 of file. When a match ( there will always be one) is found the $3 value of that file is used to update $9 of out.txt separated by a :. So the... (6 Replies)
Discussion started by: cmccabe
6 Replies

2. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

3. Linux

How do I format a Date field of a .CSV file with multiple commas in a string field?

I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below: column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10 "12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in... (3 Replies)
Discussion started by: dhruuv369
3 Replies

4. Shell Programming and Scripting

Update the table using values from a csv file

i want to run update query for oracle which is in up.sql taking values from a.csv. I have implemented shell script to do it. extn="perl" ls -1 | while read file do echo "$file,$extn" > a.csv done up.sql contains update file_list set filename=$1 where extn=$2; The code to update is... (2 Replies)
Discussion started by: millan
2 Replies

5. UNIX for Dummies Questions & Answers

Update a CSV file with a predetermined value

Hi All Thanks for all your post and advice, you have been extremely helpful in the past. I have an issue where I'm stumped and am seeking a wider opinion. I'm relatively new to unix and have been tasked with collating a vast amount of data and presenting it within excel. Now the... (5 Replies)
Discussion started by: drestarr96
5 Replies

6. Shell Programming and Scripting

Update a field in a file based on condition

Hi i am new to scripting. i have a file file.dat with content as : CONTENT_STORAGE PERCENTAGE FLAG: /storage_01 64% 0 /storage_02 17% 1 I need to update the value of FLAG for a particular CONTENT_STORAGE value I have written the following code #!/bin/sh threshold=20... (1 Reply)
Discussion started by: kichu
1 Replies

7. Shell Programming and Scripting

how to update a .csv file?

Hi everyone, im a newbie so plz bare with me, i have this txt file which contains an output of a query from oracle. now i copied the string inside this txt file in a .csv file. i used this command in doing this process: echo "Fatal alerts:", $(cat a.txt) > test.csv now what i want to... (1 Reply)
Discussion started by: 4dirk1
1 Replies

8. Shell Programming and Scripting

Command to create and update csv file

Hi, I need to create a csv file to store oracle query output. This report need to be created on hourly basis. The csv file report format as "Report_22_Sep_09_13IST.csv". I have the oracle query. Now i need to create and move the oracle query output to the report row by row starting from 3rd row.... (6 Replies)
Discussion started by: Sekar1
6 Replies

9. Shell Programming and Scripting

replace a field in a CSV file

Hello all, I've a CSV file and need to replace 5th field if its value is "X". The exact requirement is to replace 5th field (column) with "Y" if a. it's value is "X" AND b. the line must start with ABC string i guess this can be done with awk. Pl help. For security reasons, the... (2 Replies)
Discussion started by: prvnrk
2 Replies

10. UNIX for Dummies Questions & Answers

How to insert ' in a field in CSV file

print "count,pub,prodline,group,sector,date,source" > $fname cat sp_log.summary.$firstday-$lastday.ProdlineSector | sed "s/^ *//g;s/ *$//g" >sp_log.summary.$firstday-$lastday cat sp_log.summary.$firstday-$lastday | sed "s/$/ $lastyy-$lastmm cache/;s/ /,/g" >> $fname cat $fname | sed... (1 Reply)
Discussion started by: shikhakaul
1 Replies

Featured Tech Videos