Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Adam_Xu
Level III

Import csv file using specified row as column names

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:

Screen Shot 2020-01-30 at 1.14.08 AM.png

Here is the target jmp table format I want:

Screen Shot 2020-01-30 at 1.16.15 AM.png

I wrote the below scripts, but failed:

Screen Shot 2020-01-30 at 1.18.11 AM.pngHere 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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Import csv file using specified row as column names

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" )
	)
)
Jim

View solution in original post

3 REPLIES 3
Highlighted
Adam_Xu
Level III

Re: Import csv file using specified row as column names

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 )
			)
		)
	)
);
Highlighted
txnelson
Super User

Re: Import csv file using specified row as column names

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" )
	)
)
Jim

View solution in original post

Highlighted
Adam_Xu
Level III

Re: Import csv file using specified row as column names

Thank you, Sir! It works
Article Labels

    There are no labels assigned to this post.