Names Default To Here(1);
dt = Open(
"$DOWNLOADS/Book1.xlsx",
Worksheets("Sheet1"),
Use for all sheets(0),
Concatenate Worksheets(0),
Create Concatenation Column(0),
Worksheet Settings(
1,
Has Column Headers(0),
Number of Rows in Headers(1),
Headers Start on Row(1),
Data Starts on Row(1),
Data Starts on Column(1),
Data Ends on Row(0),
Data Ends on Column(0),
Replicated Spanned Rows(0),
Replicated Spanned Headers(0),
Suppress Hidden Rows(0),
Suppress Hidden Columns(0),
Suppress Empty Columns(0),
Treat as Hierarchy(0),
Multiple Series Stack(0),
Import Cell Colors(0),
Limit Column Detect(1),
Column Separator String("-"),
Column Numeric Format(Index(0), Column Name(Column 1), Datatype("Character"))
)
);
// turn everything after this into functions/expressions which you can easily run on different sheets
// Take only useful data: header which belongs to parameters (first two rows)
// Parameter rows
header_rows = [1, 2];
param_rows = dt << Get Rows Where(Starts With(:Column 1, "Parameter"));
dt_headers = dt << subset(Rows(header_rows), Selected Columns(0), Output table("Data Header"));
dt_params = dt << subset(Rows(param_rows), Selected Columns(0), Output table("Data Parametere"));
Close(dt, no save);
// Drop empty columns
For Each({colname}, dt_params << Get Column Names("String"),
If(Col Number(Column(dt_params, colname)) == 0,
dt_params << Delete Column(colname)
);
);
dt_result = dt_params << Stack(
columns(dt_params << Get Column Names("String")),
Stack By Row(0),
Drop All Other Columns(1),
Number of Series(2),
Output Table("Result")
);
// Close(dt_params, no save);
// Cleanup some columns
dt_result << Delete Columns(1, 3);
Column(dt_result, 1) << Set Name("Name");
Column(dt_result, 2) << Set Name("Value");
// Add grouping for header joining
new_col = dt_result << New Column("Group", Numeric, Ordinal, Formula(
Col Cumulative Sum(1, :Name)
));
dt_result << run formulas;
new_col << Delete Formula;
headers = dt_headers[1, 0];
header_count = N Items(Associative Array(headers));
dt_headerstack = Eval(EvalExpr(dt_headers << Stack(
columns(dt_headers << Get Column Names("String")),
Stack By Row(0),
Number of Series(Expr(header_count)),
Output Table("header stack")
)));
// Close(dt_headers, no save);
// drop unnecessary columns
dt_headerstack << Delete Columns(1::N Cols(dt_headerstack)::2);
// Move header columns and remove extra header rows
dt_headerstack << Move Up;
rows_to_delete = dt_headerstack << Get Rows Where(Starts With(Column(dt_headerstack, 1)[], "Header"));
dt_headerstack << Delete Rows(rows_to_delete);
// Add grouping for update
new_col = dt_headerstack << New Column("Group", Numeric, Ordinal, Formula(
Row()
));
dt_headerstack << run formulas;
new_col << Delete Formula;
dt_result << Update(
With(dt_headerstack),
Match Columns(:Group = :Group)
);
Close(dt_headerstack, no save);
Close(dt_headers, no save); // close earlier
Close(dt_params, no save); // close earlier
Close(dt, no save); // close earlier
/*
sheetList = Get Excel Worksheets("$DOWNLOADS/Book1.xlsx"); // for sheet list to loop over
*/
