Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Merge 2 CSV files using sed

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merge 2 CSV files using sed
# 1  
Old 04-19-2011
Merge 2 CSV files using sed
Help in writing a script using sed which updates fileOne with the contents from fileTwo

Example:
Contents of fileOne
Code:
1,111111
2,897823
3,235473
4,222222

Contents of fileTwo
Code:
1,111111,A,1,2
4,222222,A,2,2
5,374632,A,3,2
6,374654,A,4,2

Final File should be:
Code:
1,111111,A,1,2
2,897823
3,235473
4,222222,A,2,2
5,374632,A,3,2
6,374654,A,4,2

Thanks in advance...
Moderator's Comments:
Mod Comment
Please use code tags when posting data and code samples!
Last edited by vgersh99; 04-19-2011 at 07:30 AM.. Reason: code tags, please!
# 2  
Old 04-19-2011
Best done with awk:

Code:
 
awk -F"," 'NR==FNR {a[$1]=$0} {a[$1]=$0;} END { for(i=1;i in a;i++) print a[i]}' file1 file2

# 3  
Old 04-19-2011
Thanks for the reply... Could you please point to any tutorial which explains about the funtionalities used in your command... like array 'a[$1]', 'in a',...
# 4  
Old 04-19-2011
www.grymoire.com/Unix/Awk.html
# 5  
Old 04-19-2011
you can try sed but awk is more usefull if subject is condition.

Code:
# cat fileOne
1,111111
2,897823
3,235473
4,222222

Code:
# cat fileTwo
1,111111,A,1,2
4,222222,A,2,2
5,374632,A,3,2
6,374654,A,4,2

Code:
# ./justdoit fileOne fileTwo
1,111111,A,1,2
2,897823
3,235473
4,222222,A,2,2
5,374632,A,3,2
6,374654,A,4,2

Code:
# cat justdoit
## justdoit ##
#!/bin/bash
for i in $(sed 's/^\([0-9]\),.*/\1/' $1)
 do
   [ $(sed -n "/^$i,/p" $2|grep "$i,") ] || (sed "$i i $(sed -n "/^$i,/p" $1) " $2 >$2tmp ; mv $2tmp $2 )
 done ; more $2

regards
ygemici
# 6  
Old 04-20-2011
Thanks,

But in my example i need to compare only the second column, I am posting the original contents of my files

file1:
Code:
"20","9900000007"
"3","9900000001"
"3","9900000002"
"4","9900000003"

file2
Code:
"20","9900000007","1","A","2"
"3","9900000001","1","A","2"
"13","9900000004","0","A","2"
"17","9900000005","1","A","2"

outPutFile:
Code:
"20","9900000007","1","A","2"
"3","9900000001","1","A","2"
"3","9900000002"
"4","9900000003"
"13","9900000004","0","A","2"
"17","9900000005","1","A","2"


I have written this script, which has 2 problem
Code:
#!/bin/bash
while read updateline
do
   updateField=`echo $updateline | awk -F "," '{print $2}'`
   while read initialline
   do
      initialField=`echo $initialline | awk -F "," '{print $2}'`
      if [ "$initialField" == "$updateField" ]; then
         sed -e "s/$initialline/$updateline/" <file1 
      fi
   done < file1 
done < file2 >tmpUpdateFile

output of this script:
Code:
"20","9900000007","1","A","2"
"3","9900000001"
"3","9900000002"
"4","9900000003"
"20","9900000007"
"3","9900000001","1","A","2"
"3","9900000002"
"4","9900000003"

This is not the expected result, problem are,
1. During each loop, the content is getting appended
2. In my script, in the else part how to append the variable $updateline.

Thanks....

Moderator's Comments:
Mod Comment Please use [code] and [/code] tags when posting code, data or logs etc. to preserve formatting and enhance readability, thanks.
Last edited by zaxxon; 04-20-2011 at 04:14 AM.. Reason: code tags & indention
# 7  
Old 04-20-2011
try below awk
Code:
awk -F"," '{
if(NR == FNR){
  _[$1]=$2
}
else{
  if(exists _[$1]){
    _[$1]="---"
  }
  print $0
  }
}
END{
	for(i in _){
		if(_[i] != "---" && _[i] != "")
			print i","_[i]
	}
}' a b | sort -t"," -n
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Merge the three csv files as one according to first coloumn.

