Sponsored Content
Top Forums Shell Programming and Scripting Replace value in csv file with match from another file Post 302867301 by RKos on Wednesday 23rd of October 2013 09:36:58 PM
Old 10-23-2013
Question Replace value in csv file with match from another file

Good morning everyone and hello unix.com!

I'm trying to solve a problem and was unable to find a solution after a forum research. So hopefully you can help me get this solved.

I have these two csv files:

file1.csv
ID,REFERENCE,STATUS
1,2,0
2,4,1
3,1,0
file2.csv
REFERENCE,FIRST_NAME,LAST_NAME
1,John,Smith
2,Mary,James
3,Steve,Adams
4,Michael,Angelo
What I'm trying to achieve is to replace the 'REFERENCE' column in file1.csv with the respective line from file2.csv (matching 'REFERENCE'). So the output I want is:
1,2,Mary,James,0
2,4,Michael,Angelo,1
3,1,John,Smith,0
I have played around with the JOIN command in conjunction with SORT, but to no avail yet. Does anyone have an idea how to do that?

Thanks a lot,
RKos
 

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

csv file to array, match field1, replace in flat file field1 to field2

Hello, i am bit stuck with making script for automatic procedure. Case: Two files. One is flat file, other is csv file. csv file has two column/fields with comma delimited data. Here is what i need (explained way) CSV file: field1 | field2 "hello","byebye" "hello2","byebye2"... (23 Replies)
Discussion started by: frankie_konin
23 Replies

3. Shell Programming and Scripting

How to replace match data in 2 file.

How to replace match data in 2 file. to new file My log.txt (100,000 line) 11415304503฿90255050001฿3฿23/02/2009 13:12:38฿17/04/2009 13:04:59฿27/03/2009 4:13:54 11494069063฿90259730001฿3฿27/10/2008 13:14:21฿02/11/2009 14:34:01฿09/10/2009 4:13:26 11497298316฿90825970008฿5฿29/03/2009... (5 Replies)
Discussion started by: ooilinlove
5 Replies

4. Shell Programming and Scripting

search and replace in csv file

I have csv file where I want the second column has to be replaced with value 1. Source file 919568760477,1,2011-07-11T22:34:27.000+05:30, 919557735692,2,2011-07-11T22:36:16.000+05:30, 917417384969,2,2011-07-11T22:33:26.000+05:30, Final file ... (30 Replies)
Discussion started by: dondilip
30 Replies

5. Shell Programming and Scripting

Match list of strings in File A and compare with File B, C and write to a output file in CSV format

Hi Friends, I'm a great fan of this forum... it has helped me tone my skills in shell scripting. I have a challenge here, which I'm sure you guys would help me in achieving... File A has a list of job ids and I need to compare this with the File B (*.log) and File C (extend *.log) and copy... (6 Replies)
Discussion started by: asnandhakumar
6 Replies

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

7. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

8. Shell Programming and Scripting

sed : replace Nth match in a file

I have a situation where a file "config.txt" looks like this Servername: OS: Serername: OS: Servername: OS: .... .... ... Servername: OS: looking for the sed syntax to replace the "Nth" occurrence of Servername (i would apply the same logic to OS as well), want to replace the Nth... (4 Replies)
Discussion started by: alldbest
4 Replies

9. UNIX for Beginners Questions & Answers

How to count lines of CSV file where 2 fields match variables?

