Multiple find and replace from a data file

[ftpaccess]

I have a sql file which has data like this

(1, orange),
(2, blue),
(3, green),
(4, yellow),
etc..

and second file which has data like this

Green
Blue
Yellow
Yellow
green
Blue
Blue
Orange
Green
etc..

I am looking for a macro which could replace the data in my second file to the numbers in the sql file like this

3
2
4
4
3
2
2
1
3
etc

Is there any easy way to do this?

[Mofi]

Because you want to execute multiple replaces, you need a macro or script for this task. Here is a script tested on your sample lines. Please read the comments of the script before you execute the script the first time.

Code: Select all

if (UltraEdit.document.length > 1) // At least 2 files must be open.
{
   // Define the environment for the script.
   UltraEdit.insertMode();
   UltraEdit.columnModeOff();
   UltraEdit.activeDocument.hexOff();

   // The active file is the file to modify with the numbers
   // stored for every color line by line in the other opened file.
   var nListFile = (UltraEdit.activeDocument.path == UltraEdit.document[0].path) ? 1 : 0;
   // Select everything in list file with DOS terminated lines.
   UltraEdit.document[nListFile].selectAll();
   if (UltraEdit.document[nListFile].isSel())
   {
      // Get all lines in the file into an array of strings with the line termination.
      var asStrings = UltraEdit.document[nListFile].selection.split("\r\n");
      // Discard the selection in the list file by moving caret to top of file.
      UltraEdit.document[nListFile].top();

      // Now the color names can be replaced by the numbers in active document.
      UltraEdit.ueReOn();
      UltraEdit.activeDocument.findReplace.mode=0;
      UltraEdit.activeDocument.findReplace.matchCase=false;
      UltraEdit.activeDocument.findReplace.matchWord=true;
      UltraEdit.activeDocument.findReplace.regExp=false;
      UltraEdit.activeDocument.findReplace.searchDown=true;
      UltraEdit.activeDocument.findReplace.preserveCase=false;
      UltraEdit.activeDocument.findReplace.replaceAll=true;
      UltraEdit.activeDocument.findReplace.replaceInAllOpen=false;
      if (typeof(UltraEdit.activeDocument.findReplace.searchInColumn) == "boolean")
      {
         UltraEdit.activeDocument.findReplace.searchInColumn=false;
      }
      // Move caret in active file to top of the file.
      UltraEdit.activeDocument.top();

      // The following loop is run for every string in the array.
      for (var nStringIndex = 0; nStringIndex < asStrings.length; nStringIndex++ )
      {
         // Ignore empty strings caused by empty lines in the list file.
         if (asStrings[nStringIndex].length == 0) continue;
         // Get number for color.
         var sNumber = asStrings[nStringIndex].replace(/^\([ \t]*(\d+).+$/,"$1");
         // Get color name.
         var sColor = asStrings[nStringIndex].replace(/^.+,[ \t]*(\w+).+$/,"$1");
         // Replace all occurrences of current color by the number.
         UltraEdit.activeDocument.findReplace.replace(sColor, sNumber);
      }
   }
}

[ftpaccess]

Wow..it worked like a charm. Thanks