Hi SunnyR,
I know this is an old post, but I found the problem interesting and wanted to share an idea I sometime use in situations similar to yours. It's very powerful (and simple) IF the data is well-structured, and not too complex--yours qualifies.
NOTE: this particular solution assumes your logs currently lie in a single column of data, which is the only column of data in the table. As you mention, different rows may contain different numbers of inverter logs.
I noticed that the word "hours" occurs only at the end of each inverter's log, so we can break log records this way and stack the data. Once that is done, we'll break up the text to keep the numbers. It is important that all the logs have the same text and measurements, always in the same order--so these will be aligned in columns when we break the text.
We'll convert all columns to numeric, which makes non-numeric data obvious, that is, missing. We will delete non-numeric data, rename the columns, and we'll be done.
I've attached a table with a table script so you can see how this works.
Names Default To Here( 1 );
dt = :log << get data table; // for use within a table script. otherwise, use dt = current data table();
// can break up the logs at "hours". Replace with ~ or some other unused character to delimit.
For( i = 1, i <= N Row( dt ), i++,
dt:log[i] = Substitute( dt:log[i], "hours", "~" )
);
//split the logs using this delimiter
dt << Text to Columns( delimiters( "~" ), columns( :log ) );
// some logs had more inverters than others, so the stack my have empty data in some rows. Delete them.
dt2 = dt << stack( columns( (dt << get column names)[2 :: N Col( dt )] ) );
dt2 << delete rows(dt2 << get rows where (:data == ""));
//using parens and spaces as delimiters, break up each log
dt2 << text to columns( delimiters( " ()" ), columns( :data ) );
//deal with date and time. They're always in the same columns, making this easier
dt2 << New Column( "date", formula( Informat( :data 14 ) ) );
dt2 << New Column( "time", formula( Informat( :data 15 || " " || :data 16 ) ) );
dt2:date << Delete Formula;
dt2:time << Delete Formula;
dt2 << delete columns( {"data 14", "data 15"} ); //else these persist later, as they are numeric
//Remove non-numeric columns by changing all columns to numeric and removing those containing only missing data.
//Proceed "backward" as deletions change indices.
For( i = N Col( dt2 ), i >= 1, i--,
Column( dt2, i ) << set data type( numeric );
If( N Row( Loc( dt2:i << get values ) ) == 0, dt2 << delete columns( i ) );
);
//rename columns and reformat date and time.
colNames = Words( "Inverter,Below Limit(kw/inverter),% of max,Max,Sun Az,Elevation,Duration", "," );
For( i = 1, i <= N Items( colNames ), i++, Column( dt2, i ) << set name( colNames[i] ) );
dt2:date << Format( "m/d/y" );
dt2:time << Format( "h:m:s" );
