266749_en-US

liliconcom

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:

 

 

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! 

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!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 with

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 );</PATH>

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.