Sponsored Content
Top Forums UNIX for Beginners Questions & Answers Reconciling two CSV files using shell scripting Post 303041171 by hustler on Saturday 16th of November 2019 05:20:42 AM
Old 11-16-2019
Quote:
Originally Posted by Scrutinizer
Try:

Code:
awk '
  NR==FNR {                                         # When reading the first file (then NR is equal to FNR)
    A[$1]=$0                                        # Store the first file in array A with key $1
    next
  } 

  FNR==1 {                                          # On the first line of the second file
    split($0,Header)                                # Split the header labels in array "Header"
    $1=$1 OFS "diff columns"                        # Create the first 2 field headers
    for(i=2; i<=NF; i++)
      $i=sprintf("f1_%s,f2_%s,%s_diff",$i, $i, $i)  # Create the rest of the field headers
    print                                           # Print the field headers
  } 

  FNR>1 {                                           # Processing the content of file 2
    diffs=""                                        # Set the differences to ""
    if($1 in A) {                                   # if the key in $1 of file2 also occurs in file1
      split(A[$1], F)                               # Split the corresponding line of file 1 into Fields in array F
      for(i=2; i<=NF; i++) {                        # For field 2 until the last field
        if($i!=F[i])                                # if there is a value difference for that field
          diffs=diffs (diffs?OFS:"") Header[i]      # Add the corresponding header label to the differences
        $i=F[i] OFS $i OFS (F[i]-$i)                # Prepend the value of file1 and append the subtraction of file1 val - file val
      } 
      $1=$1 OFS "\"" diffs "\""                     # When all differences found, append them to field 1
      print                                         # print the result
    }
  }
' FS=', *' OFS=, file1 file2                        # set FS to a comma with spaces, set OFS to a comma and read file 1 and file2

Code:
Key,diff columns,f1_value1,f2_value1,value1_diff,f1_Value2,f2_Value2,Value2_diff,f1_Value3,f2_Value3,Value3_diff,f1_Value4,f2_Value4,Value4_diff,...
A,"Value2,Value3,Value4",50,50,0,100,80,20,50,45,5,40,50,-10,...


Hey Rudic,

Thanks for the help but still I am not getting the desired output. It seems code is just reading file 1.

Along with that I am getting a fatal error.
Code:
{FILENAME=file2.csv FNR=2} FATAL: function 'diffs' not defined

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help with shell scripting for accepting .csv files as CLA

I want to automate test script on shell scripting. There are 2 .csv files named account.csv and balance.csv.These files needs to passed as command line arguments and the following logic needs to applied further. Any account with a balance that was due before Oct 23, 2007 has an overdue... (2 Replies)
Discussion started by: coolguy123
2 Replies

2. Shell Programming and Scripting

How to insert a sequence number column inside a pipe delimited csv file using shell scripting?

Hi All, I need a shell script which could insert a sequence number column inside a dat file(pipe delimited). I have the dat file similar to the one as shown below.. |A|B|C||D|E |F|G|H||I|J |K|L|M||N|O |P|Q|R||S|T As shown above, the column 4 is currently blank and i need to insert sequence... (5 Replies)
Discussion started by: nithins007
5 Replies

3. Shell Programming and Scripting

Shell scripting:from text file to CSV

Hello friends, I have a file as follows: "empty line" content1 content2 content3 content1 content2 content3 content1 content2 content3 It starts with an empty line, how can i get a csv like this: (12 Replies)
Discussion started by: kraterions
12 Replies

4. Shell Programming and Scripting

How to create or convert to pdf files from csv files using shell script?

Hi, Can anyone help me how to convert a .csv file to a .pdf file using shell script Thanks (2 Replies)
Discussion started by: ssk250
2 Replies

5. Shell Programming and Scripting

How to calculate avg values of csv file using shell scripting .?

hi all i have a reporting work and i want it to be automated using shell scripting kindly let me know how can i make that possibe . eg data are :... (2 Replies)
Discussion started by: Avinash shaw
2 Replies

