How to convert CSV file into a fixed column width file?

How to convert CSV file into a fixed column width file?

12
Basic UserBasic User
12

    Nov 01, 2018#1

    I am working on a project where I have a text file that has all our order and shipping data into one page. Order numbers are being exported to a system. This system doesn't work with comma separated values. It has a stringent spacing system, i.e. it uses fixed column widths for the order data. There must be a header and trailer record.

    For example an order for a rubber ball would basically look like this:

    Code: Select all

    header
BALL5323
trailer
    The header data in CSV must be separated by the actual number position in the file. Here's an example of the header lines in CSV:

    Original CSV example:

    Code: Select all

    Order, item, customer name, address, order date
1234,  widget, Walmart, 123 street,11/11/2018
1235, chair, IKEA, 124 street, 11/15/2018
    The file to export must follow a format like this:
    • Order number is in columns 1-11 with leading zeros.
    • Item name is in columns 12-22.
    • Customer name is in columns 23-40.
    • Address is in columns 41-65.
    • Order date must be converted to date format YYYYMMDD and is in columns 66-74.
    With the resulting header line would look like this:

    Code: Select all

    00000001234widget     Walmart            123 Street              20181111
00000001235chair      IKEA               124 Street              20181115
    After the header comes the item #, number ordered, price, etc. would look like this:

    Code: Select all

    BALL5323                                  23                           0233
    So the header and item would look something like this:

    Code: Select all

    item number            number ordered                        price ( 2.33/each in example below)

00000001234balls       Walmart            123 Street              20181111
BALL5323                                  23                           0233
    Throw in the trailer record and now I have a headache.

    Code: Select all

    number shipped, comments, purchase#, ship method, invoice total

23                            they bounce                                 752539                                        FedEx                                         0005359      
    Final result for 1 record is this:

    Code: Select all

    00000001234balls       Walmart            123 Street              20181111
BALL5323                                  23                           0233 
23       they bounce      752539                      FedEx                                         0005359      
    I know UEStudio is powerful enough to do this, just looking for some direction on the best place to get it done. 

    Thanks in advance!
    6,685587
    Grand MasterGrand Master
    6,685587

      Nov 01, 2018#2

      The posted data does not 100% match with the explanation. Customer name is in columns 23-41 and address in columns 42-65 as indicated on status bar at bottom of UltraEdit main application window. What is really right? The rest of the order data explanation and example is quite clear. But I have problems to understand header and footer section.

      It would be best to have two files, one with all data before script execution, i.e. the CSV file, and one more with the same data after script execution, i.e. the special fixed width column data file. The files should contain at least three records. This forum allows adding nearly all files, although I would prefer the files compressed into a single ZIP or RAR archive file and just this archive file is added to the post.

      Some additional questions:
      1. Is it possible that some values in CSV file like address contain a comma in double quoted value according to CSV specification?
      2. Is it possible that (double quoted) values in CSV file contain a vertical whitespace, i.e. one or more line breaks?
      3. How large can the CSV file be in worst case?
        It makes a big difference on coding a file reformatting script if a file can have just some KiBs or a few MiBs or has several hundred MiBs or even GiBs.
      Best regards from an UC/UE/UES for Windows user from Austria
      12
      Basic UserBasic User
      12

        Nov 03, 2018#3

        Hi Mofi

        These are small files > 12 KB. I would say 20 transactions per day max. I thought long and hard about your questions and think I now have a better way of handling what I am trying to do. But could still use your help.

        I built the CSV export so that it will now put the header, item order, and trailer information all on one line.

        So at the beginning of each section I have HXXX for the header part, IXXX for the item order part and TXXX for the trailer part.

        Looks like this in .csv:

        HXXX, headerinfo1, headerinfo2, headerinfo3, IXXX, itmInfo1,itmInfo2,itmInfo3, TXXX, Trailerinfo1, Trailerinfo2, Trailerinfo3

        So I think if I can first get the spacing right for each, I can go back with a simple newline replacement search which enters a newline before each IXXX found and then each TXXX found. Once done, I'd have a search which removed the HXXX, IXXX, and TXXX fields.  

        But I do not know how to program the exact spaces for fields. Is there a way for me to do a macro or script which designates headerinfo1 field will always use 15 spaces, and headerinfo2 will use 18 spaces and headerinfo3 uses 2 spaces, etc.?  

        If you still want me to upload an example file, I will. But didn't want to burden you with it if I could help it.  

        Thanks again!
        6,685587
        Grand MasterGrand Master
        6,685587

          Nov 04, 2018#4

          There are multiple possible solutions on how to convert a CSV file to a fixed width file. For example on large files two regular expression replaces could be used to first add to each data value the maximum number of zeros or spaces for appropriate data column and next remove those zeros and spaces which make the data column too long in fixed width data file. This could be done with a macro or a script.

          But in this case with file being so small, it is better to write an UltraEdit script which loads entire file content into memory, next using JavaScript core objects like array and string on each data value to trim it first, then get the length of the value, add the zeros or spaces and finally output into a new file the reformatted data. That would be very easy to code by me, but I would nevertheless need an input and output example to really write the script code right for the data in CSV file and the expected output file and test the script on the example.

          You know that I wrote already a similar script for you, see Converting CSV data to fixed width data? So this script would be just a variant of already written script.
          Best regards from an UC/UE/UES for Windows user from Austria
          12
          Basic UserBasic User
          12

            Nov 07, 2018#5

            Hi Mofi,

            Mofi, I found your script and it worked awesome. Couple that with some Find/replace macros and it fixes the date, the spacing and everything perfectly. 

            But I made a mistake with my original plan. I didn't realize the "headers" and "trailer" records come before and after a batch of items in an order.  

            Former attached were two files: 1 CSV and one space separated TXT. I painstakingly went through these space by space, comma by comma. Hopefully everything will hold up after the transfer.

            The newMofiSample.csv is the format of the data my system produces. The NewSampleExported.txt file is the required format the shipper requires. There are many blank spaces some are used sometimes, some are never used. Please don't worry about those too much. Once I have the right program logic, I can fix spacing.

            Also, FYI I didn't manipulate the CSV file like I was talking about in prior comments. This is exact export from our system and reflected in their system. Please let me know if you have any questions

            Thank you! :)
            6,685587
            Grand MasterGrand Master
            6,685587

              Nov 08, 2018#6

              Example files with data before and after reformatting makes code writing and testing much easier. Here is the script for this task.

              Modify the variables at top according to your needs.

              Code: Select all

              // Character code value of separator. Use 0x2C for comma, 0x3B for
