Modify all lines in file A containing a number listed in another file B

jbrullemans · Dec 10, 2014#12014-12-10T15:31+00:00

Hi,

I need to modify a file which contains about 70k lines. I need to find the line that contains a string. And on that same line I need to replace another existing string. The value that has to be found are 1200+ items.

Example:

I have an input file which has the items 8814942, 8814943 in it. UltraEdit has to search for those value and has to replace

Code: Select all

"EURO      "

by

Code: Select all

"EURO-2    "

Only the items in the input list should have this modification.

Does anyone have some idea how to that with UltraEdit. I cannot do this with Excel because I will lose my original format (white space, etc.)

Start:

Code: Select all

 1, 1,"8814942       ","XXXXXXXXXXXXX","EURO      ","12/02/13", 1200, 800,155,35, 900,  800.00,   0,   0
 2, 1,    1,  0.0, 0,   207.0,   123.0,   150.0,  1,   207.0,  1,   123.0,  1,   150.0,0,0,0,   54.3600006,     0.000000,  1,0
 3, 1,0,  36,  216.0,  3,    4,    2,    3,    2,    4,    3,    3,    0,    3,    3,    1,    3,   17,2, 0, 0, 1
 4, 1,   0,   6,"D352/01","                              ","                              ","                              "
 5, 1,"                              ","                              ","                              ","              "
 6, 1,"              ","              ",     1.5100,"                              ","                              "
 7, 1,"                              ","                              ","                              "
 8, 1,"                              ",   0.0,   0.0,   0.0,   150.0,  0,  0,  0,  0,  1,  0,    0.00
 1, 1,"8814943       ","XXXXXXXXXXXXX","EURO      ","12/02/13", 1200, 800,155,35, 900,  800.00,   0,   0
 2, 1,    1,  0.0, 0,   207.0,   123.0,   150.0,  1,   207.0,  1,   123.0,  1,   150.0,0,0,0,   54.3600006,     0.000000,  1,0
 3, 1,0,  36,  216.0,  3,    4,    2,    3,    2,    4,    3,    3,    0,    3,    3,    1,    3,   17,2, 0, 0, 1
 4, 1,   0,   6,"D352/01","                              ","                              ","                              "
 5, 1,"                              ","                              ","                              ","              "
 6, 1,"              ","              ",     1.5100,"                              ","                              "
 7, 1,"                              ","                              ","                              "
 8, 1,"                              ",   0.0,   0.0,   0.0,   150.0,  0,  0,  0,  0,  1,  0,    0.00

After replace:

Code: Select all

 1, 1,"8814942       ","XXXXXXXXXXXXX","EURO-2    ","12/02/13", 1200, 800,155,35, 900,  800.00,   0,   0
 2, 1,    1,  0.0, 0,   207.0,   123.0,   150.0,  1,   207.0,  1,   123.0,  1,   150.0,0,0,0,   54.3600006,     0.000000,  1,0
 3, 1,0,  36,  216.0,  3,    4,    2,    3,    2,    4,    3,    3,    0,    3,    3,    1,    3,   17,2, 0, 0, 1
 4, 1,   0,   6,"D352/01","                              ","                              ","                              "
 5, 1,"                              ","                              ","                              ","              "
 6, 1,"              ","              ",     1.5100,"                              ","                              "
 7, 1,"                              ","                              ","                              "
 8, 1,"                              ",   0.0,   0.0,   0.0,   150.0,  0,  0,  0,  0,  1,  0,    0.00
 1, 1,"8814943       ","XXXXXXXXXXXXX","EURO-2    ","12/02/13", 1200, 800,155,35, 900,  800.00,   0,   0
 2, 1,    1,  0.0, 0,   207.0,   123.0,   150.0,  1,   207.0,  1,   123.0,  1,   150.0,0,0,0,   54.3600006,     0.000000,  1,0
 3, 1,0,  36,  216.0,  3,    4,    2,    3,    2,    4,    3,    3,    0,    3,    3,    1,    3,   17,2, 0, 0, 1
 4, 1,   0,   6,"D352/01","                              ","                              ","                              "
 5, 1,"                              ","                              ","                              ","              "
 6, 1,"              ","              ",     1.5100,"                              ","                              "
 7, 1,"                              ","                              ","                              "
 8, 1,"                              ",   0.0,   0.0,   0.0,   150.0,  0,  0,  0,  0,  1,  0,    0.00

Mofi · Dec 11, 2014#22014-12-11T08:20+00:00

Open your CSV file as first file. Open as second file the list file with the numbers. There must be one number per line for the script below.

Create as third file a new file and copy & paste the code below into this new ASCII file. Save it for example with name UpdateEuroFromList.js.

Then use Scripting - Run Active Script to update the CSV file using the numbers in list file.

Code: Select all

// Document 0 (most left one) must be the CSV file.

// Document 1 (second file) must be the file with
// the list of numbers with 1 number per line

