Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level III

## storing data tables from open() into a list

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",

//do something with the tables
for (i = 1, i <= NITEMS(Tables), i++,
close(Tables[i], nosave);
);``````

Kind regards

Anders

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: storing data tables from open() into a list

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 5
Highlighted
Super User

## Re: storing data tables from open() into a list

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)
);
``````

Highlighted
Level III

## Re: storing data tables from open() into a list

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

## Re: storing data tables from open() into a list

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

Highlighted
Super User

## Re: storing data tables from open() into a list

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.

Highlighted
Level III

## Re: storing data tables from open() into a list

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 ),

Year Rule( "20xx" )

),

);

file[i] = dt;

i = i + 1;

f--;

....

);

Article Labels

There are no labels assigned to this post.