topic Re: How to read two rows from txt-file as column name in Discussions

How to read two rows from txt-file as column name

mlo1 — Thu, 08 Jun 2023 16:34:03 GMT

Hello,

Is there an option in JSL to read in the first two lines of a text file as a column header or
alternatively (and even better) the first as Column name and the second as the column property (Units)

Thank you

Mirko

@martindemel

Re: How to read two rows from txt-file as column name

txnelson — Wed, 22 Feb 2023 23:44:57 GMT

Craige's example below is a better solution, but this code might also be useful.

Here is a little script that does the conversion you want

Names Default To Here( 1 );

dt = Open(
	"<Path to BSP_jmp.TXT>",
	columns(
		New Column( "YYYY", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "MM/DD", Character, "Nominal" ),
		New Column( "DAY", Character, "Nominal" ),
		New Column( "hh:mm:ss", Numeric, "Continuous", Format( "h:m:s", 11, 0 ), Input Format( "h:m:s", 0 ) )
	)
);

// Add 2nd line text to Column Name
For( i = 1, i <= N Cols( dt ), i++,
	If( Column( dt, i ) << get data type == "Character" & Column( dt, i )[1] != "",
		Column( dt, i ) << set name( Column( dt, i ) << get name || Column( dt, i )[1] )
	)
);

// Delete second name row
dt << delete rows(1);

// Convert columns that are character but should be numeric, to numeric
For( i = 1, i <= N Cols( dt ), i++,
	If( Column( dt, i ) << get data type == "Character" & Is Missing( Num( Column( dt, i )[1] ) ) != 1,
		Column( dt, i ) << set data type( Numeric ) << set modeling type( Continuous )
	)
);

Re: How to read two rows from txt-file as column name

Craige_Hales — Wed, 22 Feb 2023 23:24:04 GMT

Multiple File Import can do it, even for a single file.

Re: How to read two rows from txt-file as column name

mlo1 — Thu, 23 Feb 2023 08:00:29 GMT

Thank you very much for the support.
I prefer this JSL solution.

The multiple file import works fine too but adds an “_” to the header name.

Re: How to read two rows from txt-file as column name

mlo1 — Thu, 23 Feb 2023 08:19:25 GMT

Thank you very much.

Re: How to read two rows from txt-file as column name

Craige_Hales — Thu, 23 Feb 2023 13:27:45 GMT

dtlist = Multiple File Import(
	<<Set Folder( "Z:\" ),
	<<Set Name Filter( "BSP_JMP.txt" ),
	<<Set Name Enable( 1 ),
	<<Set CSV First Header Line( 1 ),
	<<Set CSV Number Of Header Lines( 2 ),
	<<Set CSV First Data Line( 3 )
) << Import Data;

dt = dtlist[1];

namelist = dt << getColumnNames();
// since the _ does not appear in the CSV file, you can use it to
// split the names and then rejoin them.
For Each( {c}, namelist, {txt, parts, newname, p1, p2},
	txt = c << getname;
	parts = Words( txt, "_" );
	p1 = Trim( parts[1] );
	If( N Items( parts ) == 2,
		p2 = Trim( parts[2] );
		If( Contains( {"km", "m/min", "%", "deg/min", "um"}, p2 ), // identify real units
			c << Set Property( "Units", Eval( p2 ) ); // and use units property
			newname = p1;
		, // else not real units
			newname = p1 || "<" || p2 || ">"; // make part of name
		);
	, // only one part? still need the rename to remove the trailing _
		newname = p1
	);
	c << setname( newname );
);

Re: How to read two rows from txt-file as column name

martindemel — Thu, 23 Feb 2023 16:42:09 GMT

You could use the multiple file import to do the import and use then a script to replace the "_" between the header lines in the column names with whatever you like. Just an idea.