Compare two files and update values accordingly

Compare two files and update values accordingly

1
NewbieNewbie
1

    May 29, 2007#1

    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? :twisted: 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.

    262
    MasterMaster
    262

      May 29, 2007#2

      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;
      }