Find value from one file in another file

[spaceBAR]

UEStudio 6.20a+2

In JavaScript that I can use in UEStudio, Will someone please show me how to find a value from one file ( let's say opened in tab 4 ) in a 2nd file ( let's say opened in tab 5 ).

I have two tab delimited files open( both files are same format ) that have 77 columns, the 1st three columns look like this:
UPDATE "90021" "645809"

Every column in each file has the double quotes except the 1st column.

I need to take the value from the 1st file and the 3rd column( for example-645809 ) for every row and see if it can be found in the same column on any row in the 2nd file and if so write that complete line from the 2nd file( all columns ) to another new window in UEStudio.

TIA for your assistance/help.

[jorrasdk]

Hello spaceBAR

Take a look at a script I posted recently: traverseSourceUpdateTarget.js...

You might find inspiration in this for a solution to your problem. If you get stuck then tell us, but in a reply could you pls. paste in a more elaborate sample of your files. (and use the bbcode tags [­code][/code] to surround the file samples). Thanks.

[spaceBAR]

Thanks for your assistance!!

This is what I've came up with:

Code: Select all

/* findValueFromSourceInTarget.js

 Script reads through source file, and finds matching 3rd column in target
 Then writes complete line from target to a another window.
 ALL files should be in open tabs. If you do not want to hard code the file names,
 then use the relational tab numbers for the files you have open       */
 
var sourceFileTab = 3; /* seed with window tab# file is in  */
var targetFileTab = 4; /* seed with window tab# file is in */
var colSep        = String.fromCharCode(9); /* TAB character      */
var LF            = String.fromCharCode(13); /* LineFeed character */

UltraEdit.unixReOn(); /* designate regexp syntax style */

if( (sourceFileTab!=-1) && (targetFileTab!=-1) ) /* Validate that both source & target file are open */
  findValueFromSourceFileInTargetFile();

/* Function to get 3rd col from source file and find in 3rd col in target file and write to next tab */
function findValueFromSourceFileInTargetFile() {
 var sourceFile = UltraEdit.document[sourceFileTab];
 var targetFile = UltraEdit.document[targetFileTab];
 sourceFile.findReplace.regExp = true;
 targetFile.findReplace.regExp = true;

 var sourceFind = "^[A-Z]+\t[\"0-9]+\t[\"0-9]+\t";

 sourceFile.top();
 while ( (sourceFile.findReplace.find(sourceFind)) && ! sourceFile.isEof() ) {
   var sourceHit = sourceFile.selection; /* seed var with value found */
        UltraEdit.activeDocument.write(sourceHit.concat(String.fromCharCode(13)));
   var cols = sourceHit.split(colSep);
        UltraEdit.activeDocument.write(cols[2].concat(String.fromCharCode(13)));
//   var targetFind = "^[A-Z]+\t[\"0-9]+\t"+cols[2]; /* search key from sourcefile, for example: *TAB*TAB"191909"TAB */
   var targetFind = "^[A-Z]+\t[\"0-9]+\t"; /* search key from sourcefile, for example: *TAB*TAB"191909"TAB */

//     UltraEdit.activeDocument.write(targetFind.concat(String.fromCharCode(13))); /* write search key to tab for debugging      */

     if ( targetFile.findReplace.find(targetFind) ) {
     var targetHit = targetFile.selectLine;
     UltraEdit.activeDocument.write(targetHit); /* write found line to next tab      */
     }
     else {
     UltraEdit.activeDocument.write("Not found in Target!!!!"); /* write found line to next tab      */
     }
 }

 /* Reposition at the top */
 sourceFile.top();
 targetFile.top();
}

This is the source in tab 3:

Code: Select all

UPDATE   "21090"   "630188"   "GFA CORPORATION"   

This is the target in tab 4( same data ):

Code: Select all

UPDATE   "21090"   "630188"   "GFA CORPORATION"   

This is the output in the active window:

Code: Select all

UPDATE   "21090"   "630188"   
"630188"
Not found in Target!!!!

What am I doing incorrect?????????

tia

[jorrasdk]

Real backslashes in strings in Javascript should always be escaped. Also embedded double quotes.

Example - this was in your script:

Code: Select all

 var sourceFind = "^[A-Z]+\t[\"0-9]+\t[\"0-9]+\t";

and it should be:

Code: Select all

  var sourceFind = "^[A-Z]+\\t[\"0-9]+\\t[\"0-9]+\\t";

Also this part:

Code: Select all

     var targetHit = targetFile.selectLine;
     UltraEdit.activeDocument.write(targetHit); /* write found line to next tab      */

