Subscribe Bookmark RSS Feed

importing data with field descriptions

rogerc

Community Trekker

Joined:

Jun 23, 2011

Hi,

I have geographic data that can be read as a CSV or in a spreadsheet. I would like to read it into JMP with all the information automatically put where it needs to be. The first entry is the field name. The second entry in each field is the field description, and the entries thereafter are values that I want to evaluate:

var1, var2, var3,...

blah blah one, blah blah 2, blah blah three,...

10, 15, 0,...

2, 3,5,...

...

There are a lot of fields and the structure is the same across regions. So I would like to load this into a table that reads the first entry as a the name, puts the second entry into the notes section of the field, and leaves the rest as entries in the table. Any ideas on how to do this or how to write a script that would such a thing?  Thanks.

2 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

I'm not entirely sure of the structure of your data.  But the following approach could work:

  • Read the data into a JMP dataset, "as is".

  • Run a custom JSL script to reformat the data into the form you want.

It's hard to say more without seeing some real or even fake data.  Can you post a snippet of the data?

ms

Super User

Joined:

Jun 23, 2011

One idea is to open the file twice: one copy without the second row and a second temporary copy with the second row only and then add the notes to the first table from the second table by JSL.

JSL example:

//Open the table, use 1st row as column names, skip 2nd row

dt = Open(

  "/path/file.csv",

  Import Settings(

  End Of Field( Comma ),

  Scan Whole File( 1 ),

  Labels( 1 ),

  Column Names Start( 1 ),

  Data Starts( 3 ),

  Lines To Read( All ),

  )

);

//Open a temporary subset of the same table with 2nd row only

dt1 = Open(

  "/path/file.csv",

  Import Settings( End Of Field( Comma ), Labels( 0 ), Data Starts( 2 ), Lines To Read( 1 ) )

);

// Add notes from first line of dt1

For( i = 1, i <= N Col( dt ), i++,

  Column( dt, i ) << set property( "notes", Column( dt1, i )[1] )

);

// Close temporary table

Close( dt1, no save );