Search in csv file for Match

[zagix]

Hi,

I need help for solving this, on daily basis around 600 entries are reconciled manually.

I have two comma separated csv files, File1.csv & File2.csv, 1st row header Date,Particulars,Amount,Type,Balance.

Startiing from Column Particulars of File1.csv into column Particulars of File2.csv for search matching (alphanumeric string,match means partial 10% will do), and if it finds a partial match also it will display complete row for acceptance or rejection of row.

If accepted (y) then move complete row to new file from both the csv files to reconresult.csv If rejection is pressed searching new possibles in the column Particulars in File2.csv to the last record (around 600 rows of data), if no search if found, then scroll to next record in File1.csv

For search ignore start tags like WIPE,CLIENT,DIRECT,PAIDTO: because they are default remarks which will scroll to each and every record better to start after '/' ':' The best search will be characters type then numberic type.

1. Display complete row on screen because its manual one to one entry.
15-05-2017,WIPE/PPKKB17137737564/1328/TOP PLAST PRIV,100000,CR,200000,, -File1.csv
15-05-2017,WIPE/TOP PLAST,1000000,DR,500000,, - File2.csv
Accept Match [Y/N]?:

File1.csv
15-05-2017,WIPE/PPKKB17137737564/1328/TOP PLAST PRIV,100000,CR,200000,,
15-05-2017,WIPE/000015778881/10100002898/TECHNOLOGIES,450000,DR,545360,,
15-05-2017,WIPE/000015113345556000026796/CREDIT CARD,15300,DR,5463800

File2.csv
15-05-2017,WIPE/TOP PLAST,1000000,DR,500000,,
15-05-2017, WIPE/000015778881/TURBO,45000.00,CR,6673880,,
15-05-2017, WIPE/000034778483/CREDIT CARD,10000,DR,1275700,,
15-05-2017,WIPE/CREDIT CARD,15300,CR,7563700,,

Reconresult.csv
15-05-2017,WIPE/PPKKB17137737564/1328/TOP PLAST PRIV,100000,CR,200000,,
15-05-2017,WIPE/TOP PLAST,1000000,DR,500000,,

15-05-2017,WIPE/000015778881/10100002898/TECHNOLOGIES,450000,DR,545360,,
15-05-2017, WIPE/000015778881/TURBO,45000.00,CR,6673880,,

15-05-2017,WIPE/000015113345556000026796/CREDIT CARD,15300,DR,5463800
15-05-2017,WIPE/CREDIT CARD,15300,CR,7563700,,

Thanks in advance.

[Aacini]

This problem is interesting! Although there are several confusing points in your description, a program can be completed up to a certain degree with this description, so I wrote a preliminary solution.

I slightly modified the method for acceptance or rejection of matched rows: all partial matches in file2.csv are displayed for each record in file1.csv, so you need to insert the number of the desired matching row for acceptance, or zero for rejection.

Code: Select all

@echo off
setlocal EnableDelayedExpansion

rem Load data from file2.csv into "row2" array
echo Loading data from file2.csv, please wait...
set "i=0"
< NUL (for /F "skip=1 tokens=1,2* delims=," %%a in (file2.csv) do (
   set /A i+=1
   set "row2[!i!]=%%a,%%b,%%c"
   set /P "=!i!,"

   rem Separate Particulars column in slash-delimited fields into "part" array
   rem that contain the records in row2 that contain each one
   set "part=%%b"
   set "start="
   for %%p in ("!part:/=" "!") do (
      if not defined start (
         set "start=%%p"
      ) else (
         set "part[%%p]=!part[%%p]! !i! "
      )
   )
))
cls

rem Process the file1.csv

del Reconresult.csv 2>NUL
for /F "skip=1 tokens=1,2* delims=," %%a in (file1.csv) do (

   rem Show each record in file1.csv
   echo %%a,%%b,%%c

   rem Separate Particulars column in slash-delimited fields,
   rem test if a matching element exist in part/row2 arrays
   rem and show a menu with all matching row2's
   set "part=%%b"
   set "start="
   set "i=0"
   for %%p in ("!part:/=" "!") do (
      if not defined start (
         set "start=%%p"
      ) else if defined part[%%p] (

         rem There are at least one slash-delimited field match; list all of them
         for %%n in (!part[%%p]!) do (
            set /A i+=1
            echo !i!:   !row2[%%n]!
            set "rec[!i!]=%%n,%%p"
         )

             rem Separate the slash-delimited field in two words
      ) else for /F "tokens=1,2" %%x in (%%p) do (
         if defined part["%%x %%y"] (

            rem There are at least one partial two-words field match
            for %%n in (!part["%%x %%y"]!) do (
               set /A i+=1
               echo !i!:   !row2[%%n]!
               set "rec[!i!]=%%n,"%%x %%y""
            )
         ) else if defined part["%%x"] (

            rem There are at least one partial one-word field match
            for %%n in (!part["%%x"]!) do (
               set /A i+=1
               echo !i!:   !row2[%%n]!
               set "rec[!i!]=%%n,"%%x""
            )
         )
      )
   )

   rem If there were matches, let the user to select one
   if !i! equ 0 (
      echo No match
   ) else (
      set /P "match=Select match, 0 for no match: "
      (if !match! gtr 0 for /F %%m in ("!match!") do for /F "tokens=1,2 delims=," %%i in ("!rec[%%m]!") do (
         rem Output the records from file1.csv and file2.csv into result
         echo %%a,%%b,%%c
         echo !row2[%%i]!
         echo/
         rem Remove the elements from row2/part
         set "row2[%%i]="
         set "part[%%j]=!part[%%j]: %%i = !"
      )) >> Reconresult.csv
   )

   echo/
)

Note that this is just a prototype: there are several points missing, like what to do with the records in both files that don't matches. These details, and all required modifications, can be added later.

Antonio

[zagix]

Hi,

Thanks Antonio for helping.
It's completely reconciliation of entries, previously we used to make printouts and then rectify it manually.
I have attached the sample data to my dropbox kindly have look to it.

https://www.dropbox.com/s/8q1319btwyas44r/file1.csv?dl=0
https://www.dropbox.com/s/ymt7wsqhgos5duf/file2.csv?dl=0

Thanks in advance.

[zagix]

Hi,

Thanks Antonio for helping.
It's completely reconciliation of entries, previously we used to make printouts and then rectify it manually.
I have attached the sample data to my dropbox kindly have look to it.

https://www.dropbox.com/s/8q1319btwyas4 ... 1.csv?dl=0
https://www.dropbox.com/s/ymt7wsqhgos5d ... 2.csv?dl=0

Thanks in advance.