Need help comparing two files and outputting to a new file the matching items

BigGee69 · Oct 11, 2018#12018-10-11T14:01+00:00

Sorry, I am not too familiar with code and I am new to UltraEdit. I am sure I have overlooked an obvious solution.
I have two CSV files. One is a public domain type file and I want to utilize the results to validate records in our current ERP system.

Table one contains 6 million lines with approximately 350 columns. Table 2 contains 21,000 lines of 10 or 11 digit numbers.

In table one the search of the 10 or 11 digit numbers from table 2 is contained in the first column.

So every line where there is a match I want to create a new table with all 21,000 matching lines with the 350 columns all output into the new table.

So where the numbers in table 2 are found in table 1, bring back those found records to a new table with only those records.

Thanks in advance.

Mofi · Oct 15, 2018#22018-10-15T06:04+00:00

This task done by database applications within in a very short time as they are designed for such database queries tasks can be done also with an UltraEdit script. I wrote some macros many years ago to search for lines containing one of the strings in a second opened file anywhere in line or at a specific position, i.e. within a specific context. And I in the last years I wrote also several UltraEdit scripts for same tasks which is better than using a macro as executed faster. But at the moment I am extremely busy and don't have time to adapt one of the existing published scripts in this forum for this task. So you have to adapt one of these scripts by yourself or wait that somebody else do the work for you or I get some time to do it which is most likely not before next weekend.

In the meantime I recommend using one or more times Find in Files or regular Find with option List lines containing string because an appropriate Perl regular expression search string with an OR expression containing the numbers in file NPIListing.csv could be also used for this task. The script files in Find strings with a regular expression and output them to new file could be also used for this task. The multiple usage of Find in Files or regular Find with option List lines containing string with appropriate Perl regular expression search string or one of the scripts is definitely the better choice if this task needs to be done only once and not periodically in future.

Oct 20, 2018#32018-10-20T20:22+00:00

This task can be done with following script using English UltraEdit.

Code: Select all

var sNPIListing = "C:\\Temp\\NPIListing.csv";

if (UltraEdit.document.length > 0)
{
   UltraEdit.perlReOn();
   UltraEdit.insertMode();
   UltraEdit.columnModeOff();

   UltraEdit.document[0].selectAll();
   var asNumbers = UltraEdit.document[0].selection.split("\r\n");
   if (!asNumbers[asNumbers.length-1].length) asNumbers.pop();
   // Remove first line with the string NPIL.
   if (asNumbers.length > 0) asNumbers.splice(0,1);
   UltraEdit.document[0].top();

   UltraEdit.frInFiles.directoryStart="";
   UltraEdit.frInFiles.useEncoding=false;
   UltraEdit.frInFiles.searchInFilesTypes=sNPIListing;
   UltraEdit.frInFiles.filesToSearch=0;
   UltraEdit.frInFiles.ignoreHiddenSubs=false;
   UltraEdit.frInFiles.displayLinesDoNotMatch=false;
   UltraEdit.frInFiles.matchCase=true;
   UltraEdit.frInFiles.reverseSearch=false;
   UltraEdit.frInFiles.matchWord=false;
   UltraEdit.frInFiles.openMatchingFiles=false;
   UltraEdit.frInFiles.useOutputWindow=false;
   UltraEdit.frInFiles.searchSubs=false;
   UltraEdit.frInFiles.regExp=true;

   var nNumber = 0;
   var nTotalNumbers = asNumbers.length;
   while (nNumber < asNumbers.length)
   {
      var sFind = "^(?:";
      var bAddVerticalBar = false;
      var nLastNumber = ((nNumber+50) < nTotalNumbers) ? (nNumber+50) : nTotalNumbers;
      while(nNumber < nLastNumber)
      {
         if (bAddVerticalBar) sFind += "|";
         sFind += asNumbers[nNumber].replace(/([.+])/g,"\\$1");
         bAddVerticalBar = true;
         nNumber++;
      }
      sFind += ")\\b";
      UltraEdit.frInFiles.find(sFind);
   }

   UltraEdit.activeDocument.top();
   UltraEdit.activeDocument.findReplace.mode=0;
   UltraEdit.activeDocument.findReplace.matchCase=true;
   UltraEdit.activeDocument.findReplace.matchWord=false;
   UltraEdit.activeDocument.findReplace.regExp=true;
   UltraEdit.activeDocument.findReplace.searchDown=true;
   if (typeof(UltraEdit.activeDocument.findReplace.searchInColumn) == "boolean")
   {
      UltraEdit.activeDocument.findReplace.searchInColumn=false;
   }
   UltraEdit.activeDocument.findReplace.preserveCase=false;
   UltraEdit.activeDocument.findReplace.replaceAll=true;
   UltraEdit.activeDocument.findReplace.replaceInAllOpen=false;
   UltraEdit.activeDocument.findReplace.replace("^(?:(?:-----|Find|Found|Search).*\\r\\n)+","");

   UltraEdit.activeDocument.top();
   UltraEdit.activeDocument.findReplace.replace("^.+?\\(\\d+\\): ","");
   UltraEdit.activeDocument.top();
}

Please edit at top of the script the path of the file NPIListing.csv containing 6 million lines or copy the file into directory C:\Temp\. For each backslash (directory separator) one more backslash must be entered for a valid path string.

The file NPIL.csv with the about 21,000 lines (numbers/values) must be opened in UltraEdit as first file (most left on file tabs bar).

The second file should be the script file with the code above. Run the script with a click on item Play script on ribbon tab / contemporary menu Advanced or with a click on menu item Run active script in menu Scripting on using toolbar/menu mode with traditional menus.

Let me know if you are interested in comments explaining how the script works.