I'm trying to use awk to count the occurrences of two matching fields of a CSV file. For instance, for data that looks like this... Joe,Blue,Yes,No,High Mike,Blue,Yes,Yes,Low Joe,Red,No,No,Low Joe,Red,Yes,Yes,Low I've been trying to use code like this... countvar=`awk ' $2~/$color/... (4 Replies)
Discussion started by: nmoore2843
4 Replies

10. Shell Programming and Scripting

Matching two fields in two csv files, create new file and append match

I am trying to parse two csv files and make a match in one column then print the entire file to a new file and append an additional column that gives description from the match to the new file. If a match is not made, I would like to add "NA" to the end of the file Command that Ive been using... (6 Replies)
Discussion started by: dis0wned
6 Replies
CSV(3pm)						User Contributed Perl Documentation						  CSV(3pm)

NAME
Class::CSV - Class based CSV parser/writer SYNOPSIS
use Class::CSV; my $csv = Class::CSV->parse( filename => 'test.csv', fields => [qw/item qty sub_total/] ); foreach my $line (@{$csv->lines()}) { $line->sub_total('$'. sprintf("%0.2f", $line->sub_total())); print 'Item: '. $line->item(). " ". 'Qty: '. $line->qty(). " ". 'SubTotal: '. $line->sub_total(). " "; } my $cvs_as_string = $csv->string(); $csv->print(); my $csv = Class::CSV->new( fields => [qw/userid username/], line_separator => " "; ); $csv->add_line([2063, 'testuser']); $csv->add_line({ userid => 2064, username => 'testuser2' }); DESCRIPTION
This module can be used to create objects from CSV files, or to create CSV files from objects. Text::CSV_XS is used for parsing and creating CSV file lines, so any limitations in Text::CSV_XS will of course be inherant in this module. EXPORT None by default. METHOD
CONSTRUCTOR parse the parse constructor takes a hash as its paramater, the various options that can be in this hash are detailed below. Required Options o fields - an array ref containing the list of field names to use for each row. there are some reserved words that cannot be used as field names, there is no checking done for this at the moment but it is something to be aware of. the reserved field names are as follows: "string", "set", "get". also field names cannot contain whitespace or any characters that would not be allowed in a method name. Source Options (only one of these is needed) o filename - the path of the CSV file to be opened and parsed. o filehandle - the file handle of the CSV file to be parsed. o objects - an array ref of objects (e.g. Class::DBI objects). for this to work properly the field names provided in fields needs to correspond to the field names of the objects in the array ref. o classdbi_objects - depreciated use objects instead - using classdbi_objects will still work but its advisable to update your code. Optional Options o line_separator - the line seperator to be included at the end of every line. defaulting to " " (unix carriage return). new the new constructor takes a hash as its paramater, the same options detailed in parse apply to new however no Source Options can be used. this constructor creates a blank CSV object of which lines can be added via add_line. ACCESSING lines returns an array ref containing objects of each CSV line (made via Class::Accessor). the field names given upon construction are available as accessors and can be set or get. for more information please see the notes below or the perldoc for Class::Accessor. the lines accessor is also able to be updated/retrieved in the same way as individual lines fields (examples below). Example retrieving the lines: my @lines = @{$csv->lines()}; removing the first line: pop @lines; $csv->lines(@lines); sorting the lines: @lines = sort { $a->userid() <=> $b->userid() } @lines: $csv->lines(@lines); sorting the lines (all-in-one way): $csv->lines([ sort { $a->userid() <=> $b->userid() } @{$csv->lines()} ]); Retrieving a fields value there is two ways to retrieve a fields value (as documented in Class::Accessor). firstly you can call the field name on the object and secondly you can call "get" on the object with the field name as the argument (multiple field names can be specified to retrieve an array of values). examples are below. my $value = $line->test(); OR my $value = $line->get('test'); OR my @values = $line->get(qw/test test2 test3/); Setting a fields value setting a fields value is simmilar to getting a fields value. there are two ways to set a fields value (as documented in Class::Accessor). firstly you can simply call the field name on the object with the value as the argument or secondly you can call "set" on the object with a hash of fields and their values to set (this isn't standard in Class::Accessor, i have overloaded the "set" method to allow this). examples are below. $line->test('123'); OR $line->set( test => '123' ); OR $line->set( test => '123', test2 => '456' ); Retrieving a line as a string to retrieve a line as a string simply call "string" on the object. my $string = $line->string(); new_line returns a new line object, this can be useful for to "splice" a line into lines (see example below). you can pass the values of the line as an ARRAY ref or a HASH ref. Example my $line = $csv->new_line({ userid => 123, domainname => 'splicey.com' }); my @lines = $csv->lines(); splice(@lines, 1, 0, $line); OR splice(@{$csv->lines()}, 1, 0, $csv->new_line({ userid => 123, domainname => 'splicey.com' })); add_line adds a line to the lines stack. this is mainly useful when the new constructor is used but can of course be used with any constructor. it will add a new line to the end of the lines stack. you can pass the values of the line as an ARRAY ref or a HASH ref. examples of how to use this are below. Example $csv->add_line(['house', 100000, 4]); $csv->add_line({ item => 'house', cost => 100000, bedrooms => 4 }); OUTPUT string returns the object as a string (CSV file format). print calls "print" on string (prints the CSV to STDOUT). SEE ALSO
Text::CSV_XS, Class::Accessor AUTHOR
David Radunz, <david@boxen.net> COPYRIGHT AND LICENSE
Copyright 2004 by David Radunz This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. perl v5.10.0 2007-02-08 CSV(3pm)
All times are GMT -4. The time now is 07:54 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy