topic Re: How do make a table out of a string like this? in Discussions

How do make a table out of a string like this?

lala — Fri, 09 Jun 2023 17:04:20 GMT

Thanks!

data<=>39.85,-0.015,0.002,1.017,1.70,3.90,-1.80,-3.80,-0.28,-12257.23,15:00,218879.08|39.70,-0.000,-0.000,0.935,3.60,3.50,-2.20,-4.90,-0.65,-357.77,09:30,5039.02|39.89,-0.001,-0.000,0.907,2.80,6.60,-10.90,1.50,-0.18,-882.37,09:32,9386.96|39.85,-0.001,0.000,1.020,0.20,4.20,-7.90,3.50,-0.28,-505.97,09:33,11499.35|

Re: How do make a table out of a string like this?

lala — Fri, 15 Jul 2022 00:04:21 GMT

off1 = Contains( txt, "data<=>" );
If( off1,
txt = Substr( txt, off1 + 1, Length( txt ) )
);
off2 = Contains( txt, "|", -1 );
If( off2,
txt = Substr( txt, 1, off2  )
);
t1=Substitute(txt,":","",","," ","|",",");

Re: How do make a table out of a string like this?

jthi — Fri, 15 Jul 2022 05:56:37 GMT

Use Substitute Into to remove data<=> then use Words twice. First create list by splitting on "|" (rows?) and then on "," to get values for cells. Then change formats as needed and add values to datatable, for example by using data table subscripting with Add Rows.

Names Default To Here(1);

txt = "data<=>39.85,-0.015,0.002,1.017,1.70,3.90,-1.80,-3.80,-0.28,-12257.23,15:00,218879.08|39.70,-0.000,-0.000,0.935,3.60,3.50,-2.20,-4.90,-0.65,-357.77,09:30,5039.02|39.89,-0.001,-0.000,0.907,2.80,6.60,-10.90,1.50,-0.18,-882.37,09:32,9386.96|39.85,-0.001,0.000,1.020,0.20,4.20,-7.90,3.50,-0.28,-505.97,09:33,11499.35|";

Substitute Into(str, "data<=>", ""); // Remove "data<=>""
rows = Words(str, "|");
// example line
first_line = Words(rows[1], ",");

Re: How do make a table out of a string like this?

lwx228 — Fri, 15 Jul 2022 06:50:52 GMT

Re: How do make a table out of a string like this?

Craige_Hales — Fri, 15 Jul 2022 11:19:28 GMT

Another approach. If you don't know the column names, you can leave out the columns(...) section.

txt = "data<=>39.85,-0.015,0.002,1.017,1.70,3.90,-1.80,-3.80,-0.28,-12257.23,15:00,218879.08|39.70,-0.000,-0.000,0.935,3.60,3.50,-2.20,-4.90,-0.65,-357.77,09:30,5039.02|39.89,-0.001,-0.000,0.907,2.80,6.60,-10.90,1.50,-0.18,-882.37,09:32,9386.96|39.85,-0.001,0.000,1.020,0.20,4.20,-7.90,3.50,-0.28,-505.97,09:33,11499.35|";

Open(
    Char To Blob( Munger( txt, 1, "data<=>", "" ) ),
    columns(
        New Column( "a" ),
        New Column( "b" ),
        New Column( "c" ),
        New Column( "d" ),
        New Column( "e" ),
        New Column( "f" ),
        New Column( "g" ),
        New Column( "h" ),
        New Column( "i" ),
        New Column( "j" ),
        New Column( "time", Informat( "h:m" ), Format( "h:m" ) ),
        New Column( "z" )
    ),
    Import Settings( End Of Line( Other( "|" ) ), Labels( 0 ), Data Starts( 1 ) )
);

This is the first time I've used munger() and felt good about it. It looks like its failure behavior (if the data<=> text changes) will be reasonable...leaving the text as is and adding it to the first value.

Opening a blob of CSV-like data using | for line end characters.

The open() function accepts a blob instead of a filename; the import settings() tells it to expect something like a .txt or .csv file in the blob.