6. Shell Programming and Scripting

How to calculate average of csv using shell scripting?

Hi, I need to calculate the average of the following values using shell scripitng. Can anyone please suggest a solution? ... (10 Replies)
Discussion started by: karan pratap si
10 Replies

7. Shell Programming and Scripting

Shell script for field wise record count for different Files .csv files

Hi, Very good wishes to all! Please help to provide the shell script for generating the record counts in filed wise from the .csv file My question: Source file: Field1 Field2 Field3 abc 12f sLm 1234 hjd 12d Hyd 34 Chn My target file should generate the .csv file with the... (14 Replies)
Discussion started by: Kirands
14 Replies

8. Shell Programming and Scripting

Need a piece of shell scripting to remove column from a csv file

Hi, I need to remove first column from a csv file and i can do this by using below command. cut -f1 -d, --complement Mytest.csv I need to implement this in shell scripting, Whenever i am using the above command alone in command line it is working fine. I have 5 files in my directory and... (3 Replies)
Discussion started by: Samah
3 Replies

9. Shell Programming and Scripting

Read csv file, convert the data and make one text file in UNIX shell scripting

I have input data looks like this which is a part of a csv file 7,1265,76548,"0102:04" 8,1266,76545,"0112:04" I need to make the output data should look like this and the output data will be part of text file: 7|1265000 |7654899 |A| 8|12660000 |76545999 |B| The logic behind the... (6 Replies)
Discussion started by: RJG
6 Replies

10. UNIX for Beginners Questions & Answers

Export Oracle multiple tables to multiple csv files using UNIX shell scripting

Hello All, just wanted to export multiple tables from oracle sql using unix shell script to csv file and the below code is exporting only the first table. Can you please suggest why? or any better idea? export FILE="/abc/autom/file/geo_JOB.csv" Export= `sqlplus -s dev01/password@dEV3... (16 Replies)
Discussion started by: Hope
16 Replies
bdiff(1)						      General Commands Manual							  bdiff(1)

NAME
bdiff - diff for large files SYNOPSIS
file1 file2 [n] DESCRIPTION
compares two files and produces output identical to what would be produced by (see diff(1)), specifying changes that must be made to make the files identical. is designed for handling files that are too large for but it can be used on files of any length. processes files as follows: o Ignore lines common to the beginning of both files. o Split the remainder of each file into n-line segments, then execute on corresponding segments. The default value of n is 3500. Command-Line Arguments recognizes the following command-line arguments: file1 file2 Names of two files to be compared by If file1 or file2 (but not both) is standard input is used instead. n If a numeric value is present as the third argument, the files are divided into n-line segments before processing by Default value for n is 3500. This option is useful when 3500-line segments are too large for processing by Silent option suppresses diagnostic printing by but does not suppress possible error messages from If the n and arguments are both used, the n argument must precede the option on the command line or it will not be properly recognized. EXTERNAL INFLUENCES
Environment Variables determines the language in which messages are displayed. If is not specified in the environment or is set to the empty string, the value of is used as a default for each unspecified or empty vari- able. If is not specified or is set to the empty string, a default of "C" (see lang(5)) is used instead of If any internationalization variable contains an invalid setting, behaves as if all internationalization variables are set to "C". See environ(5). International Code Set Support Single- and multi-byte character code sets are supported. DIAGNOSTICS
Standard input was specified for both files. Only one file can be specified as standard input. A non-numeric value was specified for the n (third) argument. EXAMPLES
Find differences between two large files: and and place the result in a new file named Do the same, but limit file length to 1400 lines; suppress error messages: WARNINGS
produces output identical to output from and makes the necessary line-number corrections so that the output looks like it was processed by However, depending on where the files are split, may or may not find a fully minimized set of file differences. FILES
SEE ALSO
diff(1). bdiff(1)
All times are GMT -4. The time now is 01:20 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy