Updating a line in a large csv file, with sed/awk?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Updating a line in a large csv file, with sed/awk?
# 1  
Old 09-17-2009
Updating a line in a large csv file, with sed/awk?

I have an extremely large csv file that I need to search the second field, and upon matches update the last field...

I can pull the line with awk.. but apparently you cant use awk to directly update the file? So im curious if I can use sed to do this... The good news is the field I want to update is the last field, so I can just do a right to left search for "," and update to the right.

anyways, the details.
Code:
...
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field14 <This is the field I need to change
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13
...

awk line to pull the info I want...
Code:
awk -v ip=$ip 'NF > 13 {if ($2 == ip && $14 =="*") print $0}' file.csv

now, besides turning the above into 3-4 long bash variables... Is there any easy way to change field $14 in the above line? Smilie
# 2  
Old 09-17-2009
If how to directly apply to the file is your only question, then the answer is -i switch of sed can do it for you.
# 3  
Old 09-17-2009
Quote:
Originally Posted by thegeek
If how to directly apply to the file is your only question, then the answer is -i switch of sed can do it for you.
I know I can do that; just wondering if there is an easier way without creating two long variables with the awk command to use with sed.
# 4  
Old 09-18-2009
awk can emulate sed -i by making a temporary copy of original file. I believe sed is doing exactly the same with the -i switch.

Code:
$ cat file
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,Y.Y.Y.Y,feild3,,,,,,,,,,,* 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13

Code:
#!/bin/bash

# make temporary copy
cp file /tmp/file

# awk part
IP="Y.Y.Y.Y"
awk -v ip=$IP -F, -v OFS="," awk'$2~/^Y.Y.Y.Y$/ && $14~/*/ {$14="new value f14"} {print}' /tmp/file > file

# if necessary remove temporary file
rm /tmp/file

# 5  
Old 09-21-2009
Thanks for all the help, I was hoping there was an easier way to write out all this out... I guess thats a limitation of a bash script doing this sort of job. I went with 2 variables and a sed line to make the change i need.

Code:
...
  old_f=`awk -v host=$1 -F, 'NF > 13 {if ($4 == host && $14 == "*") print $0}' $MYLIST`
  new_f=`awk -v host=$1 -F, 'NF > 13 {if ($4 == host && $14 == "*") print $1","$2","$3","$4","$5","$6","$7","$8","$9","$10","$11","$12","$13","UPDATED}' $MYLIST` 
  if ! sed -i s/${old_f}/${new_f}/ $MYLIST
  then
    echo "BLAH" >> ${DATE}.log
  fi
...

# 6  
Old 09-21-2009
Simply try:

Code:
# make temporary copy
cp $MYLIST /tmp/file

# awk part
IP="^Y.Y.Y.Y$"
awk -v ip=$IP -F, -v OFS="," '$2~ip && $14~/*/  {$14="new value f14"} {print}' /tmp/file > $MYLIST

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

sed awk: split a large file to unique file names

Dear Users, Appreciate your help if you could help me with splitting a large file > 1 million lines with sed or awk. below is the text in the file input file.txt scaffold1 928 929 C/T + scaffold1 942 943 G/C + scaffold1 959 960 C/T +... (6 Replies)
Discussion started by: kapr0001
6 Replies

2. Shell Programming and Scripting

Honey, I broke awk! (duplicate line removal in 30M line 3.7GB csv file)

I have a script that builds a database ~30 million lines, ~3.7 GB .cvs file. After multiple optimzations It takes about 62 min to bring in and parse all the files and used to take 10 min to remove duplicates until I was requested to add another column. I am using the highly optimized awk code: awk... (34 Replies)
Discussion started by: Michael Stora
34 Replies

3. Shell Programming and Scripting

sed and awk not working on a large record file

Hi All, I have a very large single record file. abc;date||bcd;efg|......... pqr;stu||record_count;date when i do wc -l on this file it gives me "0" records, coz of missing line feed. my problem is there is an extra pipe that is coming at the end of this record like... (6 Replies)
Discussion started by: Gurkamal83
6 Replies

4. Shell Programming and Scripting

Converting txt file into CSV using awk or sed

Hello folks I have a txt file of information about journal articles from different fields. I need to convert this information into a format that is easier for computers to manipulate for some research that I'm doing on how articles are cited. The file has some header information and then details... (8 Replies)
Discussion started by: ksk
8 Replies

5. Shell Programming and Scripting

awk/sed/something else for csv file

Hi, I have a filename.csv in which there are 3 colums, ie: Name ; prefixnumber ; number root ; 020 ; 1234567 user1,2,3 ; 070 ; 7654321 What I want is to merge colum 2 and 3 that it becomes 0201234567 or even better +31201234567 so the country number is used and drop the leading 0.... (9 Replies)
Discussion started by: necron
9 Replies

6. Shell Programming and Scripting

Script for updating a .csv file

Hello guys, I have one .csv file with all the file names(.c,.h,.sm,.txt etc)..... Now i have compared two baseline in UCM and got a report in .txt file,which looks like this...... DIFFBL INT_1029_Rel6.0.0.90@\DIR_PVOB STD_6.0.0.50_UCM@\DIR_PVOB (COMPONENT TEXT) Comparing the following: ... (14 Replies)
Discussion started by: suvenduperl
14 Replies

7. Shell Programming and Scripting

Using awk/sed in handling csv file.

Please study the below script and the output Script: echo "Minimum ${host} ${process} response time=${min} ms" >> ${OUTDIR}/${OUTFILE}; echo "Maximum ${host} ${process} response time=${max} ms" >> ${OUTDIR}/${OUTFILE}; echo "Average ${host} ${process} response time=${avg} ms" >>... (0 Replies)
Discussion started by: ajincoep
0 Replies

8. Shell Programming and Scripting

Need help with a script to process a CSV file using SED and AWK

I get a CSV file every day with 2 columns and multiple rows ex: date1,date2 ( both the fields are varchar fields) This data has to be updated in a table which is being done manually and i want to automate that. 1. I have to select all the data from the prod table( 2 columns { date1,date2}) into... (4 Replies)
Discussion started by: kkb
4 Replies

9. Shell Programming and Scripting

Sed or awk script to remove text / or perform calculations from large CSV files

I have a large CSV files (e.g. 2 million records) and am hoping to do one of two things. I have been trying to use awk and sed but am a newbie and can't figure out how to get it to work. Any help you could offer would be greatly appreciated - I'm stuck trying to remove the colon and wildcards in... (6 Replies)
Discussion started by: metronomadic
6 Replies

10. Shell Programming and Scripting

awk updating one file with another, comparing, updating

Hello, I read and search through this wonderful forum and tried different approaches but it seems I lack some knowledge and neurones ^^ Here is what I'm trying to achieve : file1: test filea 3495; test fileb 4578; test filec 7689; test filey 9978; test filez 12300; file2: test filea... (11 Replies)
Discussion started by: mecano
11 Replies
Login or Register to Ask a Question