Subscribe Bookmark RSS Feed

storing data tables from open() into a list

anders_bankefor

Community Trekker

Joined:

Dec 8, 2015

Hi,

 

I would like to use the open() keyword to open multiple worksheets and storing these datatables into a list.

 

Tables = {};
//normaly I would like to use a for loop here, however the number of worksheets will vary.
Tables = Open ("pathway",
invisible, info about the data..);
 
//do something with the tables
for (i = 1, i <= NITEMS(Tables), i++,
close(Tables[i], nosave);
);

 

Additional question:

Also is it possible to use a username/password protected searchengine/library to search and download information to jmp?

 

Kind regards

Anders

1 ACCEPTED SOLUTION

Accepted Solutions
ms

Super User

Joined:

Jun 23, 2011

Solution

I think a loop will be required here to build the list. The Files in Directory() function is useful if the number of files vary, and they all are found in the same directory.

An example:

// Define path and file type
mypath = "path";
ftype = ".jmp";

// Make list of files, remove all non-matching files
flist = Files In Directory(mypath);
For(i = N Items(flist), i > 0, i--,
    If(!Ends With(flist[i], ftype),
        Remove From(flist, i)
    )
);

// Open and create list of tables
Tables = Repeat(
    i = 0;
    {i++ ; Open(mypath || flist[i])};,
    N Items(flist)
);

//do something with the tables
For(i = 1, i <= N Items(Tables), i++,
    Close(Tables[i], No Save)
);

 

5 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Solution

I think a loop will be required here to build the list. The Files in Directory() function is useful if the number of files vary, and they all are found in the same directory.

An example:

// Define path and file type
mypath = "path";
ftype = ".jmp";

// Make list of files, remove all non-matching files
flist = Files In Directory(mypath);
For(i = N Items(flist), i > 0, i--,
    If(!Ends With(flist[i], ftype),
        Remove From(flist, i)
    )
);

// Open and create list of tables
Tables = Repeat(
    i = 0;
    {i++ ; Open(mypath || flist[i])};,
    N Items(flist)
);

//do something with the tables
For(i = 1, i <= N Items(Tables), i++,
    Close(Tables[i], No Save)
);

 

anders_bankefor

Community Trekker

Joined:

Dec 8, 2015

Thank you for your quick reply. Its a very elegant code and it solved my problem perfectly thank you.
anders_bankefor

Community Trekker

Joined:

Dec 8, 2015

Hi,

 

I've tried to incorperate this code into other sections of my script, however I cannot get dt = Tables [i] or dt = Data Table (Tables[i]) to reference to my tables in Tables. See below for examples:

 

// I used your code to open all files in a designated directory, while debugging I can see that Tables contains a reference to my CSV files so this part seems to be working

for (i =1, i <= N ITEMS (Tables), i++,

dt = Data Table( Tables[i] );   //JMP cannot locate the reference for any table in Tables. dt = Tables[i] doesnt work either.

dt << New Column( "Signal" );

dt << Move Selected Columns( {“Signal”}, To First );

:Signal << data type( Character );

col = Column( 1 );

dt << New Column( "Source Table", Character );

:Source Table << Set Each Value( dt << Get Name() );

dt << Move Selected Columns( {“Source table”}, To First );

ms

Super User

Joined:

Jun 23, 2011

I tried my code with .csv files and it works (JMP 12.2 for Mac). It also works with invisible files.

The Tables list should look something like this:

 

Show(Tables)

/*:

Tables = {DataTable("Data 1"), DataTable("Data 2"), DataTable("Data 3")};

 

Make sure the csv opens properly as a Data Table in JMP (temporarily turn off invisible) and that the table has not been closed in a previous operation.

 

anders_bankefor

Community Trekker

Joined:

Dec 8, 2015

hi,

yes my list looks like that.

 

I solved the problem by implementing a while loop (file = Tables). When I had two loops refering to the same list JMP could not find my referenses. To me it seems that Tables is declared outside of your loop by saying that Tables = repeat (....), however even if I declare Tables = {} outside of your loop it could still not be refered to by my second for loop. Do you know the reason for this?

 

f = N Items (flist)

While( f > 0,

i = 1;

dt = Open(

mypath || flist[f],

Import Settings(

End Of Line( CRLF, CR, LF ),

End Of Field( Other( ";" ), CSV( 0 ) ),

Strip Quotes( 1 ),

Use Apostrophe as Quotation Mark( 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" )

),

);

file[i] = dt;

i = i + 1;

f--;

....

);