topic Re: Import csv file using specified row as column names in Discussions

Import csv file using specified row as column names

Adam_Xu — Wed, 29 Jan 2020 17:24:48 GMT

Hi all,

I wonder to import csv file using specified row as column names (row 3 in this case).

Here is the csv file format:

Here is the target jmp table format I want:

I wrote the below scripts, but failed:

Here is my scripts:

Names Default To Here( 1 );
Files = Pick File(
	"Select CSV File",
	"$Downloads",
	{"JMP Files|jmp;jsl;jrn", "All Files|*"},
	1,
	0,
	"",
	"multiple"
);
For( i = 1, i <= N Items( Files ), i++,
	Try( Open( 
		Files[i],
			Worksheet Settings(
			1,
				Has Column Headers(1),
				Number of Rows in headers(2),
				Headers Start on Row(2),
				Data Starts on Row(8)
			)
		)
	 )
);

Any advice? Thanks

Re: Import csv file using specified row as column names

Adam_Xu — Wed, 29 Jan 2020 18:08:31 GMT

Sorry, this scripts:

Names Default To Here( 1 );
Files = Pick File(
	"Select CSV File",
	"$Downloads",
	{"JMP Files|jmp;jsl;jrn", "All Files|*"},
	1,
	0,
	"",
	"multiple"
);
For( i = 1, i <= N Items( Files ), i++,
	Try(
		Open(
			Files[i],
			Worksheet Settings(
				1,
				Has Column Headers( 1 ),
				Number of Rows in headers( 2 ),
				Headers Start on Row( 3 ),
				Data Starts on Row( 4 )
			)
		)
	)
);

Re: Import csv file using specified row as column names

txnelson — Wed, 29 Jan 2020 17:34:27 GMT

Here is the source used by JMP to open a .csv file. As can be seen, there are options for Column Names Start() and Data Starts().

You should be able to modify this code to meet your needs

Open(
	"C:\Users\Jim\Documents\Big Class.csv",
	columns(
		New Column( "name", Character, "Nominal" ),
		New Column( "age", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "sex", Character, "Nominal" ),
		New Column( "height", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "weight", Numeric, "Continuous", Format( "Best", 12 ) )
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Comma, CSV( 0 ) ),
		Strip Quotes( 1 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 1 ),
		Column Names Start( 1 ),
		Data Starts( 2 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
)

Re: Import csv file using specified row as column names

Adam_Xu — Thu, 30 Jan 2020 06:18:25 GMT

Thank you, Sir! It works