Choose Language Hide Translation Bar
Highlighted
mek249
Level II

Import csv files sorted by columns, not rows?

I've been using python to generate a txt file of some image analysis at multiple conditions. It looks something like:

 

A, a1, a2, a3, a4; B, b1, b2, b3, b4, b5; C, c1, c2, c3, c4

 

I would like to import it into a JMP table like:

 

table example.PNG

 

Tweaking the csv import settings works fine when I only have one column, but right now the best I can do is have the 'header' data show up in "column 2".

 

I think I can have my python script generate multiple files for each analysis and then import them into JMP individually and then merge the tables, but wanted to check if there's an easier way to do this. (I have a hundred thousand to a few million rows per column if that makes a difference)

 

Thanks for any help! 

3 REPLIES 3
Highlighted
mek249
Level II

Re: Import csv files sorted by columns, not rows?

Nvm--I'll I have it in separate files that I can import with "filename" as the second column and work from there!
Highlighted
txnelson
Super User

Re: Import csv files sorted by columns, not rows?

I saved your simple data table to a .txt file, and used the following script to input the data and ended up withabc.PNG

Names default to here(1);

dt=Open(
	"<Path to CSV or TXT file >",
	Import Settings(
		End Of Line( Semicolon ),
		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( 0 ),
		Column Names Start( 1 ),
		Data Starts( 1 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
);

AllColumns = dt << get column names();
remove from(AllColumns,1,1);
dtFinal = dt << Transpose(
	columns( AllColumns ),
	Label( :Column 1 ),
	Output Table( "Transpose of semicolon" )
);
dtFinal << delete columns(:Label );
Jim
Highlighted
mek249
Level II

Re: Import csv files sorted by columns, not rows?

Thank you very much!

 

It worked perfectly on the simplified data, but I cut JMP off after 10min of spinning its wheels with my actual data set. Importing the multiple files seems the best way to go for me. Thank you again though.

Article Labels

    There are no labels assigned to this post.