Compare two lines of csv files per line with output, if it is added, deleted or updated

Question

I have two csv files with 5 fields where the delimiter is a comma. I need to compare these files and get an output file with information about which lines are added, updated or deleted.

I found several batch scripts that perform comparisons using the fc or comp function, but this only gives the differences between the files. I have not found that related topics can give results about added, updated or deleted rows.

Can anyone help me with this?

Answer 1

This topic is interesting! Perhaps you, like me, find the FC command output format confusing and annoying, although it does provide the necessary information. The batch program below displays the result of the FC command and reorders it in such a way as to determine whether a new block of information has been added between the two lines of the source file or if the block of lines has been deleted from the source file, or in any other case the file has been changed (updated) . The hardest part is to choose a format for displaying information in a pleasant way, but I think my solution is pretty good!

@echo off rem FCOMP.BAT: Format FC output in a pleasant way rem Antonio Perez Ayala if "%~2" neq "" goto start echo Format FC output identifying added, deleted or updated sections echo/ echo FCOMP filename1 filename2 [/switches /for /FC /command] goto :EOF :start setlocal EnableDelayedExpansion set while=if not set do=goto endwhile set endwhile=goto while set "space= " set "spaces39= " fc %3 %4 %5 %6 %7 %8 %9 %1 %2 > differences.txt if %errorlevel% equ 1 call :FormatFC < differences.txt del differences.txt goto :EOF :FormatFC set /P line= set /P line= rem Process each set of differences :while %while% defined line %do% rem Load old and new sections of this set set line= set /P line= set old=0 :while1 %while% "!line:~0,5!" neq "*****" %do%1 set /A old+=1 set oldLine[%old%]=!line!%spaces39% set line= set /P line= %endwhile%1 :endwhile1 set line= set /P line= set new=0 :while2 %while% "!line:~0,5!" neq "*****" %do%2 set /A new+=1 set newLine[%new%]=!line!%space% set line= set /P line= %endwhile%2 :endwhile2 rem Identify the type of this set if %old% equ 2 ( echo ====== NEW SECTION ADDED ==================================================== echo/ echo(!oldLine[1]:~0,79! set /A new-=1 for /L %%i in (2,1,!new!) do echo( ^|!newLine[%%i]:~0,70! echo(!oldLine[2]:~0,79! ) else if %new% equ 2 ( echo OLD SECTION DELETED ========================================================== echo/ echo(---------!newLine[1]:~0,70! set /A old-=1 for /L %%i in (2,1,!old!) do echo -!oldLine[%%i]:~0,78! echo(---------!newLine[2]:~0,70! ) else ( rem both %old% and %new% gtr 2 echo ============================== SECTION UPDATED ============================== echo/ if %old% lss %new% ( for /L %%i in (1,1,%old%) do echo(!oldLine[%%i]:~0,39!^|!newLine[%%i]:~0,39! set /A old+=1 for /L %%i in (!old!,1,%new%) do echo(%spaces39%^|!newLine[%%i]:~0,39! ) else ( for /L %%i in (1,1,%new%) do echo(!oldLine[%%i]:~0,39!^|!newLine[%%i]:~0,39! set /A new+=1 for /L %%i in (!new!,1,%old%) do echo(!oldLine[%%i]:~0,39! ) ) rem Pass to next set of differences echo/ set /P line= set line= set /P line= %endwhile% :endwhile exit /B 

Antonio

Answer 2

EDIT: as stated, this is a variant of shell scripts, hope it can help others

Here is one of the options: I did not test it for performance with very large files:

 $ cat file1 1,'adam' 2,'chris' 6,'phil' 3,'charles' $ cat file2 2,'christopher' 6,'phil' 3,'chuck' 8,'sue',2 4,'mary' 21,'matt' 

- suppose the CSV file separator is a comma, and the first field for each record is the Primary key (unique value)

 $ comm -3 <(sort file1) <(sort file2) | sed -e 's/^[ \t]*//' | awk -F , '{if (a[$1]) {print "^"$1","} {a[$1] = $0}}' > data2.txt 

- Updates

 $ cat data2.txt | grep -E -f - file2 

- deletion

 $ cat data2.txt | grep -v -E -f - <(comm -2 -3 <(sort file1) <(sort file2)) 

- inserts

 $ cat data2.txt | grep -v -E -f - <(comm -1 -3 <(sort file1) <(sort file2))