if (UltraEdit.document.length >= 2)
{
   UltraEdit.insertMode();
   UltraEdit.columnModeOff();

   // Determine line terminator used in list file.
   var sLineTerm = "\r\n";
   if (UltraEdit.document[1].lineTerminator == 1) sLineTerm = "\n";
   else if (UltraEdit.document[1].lineTerminator == 2) sLineTerm = "\r";

   // Get all numbers in list file loaded into an array of strings.
   UltraEdit.document[1].selectAll();
   if (UltraEdit.document[1].isSel())
   {
      var asNumbers = UltraEdit.document[1].selection.split(sLineTerm);
      UltraEdit.document[1].top();

      // Has the last line in list file also a line termination, last
      // string in array is an empty string and therefore useless.
      if (asNumbers[asNumbers.length-1] == "") asNumbers.pop();

      // Build Perl OR expressions from the list of
      // numbers with 50 numbers per OR expression.
      var asOrExpressions = [];
      var sExpression = "(?:";
      var nNumberCount = 0;
      for (var nNumber = 0; nNumber < asNumbers.length; nNumber++)
      {
         // Skip all strings which are not decimal numbers.
         if (asNumbers[nNumber].search(/^\d+$/) < 0) continue;
         if(nNumberCount) sExpression += "|";
         sExpression += asNumbers[nNumber];
         nNumberCount++;
         if(nNumberCount == 50)
         {
            sExpression += ")";
            asOrExpressions.push(sExpression);
            sExpression = "(?:";
            nNumberCount = 0;
         }
      }
      if(nNumberCount)
      {
         sExpression += ")";
         asOrExpressions.push(sExpression);
      }

      // Specify the search engine to be used and the options for
      // the Perl regular expression Replace all in entire CSV file.
      UltraEdit.perlReOn();
      UltraEdit.document[0].findReplace.mode=0;
      UltraEdit.document[0].findReplace.matchCase=true;
      UltraEdit.document[0].findReplace.matchWord=false;
      UltraEdit.document[0].findReplace.regExp=true;
      UltraEdit.document[0].findReplace.searchDown=true;
      UltraEdit.document[0].findReplace.searchInColumn=false;
      UltraEdit.document[0].findReplace.preserveCase=false;
      UltraEdit.document[0].findReplace.replaceAll=true;
      UltraEdit.document[0].findReplace.replaceInAllOpen=false;

      // Run the replaces for all OR expressions in list.
      for(var nExp = 0; nExp < asOrExpressions.length; nExp++)
      {
         // Each backslash in Perl regular expression search and
         // replace string must be escaped with an additional backslash.
         var sSearchExp = '^((?:[\\t ]*\\d+,){1,2}"';
         sSearchExp += asOrExpressions[nExp];
         sSearchExp += ' .*?",".+?","EURO)  ';

         UltraEdit.document[0].top();
         UltraEdit.document[0].findReplace.replace(sSearchExp, "\\1-2");
      }
   }
}

jbrullemans · Dec 11, 2014#32014-12-11T09:29+00:00

Hi Mofi,

Thanks very much your script works

. But when I started the program I noticed that it has another file containing also the structure

Code: Select all

   1,"8160209       ","XXXXXXXXXXXXXXXXXXXXXXX  ","CHEP      ","06/11/14",81225, 2
   2,"8160874       ","XXXXXXXXXXXXXXXXXXXXXXX  ","EURO-2    ","27/10/14",82009, 2
   3,"8161821       ","XXXXXXXXXXXXXXXXXXXXXXX  ","EURO      ","07/05/14",36409, 2
   4,"8212198       ","XXXXXXXXXXXXXXXXXXXXXXX  ","WW KLEIN  ","04/12/14",38617, 2
   5,"8212473       ","XXXXXXXXXXXXXXXXXXXXXXX  ","WW KLEIN  ","04/12/14",46041, 2
   6,"8212554       ","XXXXXXXXXXXXXXXXXXXXXXX  ","WW KLEIN  ","04/12/14",31721, 2
   7,"8212811       ","XXXXXXXXXXXXXXXXXXXXXXX  ","WW KLEIN  ","04/12/14",35097, 2
   8,"8213079       ","XXXXXXXXXXXXXXXXXXXXXXX  ","WW KLEIN  ","08/12/14",31385, 2
   9,"8213320 A     ","XXXXXXXXXXXXXXXXXXXXXXX  ","EURO      ","10/12/14",72217, 2
  10,"8213320 B     ","XXXXXXXXXXXXXXXXXXXXXXX  ","EURO-2    ","10/12/14",37193, 2
  11,"8262307       ","XXXXXXXXXXXXXXXXXXXXXXX  ","EURO      ","08/09/14",54841, 2

Also in this file I need to change Euro to Euro-2 for some of the items so line 11 with item number 8232307 should be saved as

Code: Select all

  11,"8262307       ","XXXXXXXXXXXXXXXXXXXXXXX  ","EURO-2    ","08/09/14",54841, 2

I tried to run your same script, but that doesn't seem to work.

Can you help me again?

Mofi · Dec 11, 2014#42014-12-11T10:11+00:00

I updated the Perl regular expression search string as defined in line 71 and 73 of the script to work for both CSV files including line 9 and 10 from second example with A and B after the number and a space. See above the updated script.