Sponsored Content
Top Forums Shell Programming and Scripting Compare and merge two big CSV files Post 303021857 by SirMannu on Sunday 19th of August 2018 03:24:14 AM
Old 08-19-2018
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)
Code:
profile_id
57036226
57036230
57109826
57241366
57241374

File2: (has 7 columns; the data in File1 are present in the second, third, fourth and fifth columns)
Code:
Id                    FederationIdentifier    Profile_ID__c    Contact.Profile_ID__c    Contact.Account.Profile_ID__c    IsActive    LastModifiedDate
005b000000150mxAAA    57036226                57036226        57036226                57036226                        true        2018-05-24
0050X000007K4p7QAC    56564502                56564502        56564502                56564502                        true        2018-04-12
0050X000007JxYOQA0    56994082                56994082        56994082                56994082                        true        2018-04-10
0050X000007K3UrQAK    57241366                57241366        57241366                57241366                        true        2018-04-11
0050X000007K3UcQAK    57109826                57109826        57109826                57109826                        true        2018-04-11
0050X000007K4d1QAC    58699731                58699731        58699731                58699731                        true        2018-04-12

Output file:
Code:
profile_id
57036230
57241374

Output file is a file with the data of File1 not present in File2.


I've tried with join but not works...he put in the output file all the data of the second file without any kind of compare:
Code:
join -t, -v 2 <(sort file1) <(sort file2) > file 3


Last edited by vbe; 08-19-2018 at 04:55 AM..
 

10 More Discussions You Might Find Interesting

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

2. UNIX for Advanced & Expert Users

best method to compare 2 big files in unix

Hi , I have a requirement to compare 2 files which can contain 40 million or more records and more than 20 fields to compare . Currently I am using awk scripting , and since awk has a memory issue, I am not able to process file more than 10 million records. Any suggestions or pointers to... (7 Replies)
Discussion started by: rashmisb
7 Replies

3. Shell Programming and Scripting

Compare and Merge files

Hi All, I have two different files as shown below separated by a "|". I need to compare the first column from both the files and if they match merge both the columns. File 1 "S00172012"|"CHRONIC RENAL FAILURE"|""|"I" "S00159962"|"SUBENDO INFRC-INIT EPISD"|""|"I" "S00255303"|"BENIGN... (6 Replies)
Discussion started by: nua7
6 Replies

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

5. Shell Programming and Scripting

Compare two files and merge into third

Hello: Newbie with Awk. Trying to compare two files and merge data based on CID. Please see the input file format and desired output. Any help is appreciated. TIA Input File1 CID1 --- TYP1 --- DCN1 --- INDATE1 --- IN-DATA1 CID2 --- TYP2 --- DCN2 --- INDATE2 --- IN-DATA2 CID3 ---... (6 Replies)
Discussion started by: wincrazy
6 Replies

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

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

8. Shell Programming and Scripting

Compare two big files for differences using Linux

Hello everybody Looking for help in comparing two files in Linux(files are big 800MB each). Example:- File1 has below data $ cat file1 5,6,3 2.1.4 1,1,1 8,9,1 File2 has below data $ cat file2 5,6,3 8,9,8 1,2,1 2,1,4 (1 Reply)
Discussion started by: shanul karim
1 Replies

9. UNIX for Beginners Questions & Answers

Compare two big files for differences using Linux

Hello everybody Looking for help in comparing two files in Linux(files are big 800MB each). Example:- File1 has below data $ cat file1 5,6,3 2.1.4 1,1,1 8,9,1 File2 has below data $ cat file2 5,6,3 8,9,8 1,2,1 2,1,4 (8 Replies)
Discussion started by: shanul karim
8 Replies

10. 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
Text::CSV::Encoded::Coder::EncodeGuess(3pm)		User Contributed Perl Documentation	       Text::CSV::Encoded::Coder::EncodeGuess(3pm)

NAME
Text::CSV::Encoded::Coder::EncodeGuess - Text::CSV::Encoded coder class using Encode::Guess SYNOPSIS
use Text::CSV::Encoded coder_class => 'Text::CSV::Encoded::Coder::EncodeGuess'; use Spreadsheet::ParseExcel; my $csv = Text::CSV::Encoded->new(); $csv->encoding( ['ucs2', 'ascii'] ); # guessing ucs2 or ascii? $csv->encoding_to_combine('shiftjis'); my $excel = Spreadsheet::ParseExcel::Workbook->Parse( $file ); my $sheet = $excel->{Worksheet}->[0]; for my $row ( $sheet->{MinRow} .. $sheet->{MaxRow} ) { my @fields; for my $col ( $sheet->{MinCol} .. $sheet->{MaxCol} ) { my $cell = $sheet->{Cells}[$row][$col]; push @fields, $cell->{Val}; } $csv->combine( @fields ) or die; print $csv->string, " "; } DESCRIPTION
This module is inherited from Text::CSV::Encoded::Coder::Encode. USE
Except for 2 attributes, same as Text::CSV::Encoded::Coder::Encode. encoding_in $csv = $csv->encoding_in( $encoding_list_ref ); The accessor to an encoding for pre-parsing CSV strings. If no encoding is given, returns current $encoding, otherwise the object itself. $encoding_list_ref = $csv->encoding_in() When you pass a list reference, it might guess the encoding from the given list. $csv->encoding_in( ['shiftjis', 'euc-jp', 'iso-20022-jp'] ); If it cannot guess the encoding, the first encoding of the list is used. encoding $csv = $csv->encoding( $encoding_list_ref ); $encoding_list_ref = $csv->encoding(); You can pass a list reference to this attribute only: * For list data consumed by combine(). * For list reference returned by getline(). In other word, in "combine" and "print", it might guess an encoding for the passing list data. If it cannot guess the encoding, the first encoding of the list is used. SEE ALSO
Encode, Encode::Guess AUTHOR
Makamaka Hannyaharamitu, <makamaka[at]cpan.org> COPYRIGHT AND LICENSE
Copyright 2008-2010 by Makamaka Hannyaharamitu This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. perl v5.14.2 2010-04-26 Text::CSV::Encoded::Coder::EncodeGuess(3pm)
All times are GMT -4. The time now is 12:48 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy