How do I get the import file settings right before transposing and joining two files by script?

ivomdb

I have two sources of data that I need to put together and create a master tabel for analysis and reporting.

One is in .csv (Resulst 1) and another is in .xlsx (Results 2) format. The file in .xlsx format I need to specify individual worksheet settings and which sheet to open when importing. I have seen the following posts but have not managed to get it to work:

https://community.jmp.com/t5/JMP-Scripts/Combine-multiple-txt-or-csv-files/ta-p/24083

https://community.jmp.com/t5/Discussions/How-do-I-get-the-import-file-settings-right-before-concatenating/m-p/39286#M22977

 

On Results 1 I need to specify columns 1 to 3. On Results 2, I need to specify columns 1 to 16 and rows 6 to 7 on the Protocol sheet. Also, after importing Results 2 I will then need to transpose from column 3 until column n (n means not empty), transposing it by column 1.

 

I will then need to join the resulting tables together based on matching columns.

 

Could anyone provide a script for this or give some examples so I can work it out myself?

 

Thanks

 

 

 

 

 

 

 

Results 2.xlsx

Results 1.csv

Phil_Kay

I think that the best way to get started with JSL is by using script that JMP generates when you use the point-and-click interface.

 

Let's start with the import of Results 1. All you should need to do is open it (File > Open) and the resulting JMP table will have a "source" script. Right-click on this and Edit to get the script that will carry out this import. (Don't worry about the extra columns that you don't need - you can specify the ones you need when you join tables later.) Your script should look like this but with the full file path instead of "C:\...".

 

Open(
	"C:\...",
	columns(
		New Column( "Sample", Character, "Nominal" ),
		New Column( "Hour", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "Average", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "Minimum", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "Maximum", Numeric, "Continuous", Format( "Best", 12 ) )
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Comma, CSV( 1 ) ),
		Strip Quotes( 0 ),
		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" )
	)
)

For the Excel file you can use the Excel Import Wizard. You can specify the worksheet and the row numbers where the data starts and ends. Again, you can then get the source script from the resulting JMP table.

Open(
	"C:\...",
	Worksheets( "Protocol" ),
	Use for all sheets( 0 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 2 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 9 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 10 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
)

If you put these scripts together you will have a script that imports both tables into JMP, which is a good start. One thing that you need to know is that you need to add a semi-colon, ";", at the end of each action when you have a sequence of actions in one script.

 

Another thing that you will need to know is that you want to create references for your data tables in your script. You might give each table a reference like dt1, dt2 ...

In this next bit of script I have modified the Results1 import to give the resulting table a reference, dt1, and I have added a semi-colon at the end so that it will work when I then add the script for the next action later.

 

dt1 = Open(
	"C:\...",
	columns(
		New Column( "Sample", Character, "Nominal" ),
		New Column( "Hour", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "Average", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "Minimum", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "Maximum", Numeric, "Continuous", Format( "Best", 12 ) )
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Comma, CSV( 1 ) ),
		Strip Quotes( 0 ),
		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" )
	)
);

 

Obviously there is more to do here to get your solution but hopefully that helps as a starting point.

 

Phil