Compare two files and update values accordingly

[dizzybee]

I'm not sure if this has been tackled before.

I need outside-database text manipulation before uploading them. I have two text files, source and target. The macro should traverse the source from top to bottom and update the target accordingly. Im very aware UE is a text editor but can UE do this? Help???

The sequence is simple:

Update target.col1 with source.col3
If source.col1 isFound in target
And source.col2 = target.col1

SOURCE:

Code: Select all

8085452850   0105   2130
8088712153   0105   2130
8089299497   0105   2130
8088896207   0105   2004
8087341288   0105   2004
8088827733   0105   2004
8085248063   0105   2130
8083353821   0305   3107

TARGET:

Code: Select all

0105                        8085452850        070405B   
0105   8089299497   8088391797        070403B   
0105                        8089232509        070403B   
0305   8083353821   8086821969        070410B   
0105   8082447845   8088712153        070328R   
0105                        8085248063        070406B   
0105   8088827733   8083949416        070403R   
0105   8083353821   8083353821        070328R   

Just wishful thinking here.

[jorrasdk]

Since I am a "script man" and you don't mention your version of UE, I have no problems suggesting a solution that will work in UE version 13.

(But if you have an older version than 13, I'm sure Mofi will offer some hints on the best macro solution, give time).

Code: Select all

// traverseSourceUpdateTarget.js
// Created by: Jorrasdk, May 29th, 2007
// Use and modify freely. Released into public domain.
//
// The script runs through a source document, extracts values from certain lines.
// Then from these values, finds and replaces data in the target file.
// Both files should be in open tabs. If you do not want to hard code the file names,
// then arrange your tabs and for example use:
// var sourceDocIx = 0; /* first tab */
// var targetDocIx = 1; /* second tab */

var colSep = " "; /* modify this to "^t" (tab) if the columns are tab separated */
UltraEdit.ueReOn(); /* Let's use UE's own regexp syntax */

var sourceDocIx = getDocumentIndex("SOUrce.txt"); /* letter case is of no importance for getDocumentIndex */
var targetDocIx = getDocumentIndex("tarGET.txt");

if( (sourceDocIx!=-1) && (targetDocIx!=-1) ) /* Both have to be located as open files */
  traverseSourceDocUpdateTargetDoc();

/* Traverse sourceDoc and update targetDoc */
function traverseSourceDocUpdateTargetDoc() {
   var sourceDoc = UltraEdit.document[sourceDocIx]; /* for easy access */
   var targetDoc = UltraEdit.document[targetDocIx]; /* for easy access */

   sourceDoc.findReplace.regExp = true; /* we're going to find data using regular expressions */

   targetDoc.findReplace.regExp = false;
   targetDoc.findReplace.replaceAll = true;

   /* Find source lines like: "8085452850 0105 2130" */
   var sourceFind = "%[0-9]+"+colSep+"[0-9]+"+colSep+"[0-9]+"; /* refine if necessary */

   /* First go to top of source document */
   sourceDoc.top();

   while ( (sourceDoc.findReplace.find(sourceFind)) && ! sourceDoc.isEof() ) {
      var sourceHit = sourceDoc.selection; /* found new hit, extract selected text */

      /* extract values */
      var splitVal = colSep;
      if (colSep=="^t")
         splitVal = String.fromCharCode(9); /* tab value */
      var cols = sourceHit.split(splitVal); /* create array */

      var targetFind = cols[1] + colSep+ cols[0]; /* search key for targetDoc, for example: "0105 8085452850"*/
      var targetReplace = cols[2] + colSep + cols[0]; /* replace value for targetDoc, for example: "2130 8085452850"*/

      targetDoc.findReplace.replace(targetFind,targetReplace);
   }

   /* Reposition at the top */
   sourceDoc.top();
   targetDoc.top();
}


/* getDocumentIndex(filepath) */
/* If called without parameter = Find the tab index of the active document */
/* If called with parameter "filepath" = Find the tab index of that document. -1 = document not found */

function getDocumentIndex(filepath) {
   var tabindex = -1; /* start value */

   for (i = 0; i < UltraEdit.document.length; i++)
   {
      if(filepath) {
         if (UltraEdit.document[i].path.toLowerCase().indexOf(filepath.toLowerCase())>0) {
            tabindex = i;
            break;
         }
      }
      else {
         if (UltraEdit.activeDocument.path==UltraEdit.document[i].path) {
            tabindex = i;
            break;
         }
      }
   }
   return tabindex;
}