should be

Code: Select all

      targetFile.selectLine();
      var targetHit = targetFile.selection;
      UltraEdit.activeDocument.write(targetHit); /* write found line to next tab      */


I repost your script with these and other small changes. Do a compare.

Code: Select all

/* findValueFromSourceInTarget.js
 
 Script reads through source file, and finds matching 3rd column in target
 Then writes complete line from target to a another window.
 ALL files should be in open tabs. If you do not want to hard code the file names,
 then use the relational tab numbers for the files you have open       */

var sourceFileTab = 3; /* seed with window tab# file is in  */
var targetFileTab = 4; /* seed with window tab# file is in */
var colSep        = String.fromCharCode(9); /* TAB character      */
var LF            = String.fromCharCode(13); /* LineFeed character */

UltraEdit.unixReOn(); /* designate regexp syntax style */

if( (sourceFileTab!=-1) && (targetFileTab!=-1) ) /* Validate that both source & target file are open */
  findValueFromSourceFileInTargetFile();

/* Function to get 3rd col from source file and find in 3rd col in target file and write to next tab */
function findValueFromSourceFileInTargetFile() {
  var sourceFile = UltraEdit.document[sourceFileTab];
  var targetFile = UltraEdit.document[targetFileTab];
  sourceFile.findReplace.regExp = true;
  targetFile.findReplace.regExp = true;

  var sourceFind = "^[A-Z]+\\t[\"0-9]+\\t[\"0-9]+\\t";

  sourceFile.top();
  while ( (sourceFile.findReplace.find(sourceFind)) && ! sourceFile.isEof() ) {
    var sourceHit = sourceFile.selection; /* seed var with value found */
    UltraEdit.activeDocument.write(sourceHit.concat(LF));
    var cols = sourceHit.split(colSep);
    UltraEdit.activeDocument.write(cols[2].concat(LF));
   
    var targetFind = "^[A-Z]+\\t[\"0-9]+\\t"+cols[2]+"\\t"; /* search key from sourcefile, for example: *TAB*TAB"191909"TAB */

    if ( targetFile.findReplace.find(targetFind) ) {
      targetFile.selectLine();
      var targetHit = targetFile.selection;
      UltraEdit.activeDocument.write(targetHit); /* write found line to next tab      */
    } else {
      UltraEdit.activeDocument.write("Not found in Target!!!!"); /* write found line to next tab      */
    }
  }

  /* Reposition at the top */
  sourceFile.top();
  targetFile.top();
}


[spaceBAR]

Still not finding in target tab, So I changed the find to be even less restrictive, these are lines 34 through the next "if":

Code: Select all

    var targetFind = "^[A-Z]+\\t";
