Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Conditional replacement in CSV files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Conditional replacement in CSV files
# 1  
Old 07-31-2014
Conditional replacement in CSV files
Hello,

I have many CSV files with variable number of rows and columns.

Sample of few problematic CSV files.
Code:
,,Price,Price,Price,Price,Price,Price,Price,Price,Price,Qty
Date,Sl,AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,PriQueue,%busy
30/07/2014,1,AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,NA,0
30/07/2014,2,34,64,1,1,0.63,210.5,2.4,73,NA,71
30/07/2014,3,36,62,1,1,0.62,206.7,2.3,72,NA,70
30/07/2014,4,29,70,1,0,0.78,258.5,14.2,91,NA,26

Few CSV files have same values in 2nd and 3rd rows (from column 3rd to 10th). For those problematic CSV files, we need to replace those fields (in 3rd row from 3rd column to 10th column) with 4th row's 3rd column to 10th column.

Desired output
Code:
,,Price,Price,Price,Price,Price,Price,Price,Price,Price,Qty
Date,Sl,AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,PriQueue,%busy
30/07/2014,1,34,64,1,1,0.63,210.5,2.4,73,NA,0
30/07/2014,2,34,64,1,1,0.63,210.5,2.4,73,NA,71
30/07/2014,3,36,62,1,1,0.62,206.7,2.3,72,NA,70
30/07/2014,4,29,70,1,0,0.78,258.5,14.2,91,NA,26

Please advise!
Thanks a lot
Last edited by reddyr; 07-31-2014 at 09:52 PM..
# 2  
Old 08-01-2014
Code:
awk -F ',' '
NR == 2 {  p=($3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10); a=$0;
  getline; q=($3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10); b=$0;
  getline; r=($3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10);
  if(p == q) sub(q, r, b); print a; print b}1' file

This User Gave Thanks to SriniShoo For This Post:
reddyr
# 3  
Old 08-01-2014
Quote:
Originally Posted by SriniShoo
Code:
awk -F ',' '
NR == 2 {  p=($3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10); a=$0;
  getline; q=($3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10); b=$0;
  getline; r=($3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10);
  if(p == q) sub(q, r, b); print a; print b}1' file

