Optimised EmEditor Macro to transpose and transform tab delimited data

Question

I currently have a large tab delimited data set that looks like this (n is simply a number, there are more than 4 Headers), an initial Headers column that repeats, followed by at most, 2 columns of data (sometimes only one):

Input file:

Hdr1 A1 B1
Hdr2 A2 B2
Hdr3 A3 B3
Hdrn An Bn
Hdr1 C1
Hdr2 C2
Hdr3 C3
Hdrn Cn
Hdr1 D1 E1
Hdr2 D2 E2
Hdr2 D3 E3
Hdrn Dn En

I need to transpose and transform the data so output looks similar to this (so the repeating headers are removed and the data remains):

Hdr1 Hdr2 Hdr3 Hdrn
A1 A2 A3 An
B1 B2 B3 Bn
C1 C2 C3 Cn
D1 D2 D3 Dn
E1 E2 E3 En

Any ideas for how to do this with an Optimised EmEditor javascript Macro would be much appreciated.

Answer 1

Here is a JavaScript macro for you:

sSeparator = "Hdr1";
sDelimiter = "\t";
sNL = "\r\n";
Redraw = false;
document.CellMode = true;   // Must be cell selection mode
editor.ExecuteCommandByID(4323);  // Clear All Bookmarks in This Document
document.Filter("^" + sSeparator,0,eeFindReplaceCase | eeFindReplaceRegExp,0,0,0);
editor.ExecuteCommandByID(3927);  // Bookmark All
document.Filter("",0,0,0,0,0);    // Reset Filter
if( document.BookmarkCount == 0 ) {
    alert( "Cannot find any lines that begin with \"" + sSeparator + "\"" );
    Quit();
}

document.selection.StartOfDocument(false);
x1 = 1;
y1 = 1;
nLines = 0;
nMaxCol = document.GetColumns();
str = "";

for( ;; ) {
    bStop = false;
    if( document.selection.NextBookmark() ) {  // if next bookmark found
        y2 = document.selection.GetActivePointY(eePosCellLogical);
    }
    else {
        y2 = document.GetLines();    // if bookmark does NOT exist at end of document
        bStop = true;
    }
    if( nLines == 0 ) {
        nLines = y2 - y1;
    }
    else {
        if( nLines != y2 - y1 ) {
            alert( "Number of lines between " + sSeparator + " is not same. Check the format of the input file." );
            Quit();
        }
    }

    for( iCol = x1; iCol <= nMaxCol; ++iCol ) {
        s = document.GetCell( y1, iCol, eeCellIncludeQuotes );
        if( s.length == 0 ) {
            break;
        }
        str += s;
        str += sDelimiter;
        str += document.GetColumn( iCol, sDelimiter, eeCellIncludeQuotes, y1 + 1, y2 - y1 - 1 );
        str += sNL;
    }
    
    y1 = y2;
    
    if( bStop ) {
        break;
    }
    x1 = 2;  // don't need the first column except the first time
}

editor.NewFile();
document.selection.Text = str;  // insert copied text
editor.ExecuteCommandByID(22529);  // set TSV mode

To run this, save this code as, for instance, Transpose.jsee, and then select this file from Select... in the Macros menu. Finally, select Run Transpose.jsee in the Macros menu.