//    var targetFind = "^[A-Z]+\\t[\"0-9]+\\t"+cols[2]+"\\t"; /* search key from sourcefile, for example: *TAB*TAB"191909"TAB */
    UltraEdit.activeDocument.write(targetFind.concat(LF));
   
    if ( targetFile.findReplace.find(targetFind) ) {

Still not finding anything, Data in tab 3 & 4 is same, this is output to Active Window:

Code: Select all

UPDATE   "21090"   "630188"   
"630188"
^[A-Z]+\t
Not found in Target!!!!

tab 3 & 4 data:

Code: Select all

UPDATE   "21090"   "630188"   "GFA CORPORATION"   

I know it's probably right in front of me but I still don't see what I'm doing incorrect...

tia

[jorrasdk]

Two things:

Add a top() for target file just before the find:

Code: Select all

    targetFile.top();
   
    if ( targetFile.findReplace.find(targetFind) ) {


and just to make sure, When you say

Code: Select all

var sourceFileTab = 3; /* seed with window tab# file is in  */
var targetFileTab = 4; /* seed with window tab# file is in */


you know that the "tab index" in the UltraEdit.document[] array starts from 0 ?

So what you say is in fact: sourceFileTab = 3 is the 4th tab and targetFileTab = 4 is the 5th tab.

If you really mean tab 3 and 4 the document index values should be

Code: Select all

var sourceFileTab = 2; /* seed with window tab# file is in  */
var targetFileTab = 3; /* seed with window tab# file is in */


[spaceBAR]

Thanks so much for you assistance!!!!

I got it to working, this is the code:

Code: Select all

/* findValueFromSourceInTarget.js
 
 Script reads through source file, and finds matching 3rd column in target
 Then writes complete line from target to a another window.
 ALL files should be in open tabs. If you do not want to hard code the file names,
 then use the relational tab numbers for the files you have open       */

var sourceFileTab  = 2; /* seed with window tab# file is in              */
var targetFileTab  = 3; /* seed with window tab# file is in              */
var outputFoundTab = 4; /* seed with window tab# for writing found data  */
var debugTab       = 5; /* seed with window tab# for writing debug data  */
var colSep         = String.fromCharCode(9);  /* TAB character      */
var LF             = String.fromCharCode(13); /* LineFeed character */

UltraEdit.unixReOn(); /* designate regexp syntax style */

// Validate that UES windows to use are open are open */
if( (sourceFileTab!=-1) && (targetFileTab!=-1) && (outputFoundTab!=-1) && (debugTab!=-1) )
  findValueFromSourceFileInTargetFile();

/* Function to get 3rd col from source file and find in 3rd col in target file and
   write complete line it was found on to output tab */
function findValueFromSourceFileInTargetFile() {
  var sourceFile = UltraEdit.document[sourceFileTab];
  var targetFile = UltraEdit.document[targetFileTab];
  var outputFile = UltraEdit.document[outputFoundTab];
  var debugFile  = UltraEdit.document[debugTab];
  sourceFile.findReplace.regExp = true;
  targetFile.findReplace.regExp = true;

  var sourceFind = "^[A-Z]+\\t[\"0-9]+\\t[\"0-9]+\\t";

  sourceFile.top();
  while ( (sourceFile.findReplace.find(sourceFind)) && ! sourceFile.isEof() ) {
    var sourceHit = sourceFile.selection;  /* value found in sourcefile   */
    debugFile.write(sourceHit.concat(LF)); /* write info to debug tab     */
   
    var cols = sourceHit.split(colSep);    /* extract delimited cols into array elements */
    debugFile.write(cols[2].concat(LF));   /* write info to debug tab     */

    /* search key from sourcefile- .start @beginning of each row
                                   .then match any Uppercase letters until a TAB
                                   .then match TAB
                                   .then match double quotes and digits 0-9 until a TAB
                                   .then match value extracted from sourcefile ex. "191909"
                                   .then match TAB             */
    var targetFind = "^[A-Z]+\\t[\"0-9]+\\t"+cols[2]+"\\t";
   
    debugFile.write(targetFind.concat(LF)); /* write info to debug tab     */
    debugFile.write(LF);

    targetFile.top();
    if ( targetFile.findReplace.find(targetFind) ) {
      targetFile.selectLine();
      var targetHit = targetFile.selection;
      outputFile.bottom();
      outputFile.write(targetHit); /* write complete row that info was found on to output tab      */
    } else {
      debugFile.write("Not found in Target!!!!"); /* write error to debug tab  */
    }
  }
}

But it is SO slow, it took over an hour to process 1254 rows, for the source file I have 1254 rows, so I pasted the same rows in the target, so obviously it found 1254 matches and correctly wrote those rows to the "output" tab.

Well I say correctly, NOT exactly, It always drops the first TAB( it is right after the word "UPDATE" ) on a row on all rows written except for the first row, for example this is the first 5 rows in the sourcefile:

Code: Select all

UPDATE   "99883"   "191909"   "SHELEY ENTERPRISES"
UPDATE   "99885"   "651429"   "COLD WATER INDUSTRIES"
UPDATE   "21090"   "434957"   "ADVANCED MAILING SVC"
UPDATE   "21090"   "428918"   "EINS SERVICES"
UPDATE   "21090"   "650297"   "GOLD STAR PEST CONTROL INC"

This is the first 5 rows it wrote to the "output" tab, notice the first TAB is missing on ALL rows except the first row:

Code: Select all

UPDATE   "99883"   "191909"   "SHELEY ENTERPRISES"
UPDATE"99885"   "651429"   "COLD WATER INDUSTRIES"
UPDATE"21090"   "434957"   "ADVANCED MAILING SVC"
UPDATE"21090"   "428918"   "EINS SERVICES"
UPDATE"21090"   "650297"   "GOLD STAR PEST CONTROL INC"

I don't need to do this alot so I went ahead and just extracted the third column from both the source file and target file and created two tables in an oracle database to do the matching because this method is so SLOW.

I was just trying to create a generic utility for doing this type of matching on flat files that are delimited( i.e. TAB, comma, pipe, etc. ).

Is there a different search/match method I can use to speed it up?

Would it run faster if I did it in a UE macro?

Again Thanks very much for your assistance!!!

[jorrasdk]

No time at the moment to investigate the TAB issue, but just this comment:

Would it run faster if I did it in a UE macro?

At the moment yes, macros will beat scripting hands down with regard to speed.