This is a very nice solution as long as the sample input is representative of the actual data that will be processed. If fields 3 though 10 in line 3 contain any ERE special characters ([\()*+?{|ˆ$) or if those fields in line 4 contain any ampersand characters (&), the results will not be what was wanted.

If any of these are a problem, the OP needs to specify more clearly what characters are allowed in those fields on those lines.
# 4  
Old 08-01-2014
Thank you SriniShoo,

As Don Cragun correctly pointed out, unfortunately few CSVs contain % in those fields and the solution did not work. It works fine for the CSVs without special characters in those fields.

Pl advise. Many thanks!
# 5  
Old 08-01-2014
Please show us a sample 4 line file that didn't work. (Maybe I didn't get enough sleep last night, but I don't see why a % should cause a problem.) Please also give us the complete list of characters (other than upper- and lower-case alphabetic and numeric characters) that might appear in those fields on line3 and a similar list of characters that might appear in those fields on line 4.

What OS and version of awk are you using?
# 6  
Old 08-01-2014
Apologies... Though the values same but there're spaces in 2nd row which I failed to notice because I opened in excel.

Code:
,,Price,Price,Price,Price,Price,Price,Price,Price,Price,Qty
Date,Time,%AAA,    %BBB,    %CCC,   %DDD,   %EEE,   %FFF, %GGG, %HHH, Unit Value
30/07/2014,23:26:00,%AAA,%BBB,%CCC,%DDD,%EEE,%FFF,%GGG,%HHH,NA
30/07/2014,23:31:00,53,37,0,10,0.56,93.7,5.3,88,NA
30/07/2014,23:36:00,52,36,0,12,0.55,92.2,6.4,92,NA
30/07/2014,23:41:00,53,37,0,10,0.56,93.8,4.4,85,NA
30/07/2014,23:46:00,64,33,1,3,0.82,136.7,4.6,91,NA

Desired output
Code:
,,Price,Price,Price,Price,Price,Price,Price,Price,Price,Qty
Date,Time,%AAA,    %BBB,    %CCC,   %DDD,   %EEE,   %FFF, %GGG, %HHH, Unit Value
30/07/2014,23:26:00,53,37,0,10,0.56,93.7,5.3,88,NA
30/07/2014,23:31:00,53,37,0,10,0.56,93.7,5.3,88,NA
30/07/2014,23:36:00,52,36,0,12,0.55,92.2,6.4,92,NA
30/07/2014,23:41:00,53,37,0,10,0.56,93.8,4.4,85,NA
30/07/2014,23:46:00,64,33,1,3,0.82,136.7,4.6,91,NA

So I don't think it's issue with awk - I'm using GNU awk 4.1.1

Is it possible to compare strings "without" spaces but NOT removing those spaces in the files.? Please advise.

Many thanks!
Last edited by reddyr; 08-01-2014 at 06:40 PM..
# 7  
Old 08-01-2014
Building on SriniShoo's code, try:
Code:
awk -F ',' '
NR == 2 {  p=($3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10); a=$0; nsp=p; gsub(/ /, "", nsp)
  getline; q=($3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10); b=$0
  getline; r=($3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10)
  if(nsp == q) sub(q, r, b); print a; print b}1' file

This User Gave Thanks to Don Cragun For This Post:
reddyr
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. 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

2. Shell Programming and Scripting

Conditional replacement of columns in a text file

Hello scriping expert friends, I have 2 requirements on replacing fields of text files: I have lot of data with contents like below: Requirement-1: The digit after 0 should always be changed to 1 (3 Replies)
Discussion started by: magnus29
3 Replies

3. Shell Programming and Scripting

Compare 2 csv files in ksh and o/p the difference in a new csv file

(say) I have 2 csv files - file1.csv & file2.csv as mentioned below: file1.csv ID,version,cost 1000,1,30 2000,2,40 3000,3,50 4000,4,60 file2.csv ID,version,cost 1000,1,30 2000,2,45 3000,4,55 6000,5,70 The... (7 Replies)
Discussion started by: Naresh101
7 Replies

4. Shell Programming and Scripting

Comparing 2 CSV files and sending the difference to a new csv file

(say) I have 2 csv files - file1.csv & file2.csv as mentioned below: file1.csv ID,version,cost 1000,1,30 2000,2,40 3000,3,50 4000,4,60 file2.csv ID,version,cost 1000,1,30 2000,2,45 3000,4,55 6000,5,70 ... (1 Reply)
Discussion started by: Naresh101
1 Replies

5. Shell Programming and Scripting

Conditional replacement of a delimiter

Hello, I'm new to this forum but this seems like the place to ask this question. I have a pipe delimited data file with the fields except for the header being encased in double quotes. I found out that some of the fields have an trash pipe within the data itself. I'd like to conditionally... (4 Replies)
Discussion started by: samahs
4 Replies

6. Shell Programming and Scripting

conditional replacement

Hi all, I need a bash, sed, awk script or one liner to do the following task: This is the format of a text file: 2010-06-11 20:01 902656 HOP-W-100412-1.doc 2010-11-05 18:01 364447 NEX-W-101104-1 2010-07-06 10:01 64512 Cerintele 2010-07-06 10:01 599420 content 2010-07-19 14:01 1785344... (7 Replies)
Discussion started by: supervazi
7 Replies

7. Shell Programming and Scripting

Conditional tab replacement sed/awk

Hi I am struggling to find a solutions to this problem: I have a directory full of files and I wish to: read each line of each file and if any one line in those files is longer than 72 characters I want to replace any tab characters with a space character. Ive been... (3 Replies)
Discussion started by: benackland
3 Replies

8. Shell Programming and Scripting

HELP Need in SED/PERL conditional line replacement

Hi , I need some help on perl/sed conditional replacement The situation is like below . I have a file contents like below . AAA|BBB|CCC|DDD AAA|BCF|CCC|HHH AAA|BVF|JJJ|KKK Here in the above file . I know my second column value (taking "|" as my delimited ) Basically I have to... (3 Replies)
Discussion started by: robin.r888
3 Replies

9. UNIX for Dummies Questions & Answers

conditional renaming of files

Hello, I want to rename all the files in my directory according to the following criterion: IF the original filename does not end in 0 THEN rename the file to "original filname" but with the last character preceded by the string: _copy IF the original filename ends in 0 THEN take the... (2 Replies)
Discussion started by: juliette salexa
2 Replies

10. Shell Programming and Scripting

Need to compare two csv files values and write into another csv file

Hi all, Am new to scripting. So i just need your ideas to help me out. Here goes my requirement. I have two csv files 1.csv 2.csv abc,1.24 abc,1 def,2.13 def,1 I need to compare the first column of 1.csv with 2.csv and if matches then need to compare... (2 Replies)
Discussion started by: chinnahyd
2 Replies
Login or Register to Ask a Question