I have three files with similar pattern i need to merge all the coloumns side by side from all three files according to the first coloumn example as shown below I mentioned 5 coloumns only in example but i have around 15 coloumns in each file. file1: Name,Samples,Error,95RT,90RT... (4 Replies)
Discussion started by: Raghuram717
4 Replies

2. Shell Programming and Scripting

Compare and merge two big CSV files

Hi all, i need help. I have two csv files with a huge amount of data. I need the first column of the first file, to be compared with the data of the second, to have at the end a file with the data not present in the second file. Example File1: (only one column) profile_id 57036226... (11 Replies)
Discussion started by: SirMannu
11 Replies

3. Shell Programming and Scripting

I am trying to merge all csv files from source path into 1 file

I am trying to merge all csv files from source path into one single csv file in target. but getting error message: hadoop fs -cat /user/hive/warehouse/stage.db/PK_CLOUD_CHARGE/TCH-charge_*.csv > /user/hive/warehouse/stage.db/PK_CLOUD_CHARGE/final/TCH_pb_charge.csv getting error message:... (0 Replies)
Discussion started by: cplusplus1
0 Replies

4. UNIX for Dummies Questions & Answers

Merge two csv files using column name

Hi all, I have two separate csv files(comma delimited) file 1 and file 2. File 1 contains PAN,NAME,Salary AAAAA5467D,Raj,50000 AAFAC5467D,Ram,60000 BDCFA5677D,Kumar,90000 File 2 contains PAN,NAME,Dept,Salary ASDFG6756T,Karthik,ABC,450000 QWERT8765Y,JAX,CDR,780000... (5 Replies)
Discussion started by: Nivas
5 Replies

5. Shell Programming and Scripting

Merge CSV files

I have lot of csv file collected from script like below : Name of files (some examples) there are thousands of it: 192.168.0.123_251_18796_1433144473.csv 192.168.0.123_251_18796_1433144772.csv 192.168.0.123_251_18796_1433145073.csv 192.168.0.123_251_18796_1433145372.csvContent of each... (5 Replies)
Discussion started by: rk4k
5 Replies

6. Shell Programming and Scripting

Merge 2 last columns in a csv file using sed or perl

Hello everyone, want to merge 2 last columns 3rd and 4rd (if it exists). I have this 1;2;1;1 2;3;1;1 1;1;2 1;2;3;4 1;1;2 1;2;3;1 Desired output: 1;2;1 1 2;3;1 1 1;1;2 1;2;3 4 1;1;2 1;2;3 1 (3 Replies)
Discussion started by: satir
3 Replies

7. UNIX for Dummies Questions & Answers

Need help combining txt files w/ multiple lines into csv single cell - also need data merge

:confused:Hello -- i just joined the forums. I am a complete noob -- only about 1 week into learning how to program anything... and starting with linux. I am working in Linux terminal. I have a folder with a bunch of txt files. Each file has several lines of html code. I want to combine... (2 Replies)
Discussion started by: jetsetter
2 Replies

8. Shell Programming and Scripting

Merge *.csv files, each in separate sheets

Does anyone know how to Merge *.csv files, each in seperate sheets? (7 Replies)
Discussion started by: frhling
7 Replies

9. Shell Programming and Scripting

Merge CSV files and create a column with the filename from the original file

Hello everyone!! I am not completely new to shell script but I havent been able to find the answer to my problem and I'm sure there are some smart brains here up for the challenge :D. I have several CSV files that I need to combine into one, but I also need to know where each row came from.... (7 Replies)
Discussion started by: fransanchezoria
7 Replies

10. Shell Programming and Scripting

Merge 2 csv files with awk

I have 2 files pipe delimted and want to merge them based on a key e.g file 1 123$aaa$yyy$zzz 345$xab$yzy$zyz 456$sss$ttt$foo 799$aaa$ggg$dee file 2 123$hhh 345$ddd 456$xxx 888$zzz so if the key is the first field, and the result should be the common key between file 1 and 2 (6 Replies)
Discussion started by: loloAix
6 Replies
Login or Register to Ask a Question