// semicolon, 0x7C for vertical bar, or 0x09 for a horizontal tab.
var nSeparatorCode = 0x2C;

// Array of comma separated list of field widths. For example [5,10,20]
// means first data column should have a width of 5 characters, second
// of 10 characters and third of 20 characters. A negative value means
// right aligned data on output with specified width. The value 0 means
// ignore this field value and don't output it.
var anFieldWidths = [1,10,8,2,30,30,60,0,15,2,117,0,0,0,0,0,0,54,0,0,0, // H line
                     1,-5,-5,20,126,0,0,0,0,0,0, // D line
                     1,-5,-5,55,55,15,40,11,0];  // E line

var nValuesH = 21;   // Number of values for header line
var nValuesD = 11;   // Number of values for data line

// Character as replacement for a carriage return and character as
// replacement for a line-feed in a field value. Each string can be also
// an empty string to remove all carriage returns and line-feeds from
// field values. Also a string with more than one character is possible.
var sCarriageReturn = "";
var sLineFeed = "";

// Write the reformatted lines into a new file or overwrite active file.
var bNewFile = true;


if (UltraEdit.document.length > 0)  // Is any file opened?
{
   // Define environment for this script.
   UltraEdit.insertMode();
   if (typeof(UltraEdit.columnModeOff) == "function") UltraEdit.columnModeOff();
   else if (typeof(UltraEdit.activeDocument.columnModeOff) == "function") UltraEdit.activeDocument.columnModeOff();

   UltraEdit.activeDocument.selectAll();
   if (UltraEdit.activeDocument.isSel())
   {
      // Parse the selected CSV data character by character to support also
      // 1 or more delimiters as well as newline characters within a double
      // quoted value. Additionally just store in memory on which indices in
      // CSV character stream the data values start and end instead of using
      // array of strings to avoid copying the strings in memory as much as
      // possible for more efficient processing of the CSV data.

      var bShowOutput = false;     // Show output window after finishing conversion.
      var bNewDataRow = false;     // For detection of a new data row.
      var bWithinQuotes = false;   // For detection of double quoted values.
      var anValueIndices = [0,0];  // Start and end indices of value in stream.
      var anRowValueIndices = [];  // Array of value indices pairs in a data row.
      var anAllValueIndices = [];  // Array of all data row arrays in entire file.
      var nMultiLineValue = 0;     // Is there any multi-line value in CSV file?

      var nCharIndex = 0;
      do
      {
         var nCharCode = UltraEdit.activeDocument.selection.charCodeAt(nCharIndex);
         if (!bWithinQuotes)
         {
            // Is the current character the separator?
            if (nCharCode == nSeparatorCode)
            {
               anValueIndices[1] = nCharIndex;
               anRowValueIndices.push(anValueIndices);
               anValueIndices = [nCharIndex+1,nCharIndex+1];
            }
            // Is the current character a carriage return or a line-feed?
            else if ((nCharCode == 0x0D) || (nCharCode == 0x0A))
            {
               if (!bNewDataRow)
               {
                  bNewDataRow = true;
                  anValueIndices[1] = nCharIndex;
                  anRowValueIndices.push(anValueIndices);
                  anAllValueIndices.push(anRowValueIndices);
                  anRowValueIndices = [];
               }
               anValueIndices = [nCharIndex+1,nCharIndex+1];
               continue;
            }
            else if (nCharCode == 0x22)
            {
               bWithinQuotes = true;
            }
            bNewDataRow = false;
         }
         else  // Everything enclosed in double quotes must be interpreted
         {     // as part of data value including delimiter and newline
               // character until next double quote character is found.
            if (nCharCode == 0x22)
            {
               bWithinQuotes = false;
            }
            else if (nCharCode == 0x0D)
            {
               nMultiLineValue |= 1;
            }
            else if (nCharCode == 0x0A)
            {
               nMultiLineValue |= 2;
            }
         }
      }
      while (++nCharIndex < UltraEdit.activeDocument.selection.length);

      // Does the file not end with newline characters?
      if (!bNewDataRow)
      {
         anValueIndices[1] = nCharIndex;
         anRowValueIndices.push(anValueIndices);
         anAllValueIndices.push(anRowValueIndices);
      }

//      UltraEdit.outputWindow.showWindow(true);
//      UltraEdit.outputWindow.write("\nDump of array anAllValueIndices:\n");
//      var_dump(anAllValueIndices);
//      for (var nLine = 0; nLine < anAllValueIndices.length; nLine++)
//      {
//         var sPluralS = (anAllValueIndices[nLine].length != 1) ? "s" : " ";
//         var sLineData = "The " + anAllValueIndices[nLine].length.toString(10)+
//                         " value indices pair" + sPluralS + " of data row " +
//                         (nLine+1).toString(10) + ": ";
//         for (var nPair = 0; nPair < anAllValueIndices[nLine].length; nPair++)
//         {
//            if (nPair != 0) sLineData += ", ";
//            sLineData += anAllValueIndices[nLine][nPair][0].toString(10);
//            sLineData += '-';
//            sLineData += anAllValueIndices[nLine][nPair][1].toString(10);
//         }
//         UltraEdit.outputWindow.write(sLineData);
//      }
//      UltraEdit.outputWindow.write("");

      // The number of values in first row is the number of data columns.
      // The number of data rows in output depends on number of identical
      // H and E values in input file.
      var nColCount = anAllValueIndices[0].length;
      var nRowCount = anAllValueIndices.length;

      if (nColCount == anFieldWidths.length)
      {
         var nFieldWidth;
         var sFieldValue;
         var nCol = anFieldWidths.length;

         // Build string with just spaces for each data column for fast
         // extending with spaces later each value to required length.
         var asFieldWidths = new Array(nColCount);
         for (nCol = 0; nCol < nColCount; nCol++)
         {
            var sSpaces = "";
            nFieldWidth = anFieldWidths[nCol];
            if (nFieldWidth < 0)
            {
               nFieldWidth = -nFieldWidth;
            }
            while (nFieldWidth)
            {
               sSpaces += ' ';
               nFieldWidth--;
            }
            asFieldWidths[nCol] = sSpaces;
         }

         // Convert the lines in CSV file to fixed width lines now.
         var sValues = (nColCount != 1) ? " values." : " value.";
         var asRows = [];
         var sLastLineH = "";
         var sLastLineE = "";
         var nFirstValueE = nValuesH + nValuesD;

         for (nRow = 0; nRow < nRowCount; nRow++)
         {
            // Verify the number of values in that row.
            var nValueCount = nColCount;
            if (nValueCount > anAllValueIndices[nRow].length)
            {
               bShowOutput = true;
               nValueCount = anAllValueIndices[nRow].length;
               UltraEdit.outputWindow.write("Warning: Data row " + (nRow+1) +
                                            " has just " + nValueCount +
                                            " instead of " + nColCount + sValues);
            }
            else if (nValueCount < anAllValueIndices[nRow].length)
            {
               bShowOutput = true;
               UltraEdit.outputWindow.write("Warning: Data row " + (nRow+1) +
                                            " has " + anAllValueIndices[nRow].length +
                                            " instead of " + nColCount + sValues);
            }

            var sLineH = "";
            var sLineD = "";
            var sLineE = "";

            for (nCol = 0; nCol < nValueCount; nCol++)
            {
               nFieldWidth = asFieldWidths[nCol].length;
               if (nFieldWidth == 0) continue;

               sFieldValue = UltraEdit.activeDocument.selection.substring(anAllValueIndices[nRow][nCol][0],anAllValueIndices[nRow][nCol][1]);

               sFieldValue = sFieldValue.replace(/^\"(.*)\"$/,"$1");
               sFieldValue = sFieldValue.replace(/\"\"/g,"");

               if (nMultiLineValue) // Is there any multi-line value in file?
               {
                  var sReplacedValue;
                  if (nMultiLineValue & 1)
                  {
                     sReplacedValue = sFieldValue.replace(/\r/g,sCarriageReturn);
                     if (sReplacedValue != sFieldValue)
                     {
                        bShowOutput = true;
                        UltraEdit.outputWindow.write("Replaced in data row " + (nRow+1) + " in value " +
                                                     (nCol+1) + " at least one carriage return by '" +
                                                     sCarriageReturn + "'.");
                        sFieldValue = sReplacedValue;
                     }
                  }
                  if (nMultiLineValue & 2)
                  {
                     sReplacedValue = sFieldValue.replace(/\n/g,sLineFeed);
                     if (sReplacedValue != sFieldValue)
                     {
                        bShowOutput = true;
                        UltraEdit.outputWindow.write("Replaced in data row " + (nRow+1) + " in value " +
                                                     (nCol+1) + " at least one line-feed by '" +
                                                     sLineFeed + "'.");
                        sFieldValue = sReplacedValue;
                     }
                  }
               }

               if (sFieldValue.length > nFieldWidth)
               {
                  bShowOutput = true;
                  UltraEdit.outputWindow.write("Warning: Truncated in data row " + (nRow+1) +
                                               " value " + (nCol+1) + " from " +
                                               sFieldValue.length + " to " +
                                               nFieldWidth + " character" +
                                               ((nFieldWidth != 1) ? "s." : "."));
                  sFieldValue = (nFieldWidth > 0) ? sFieldValue.substr(0,nFieldWidth) : "";
               }

               if (sFieldValue.length < nFieldWidth)
               {
                  if (anFieldWidths[nCol] > 0)
                  {
                     sFieldValue += asFieldWidths[nCol].substr(sFieldValue.length);
                  }
                  else
                  {
                     sFieldValue = asFieldWidths[nCol].substr(sFieldValue.length) + sFieldValue;
                  }
               }

               if (nCol < nValuesH)          // Is this a value of H line?
               {
                  sLineH += sFieldValue;
               }
               else if (nCol < nFirstValueE) // Is this a value of D line?
               {
                  sLineD += sFieldValue;
               }
               else                          // It is a value of E line.
               {
                  sLineE += sFieldValue;
               }
            }

            while (nCol < nColCount)         // Should be never true.
            {
               // Add spaces for each value missing in that row.
               if (nCol < nValuesH)
               {
                  sLineH += asFieldWidths[nCol];
               }
               else if (nCol < nFirstValueE)
               {
                  sLineD += asFieldWidths[nCol];
               }
               else
               {
                  sLineE += asFieldWidths[nCol];
               }
               nCol++;
            }

            // Is H or E line different to previous record?
            if ((sLastLineH != sLineH) || (sLastLineE != sLineE))
            {
               // Append to rows array first the previous E line,
               // except the array is empty because of first record.
               if (asRows.length) asRows.push(sLastLineE);
               // Next append to rows array the current H line.
               asRows.push(sLineH);
               sLastLineH = sLineH;
               sLastLineE = sLineE;
            }
            // Append the D line to the array of reformatted rows.
            asRows.push(sLineD);
         }

         // Finally append the last E line to the rows array.
         asRows.push(sLastLineE);

         // Determine line terminator type from active file.
         var sLineTerm;
         if (UltraEdit.activeDocument.lineTerminator < 1) sLineTerm = "\r\n";
         else if (UltraEdit.activeDocument.lineTerminator == 1) sLineTerm = "\n";
         else sLineTerm = "\r";

         if (bNewFile) // Write the reformatted lines into a new file?
         {
            // Replace the file extension of active file by ".txt".
            // This quick and dirty solution works only for files
            // which really have a file extension.
            var sNewFileName = UltraEdit.activeDocument.path.replace(/\.[^.\\]+$/,".txt");
            // Append .txt if new file name is identical to active file name.
            if (sNewFileName == UltraEdit.activeDocument.path) sNewFileName += ".txt";

            // Cancel the selection in input file.
            UltraEdit.activeDocument.top();

            // Create a new file with same line ending type as input file.
            UltraEdit.newFile();
            UltraEdit.activeDocument.unixMacToDos();
            if (sLineTerm == "\n") UltraEdit.activeDocument.dosToUnix();
            else if (sLineTerm == "\r") UltraEdit.activeDocument.dosToMac();

            UltraEdit.activeDocument.write(asRows.join(sLineTerm));
            UltraEdit.saveAs(sNewFileName);
            UltraEdit.closeFile(UltraEdit.activeDocument.path,2);
         }
         else
         {
            // Overwrite the entire data in input file.
            UltraEdit.activeDocument.write(asRows.join(sLineTerm));
            // Close file with saving it.
            UltraEdit.closeFile(UltraEdit.activeDocument.path,1);
         }
      }
      else  // The number of defined field widths does not match with
      {     // number of field values in first line of input file.
         bShowOutput = true;
         UltraEdit.outputWindow.write("The number of comma separated values in first line is: "+nColCount);
         UltraEdit.outputWindow.write("The number of width numbers in array anFieldWidths is: "+anFieldWidths.length);
         UltraEdit.outputWindow.write("Please adapt the width numbers in array anFieldWidths.");
      }

      if (bShowOutput) UltraEdit.outputWindow.showWindow(true);
   }
}
              Best regards from an UC/UE/UES for Windows user from Austria
              12
              Basic UserBasic User
              12

                Nov 16, 2018#7

                Thank you so much Mofi. This worked great.  

                  Dec 04, 2018#8

                  Hi Mofi,

                  I found out too late, the totaling function on the export system doesn't work.

                  Is there an elegant way to do this in UEStudio?

                  I've attached a screenshot of an example column. So far, what I've come up with is run a regex against the resulting file and total after the fact. Your thoughts?

                  I am attaching a sample data file. It works using your script above, but of course won't add the column.

                  You'll need the following field widths for it to work:

                  Code: Select all

                  var anFieldWidths = [1,10,8,2,30,30,30,30,15,2,5,30,30,30,15,2,5,1,8,10,15,20, // H line
                     1,-5,-5,20,1,20,9,10,60,6,20, // D line
                     1,-5,-5,55,55,15,40,1,10];  // E line

var nValuesH = 22;   // Number of values for header line
var nValuesD = 11;   // Number of values for data line
                  Thank you again!
                  TotalSum.png (133.87KiB)
                  Image showing the data to sum on D lines and where to write the sum in E line.
                  newMofiSample.zip (951 Bytes)   28
                  ZIP file containing the small example CSV file.
                  6,685587
                  Grand MasterGrand Master
                  6,685587

                    Dec 05, 2018#9

                    The calculation of total sum of first integer or float value of all D lines of same H line can be done already on creation of every D line and can be output output at beginning of every E line instead of value 0 read from CSV file. The updated script does that according to example.

                    Code: Select all

                    // Character code value of separator. Use 0x2C for comma, 0x3B for
// semicolon, 0x7C for vertical bar, or 0x09 for a horizontal tab.
var nSeparatorCode = 0x2C;

// Array of comma separated list of field widths. For example [5,10,20]
// means first data column should have a width of 5 characters, second
// of 10 characters and third of 20 characters. A negative value means
// right aligned data on output with specified width. The value 0 means
// ignore this field value and don't output it.
var anFieldWidths = [1,10,8,2,30,30,30,30,15,2,5,30,30,30,15,2,5,1,8,10,15,20, // H line
                     1,-5,-5,20,1,20,9,10,60,6,20, // D line
                     1,-5,-5,55,55,15,40,1,10];  // E line

var nValuesH = 22;   // Number of values for header line
var nValuesD = 11;   // Number of values for data line

// Character as replacement for a carriage return and character as
// replacement for a line-feed in a field value. Each string can be also
// an empty string to remove all carriage returns and line-feeds from
// field values. Also a string with more than one character is possible.
var sCarriageReturn = "";
var sLineFeed = "";

// Write the reformatted lines into a new file or overwrite active file.
var bNewFile = true;


if (UltraEdit.document.length > 0)  // Is any file opened?
{
   // Define environment for this script.
   UltraEdit.insertMode();
   if (typeof(UltraEdit.columnModeOff) == "function") UltraEdit.columnModeOff();
   else if (typeof(UltraEdit.activeDocument.columnModeOff) == "function") UltraEdit.activeDocument.columnModeOff();

   UltraEdit.activeDocument.selectAll();
   if (UltraEdit.activeDocument.isSel())
   {
      // Parse the selected CSV data character by character to support also
      // 1 or more delimiters as well as newline characters within a double
      // quoted value. Additionally just store in memory on which indices in
      // CSV character stream the data values start and end instead of using
      // array of strings to avoid copying the strings in memory as much as
      // possible for more efficient processing of the CSV data.

      var bShowOutput = false;     // Show output window after finishing conversion.
      var bNewDataRow = false;     // For detection of a new data row.
      var bWithinQuotes = false;   // For detection of double quoted values.
      var anValueIndices = [0,0];  // Start and end indices of value in stream.
      var anRowValueIndices = [];  // Array of value indices pairs in a data row.
      var anAllValueIndices = [];  // Array of all data row arrays in entire file.
      var nMultiLineValue = 0;     // Is there any multi-line value in CSV file?

      var nCharIndex = 0;
      do
      {
         var nCharCode = UltraEdit.activeDocument.selection.charCodeAt(nCharIndex);
         if (!bWithinQuotes)
         {
            // Is the current character the separator?
            if (nCharCode == nSeparatorCode)
            {
               anValueIndices[1] = nCharIndex;
               anRowValueIndices.push(anValueIndices);
               anValueIndices = [nCharIndex+1,nCharIndex+1];
            }
            // Is the current character a carriage return or a line-feed?
            else if ((nCharCode == 0x0D) || (nCharCode == 0x0A))
            {
               if (!bNewDataRow)
               {
                  bNewDataRow = true;
                  anValueIndices[1] = nCharIndex;
                  anRowValueIndices.push(anValueIndices);
                  anAllValueIndices.push(anRowValueIndices);
                  anRowValueIndices = [];
               }
               anValueIndices = [nCharIndex+1,nCharIndex+1];
               continue;
            }
            else if (nCharCode == 0x22)
            {
               bWithinQuotes = true;
            }
            bNewDataRow = false;
         }
         else  // Everything enclosed in double quotes must be interpreted
         {     // as part of data value including delimiter and newline
               // character until next double quote character is found.
            if (nCharCode == 0x22)
            {
               bWithinQuotes = false;
            }
            else if (nCharCode == 0x0D)
            {
               nMultiLineValue |= 1;
            }
            else if (nCharCode == 0x0A)
            {
               nMultiLineValue |= 2;
            }
         }
      }
      while (++nCharIndex < UltraEdit.activeDocument.selection.length);

      // Does the file not end with newline characters?
      if (!bNewDataRow)
      {
         anValueIndices[1] = nCharIndex;
         anRowValueIndices.push(anValueIndices);
         anAllValueIndices.push(anRowValueIndices);
      }

//      UltraEdit.outputWindow.showWindow(true);
//      UltraEdit.outputWindow.write("\nDump of array anAllValueIndices:\n");
//      var_dump(anAllValueIndices);
//      for (var nLine = 0; nLine < anAllValueIndices.length; nLine++)
//      {
//         var sPluralS = (anAllValueIndices[nLine].length != 1) ? "s" : " ";
//         var sLineData = "The " + anAllValueIndices[nLine].length.toString(10)+
//                         " value indices pair" + sPluralS + " of data row " +
//                         (nLine+1).toString(10) + ": ";
//         for (var nPair = 0; nPair < anAllValueIndices[nLine].length; nPair++)
//         {
//            if (nPair != 0) sLineData += ", ";
//            sLineData += anAllValueIndices[nLine][nPair][0].toString(10);
//            sLineData += '-';
//            sLineData += anAllValueIndices[nLine][nPair][1].toString(10);
//         }
//         UltraEdit.outputWindow.write(sLineData);
//      }
//      UltraEdit.outputWindow.write("");

      // The number of values in first row is the number of data columns.
      // The number of data rows in output depends on number of identical
      // H and E values in input file.
      var nColCount = anAllValueIndices[0].length;
      var nRowCount = anAllValueIndices.length;

      if (nColCount == anFieldWidths.length)
      {
         var nFieldWidth;
         var sFieldValue;
         var nCol = anFieldWidths.length;

         // Build string with just spaces for each data column for fast
         // extending with spaces later each value to required length.
         var asFieldWidths = new Array(nColCount);
         for (nCol = 0; nCol < nColCount; nCol++)
         {
            var sSpaces = "";
            nFieldWidth = anFieldWidths[nCol];
            if (nFieldWidth < 0)
            {
               nFieldWidth = -nFieldWidth;
            }
            while (nFieldWidth)
            {
               sSpaces += ' ';
               nFieldWidth--;
            }
            asFieldWidths[nCol] = sSpaces;
         }

         // Convert the lines in CSV file to fixed width lines now.
         var sValues = (nColCount != 1) ? " values." : " value.";
         var asRows = [];
         var sLastLineH = "";
         var sLastLineE = "";
         var nFirstValueE = nValuesH + nValuesD;

         // Determine once field width of total sum in E line and its alignment.
         var bTotalRightAlign = false;
         var nTotalSum = 0;
         var fTotalSum = 0.0;
         var bTotalFloat = false;
         var nTotalLength = -5;
         var sTotalSpaces = "     ";
         for (nCol = nFirstValueE+1; nCol < anFieldWidths.length; nCol++)
         {
            if (anFieldWidths[nCol])
            {
               sTotalSpaces = asFieldWidths[nCol];
               nTotalLength = anFieldWidths[nCol];
               if (nTotalLength < 0)
               {
                  nTotalLength = -nTotalLength;
                  bTotalRightAlign = true;
               }
               break;
            }
         }

         for (nRow = 0; nRow < nRowCount; nRow++)
         {
            // Verify the number of values in that row.
            var nValueCount = nColCount;
            if (nValueCount > anAllValueIndices[nRow].length)
            {
               bShowOutput = true;
               nValueCount = anAllValueIndices[nRow].length;
               UltraEdit.outputWindow.write("Warning: Data row " + (nRow+1) +
                                            " has just " + nValueCount +
                                            " instead of " + nColCount + sValues);
            }
            else if (nValueCount < anAllValueIndices[nRow].length)
            {
               bShowOutput = true;
               UltraEdit.outputWindow.write("Warning: Data row " + (nRow+1) +
                                            " has " + anAllValueIndices[nRow].length +
                                            " instead of " + nColCount + sValues);
            }

            var sLineH = "";
            var sLineD = "";
            var sLineE = "";

            for (nCol = 0; nCol < nValueCount; nCol++)
            {
               nFieldWidth = asFieldWidths[nCol].length;
               if (!nFieldWidth) continue;

               sFieldValue = UltraEdit.activeDocument.selection.substring(anAllValueIndices[nRow][nCol][0],anAllValueIndices[nRow][nCol][1]);

               sFieldValue = sFieldValue.replace(/^\"(.*)\"$/,"$1");
               sFieldValue = sFieldValue.replace(/\"\"/g,"");

               if (nMultiLineValue) // Is there any multi-line value in file?
               {
                  var sReplacedValue;
                  if (nMultiLineValue & 1)
                  {
                     sReplacedValue = sFieldValue.replace(/\r/g,sCarriageReturn);
                     if (sReplacedValue != sFieldValue)
                     {
                        bShowOutput = true;
                        UltraEdit.outputWindow.write("Replaced in data row " + (nRow+1) + " in value " +
                                                     (nCol+1) + " at least one carriage return by '" +
                                                     sCarriageReturn + "'.");
                        sFieldValue = sReplacedValue;
                     }
                  }
                  if (nMultiLineValue & 2)
                  {
                     sReplacedValue = sFieldValue.replace(/\n/g,sLineFeed);
                     if (sReplacedValue != sFieldValue)
                     {
                        bShowOutput = true;
                        UltraEdit.outputWindow.write("Replaced in data row " + (nRow+1) + " in value " +
                                                     (nCol+1) + " at least one line-feed by '" +
                                                     sLineFeed + "'.");
                        sFieldValue = sReplacedValue;
                     }
                  }
               }

               if (sFieldValue.length > nFieldWidth)
               {
                  bShowOutput = true;
                  UltraEdit.outputWindow.write("Warning: Truncated in data row " + (nRow+1) +
                                               " value " + (nCol+1) + " from " +
                                               sFieldValue.length + " to " +
                                               nFieldWidth + " character" +
                                               ((nFieldWidth != 1) ? "s." : "."));
                  sFieldValue = (nFieldWidth > 0) ? sFieldValue.substr(0,nFieldWidth) : "";
               }

               if (sFieldValue.length < nFieldWidth)
               {
                  if (anFieldWidths[nCol] > 0)
                  {
                     sFieldValue += asFieldWidths[nCol].substr(sFieldValue.length);
                  }
                  else
                  {
                     sFieldValue = asFieldWidths[nCol].substr(sFieldValue.length) + sFieldValue;
                  }
               }

               if (nCol < nValuesH)          // Is this a value of H line?
               {
                  sLineH += sFieldValue;
               }
               else if (nCol < nFirstValueE) // Is this a value of D line?
               {
                  sLineD += sFieldValue;
               }
               else                          // It is a value of E line.
               {
                  sLineE += sFieldValue;
               }
            }

            while (nCol < nColCount)         // Should be never true.
            {
               // Add spaces for each value missing in that row.
               if (nCol < nValuesH)
               {
                  sLineH += asFieldWidths[nCol];
               }
               else if (nCol < nFirstValueE)
               {
                  sLineD += asFieldWidths[nCol];
               }
               else
               {
                  sLineE += asFieldWidths[nCol];
               }
               nCol++;
            }

            // Is H line different to previous record?
            if (sLastLineH != sLineH)
            {
               // Append to rows array first the previous E line,
               // except the array is empty because of first record.
               if (asRows.length)
               {
                  // Convert current total sum value from integer or float to string.
                  if (!bTotalFloat)
                  {
                     sFieldValue = nTotalSum.toString(10);
                  }
                  else
                  {
                     sFieldValue = fTotalSum.toString(10);
                  }
                  // Append or prepend spaces on too short string length.
                  if (sFieldValue.length < nTotalLength)
                  {
                     if (!bTotalRightAlign)
                     {
                        sFieldValue += sTotalSpaces.substr(sFieldValue.length);
                     }
                     else
                     {
                        sFieldValue = sTotalSpaces.substr(sFieldValue.length) + sFieldValue;
                     }
                  }
                  sLastLineE = 'E' + sFieldValue + sLastLineE.substr(nTotalLength+1);
                  asRows.push(sLastLineE);
               }
               // Next append to rows array the current H line.
               asRows.push(sLineH);
               sLastLineH = sLineH;
               sLastLineE = sLineE;
               nTotalSum = 0;
               bTotalFloat = false;
            }
            // Append the D line to the array of reformatted rows.
            asRows.push(sLineD);
            // Convert first integer or float value in this data line
            // to an integer or float and add it to current total sum.
            var sValue = sLineD.replace(/^D *([\d\-+.]+).*$/,"$1");
            if (!bTotalFloat && (sValue.indexOf('.') < 0))
            {
               nTotalSum += parseInt(sValue,10);
            }
            else
            {
               if (!bTotalFloat)
               {
                  bTotalFloat = true;
                  fTotalSum = nTotalSum
               }
               fTotalSum += parseFloat(sValue);
            }
         }

         // Finally append the last E line to the rows array
         // also with the correct total sum at beginning.
         if (!bTotalFloat)
         {
            sFieldValue = nTotalSum.toString(10);
         }
         else
         {
            sFieldValue = fTotalSum.toString(10);
         }
         if (sFieldValue.length < nTotalLength)
         {
            if (!bTotalRightAlign)
            {
               sFieldValue += sTotalSpaces.substr(sFieldValue.length);
            }
            else
            {
               sFieldValue = sTotalSpaces.substr(sFieldValue.length) + sFieldValue;
            }
         }
         sLastLineE = 'E' + sFieldValue + sLastLineE.substr(nTotalLength+1);
         asRows.push(sLastLineE);

         // Determine line terminator type from active file.
         var sLineTerm;
         if (UltraEdit.activeDocument.lineTerminator < 1) sLineTerm = "\r\n";
         else if (UltraEdit.activeDocument.lineTerminator == 1) sLineTerm = "\n";
         else sLineTerm = "\r";

         if (bNewFile) // Write the reformatted lines into a new file?
         {
            // Replace the file extension of active file by ".txt".
            // This quick and dirty solution works only for files
            // which really have a file extension.
            var sNewFileName = UltraEdit.activeDocument.path.replace(/\.[^.\\]+$/,".txt");
            // Append .txt if new file name is identical to active file name.
            if (sNewFileName == UltraEdit.activeDocument.path) sNewFileName += ".txt";

            // Cancel the selection in input file.
            UltraEdit.activeDocument.top();

            // Create a new file with same line ending type as input file.
            UltraEdit.newFile();
            UltraEdit.activeDocument.unixMacToDos();
            if (sLineTerm == "\n") UltraEdit.activeDocument.dosToUnix();
            else if (sLineTerm == "\r") UltraEdit.activeDocument.dosToMac();

            UltraEdit.activeDocument.write(asRows.join(sLineTerm));
            UltraEdit.saveAs(sNewFileName);
            UltraEdit.closeFile(UltraEdit.activeDocument.path,2);
         }
         else
         {
            // Overwrite the entire data in input file.
            UltraEdit.activeDocument.write(asRows.join(sLineTerm));
            // Close file with saving it.
            UltraEdit.closeFile(UltraEdit.activeDocument.path,1);
         }
      }
      else  // The number of defined field widths does not match with
      {     // number of field values in first line of input file.
         bShowOutput = true;
         UltraEdit.outputWindow.write("The number of comma separated values in first line is: "+nColCount);
         UltraEdit.outputWindow.write("The number of width numbers in array anFieldWidths is: "+anFieldWidths.length);
         UltraEdit.outputWindow.write("Please adapt the width numbers in array anFieldWidths.");
      }

      if (bShowOutput) UltraEdit.outputWindow.showWindow(true);
   }
}

                      Dec 07, 2018#10

                      Script above updated to do this additional task working also for data values of type floating point instead of integer as needed for data lines like the one starting with D 0.16. I forgot initially that each data line starts with D and each end line starts with E.
                      Best regards from an UC/UE/UES for Windows user from Austria