topic Re: Copying Columns, Column Names and Table name to a New Table in Discussions

Copying Columns, Column Names and Table name to a New Table

luqi — Thu, 04 Feb 2016 23:02:10 GMT

Hello,

I'm having a bit of trouble copying columns from multiple data tables into new blank table. The data tables to be opened all have the same column names.

So far, I can open multiple tables but can only copy columns from one the latest data table opened.

Here is my code below:

Names Default To Here( 1 );

// Use 'PickFile()' to get a list of files to open (Windows only)

fileList = Pick File(

"Select one of more files", // Prompt

"$DESKTOP", // Initial folder

{"JMP Files|jmp;jsl;jrn;csv", "All Files|*"}, // List of file filters to apply (ignored by OS/X)

1, // Initial file filter to apply (index of item in the list above)

0, // Save flag - Specify either a 'Save' or 'Open' window. Set a value of 1 or 0 respectively.

Empty(), // Default file

multiple // Multiple - allows more than one file to be opened (ignored by OS/X)

);

// If only a single file is selected, fileList will not be a list, so we need to build it for ourselves

If( !Is List( fileList ),

fileList = Eval List( {fileList} )

);

// Open the files and store their corresponding JMP table names

tableNames = {};

For( f = 1, f <= N Items( fileList ), f++,

dt = Open( fileList[f] );

Insert Into( tableNames, dt << getName );

);

//Create Column Copier to copy files

col_dlg = new window("Column Copier",

panelbox("Select four columns to create a new table with:",

col_clist = collistbox(all, width(200), max selected(4)),

buttonbox("OK",

selected_column_list = col_clist << getselected;

col1 = selected_column_list[1];

col2 = selected_column_list[2];

col3 = selected_column_list[3];

col4 = selected_column_list[4];

// Create a new table using just the two selected columns

dt2 = dt << Subset( columns(column(dt, col1), column(dt, col2), column(dt, col3), column(dt, col4) ) );

col_dlg << close window;

)

);

Note: The new data table which is created is a subset of the latest data table opened from the multiple data table. I would like to be able to choose any copy any column from any of the data tables into the new one. Thank you.

Re: Copying Columns, Column Names and Table name to a New Table

txnelson — Fri, 05 Feb 2016 01:40:10 GMT

The code below reads in each file and then concatenates them together into one data table, which then is processed by your Column Copier

Names Default To Here( 1 );

// Use 'PickFile()' to get a list of files to open (Windows only)

fileList = Pick File(

"Select one of more files", // Prompt

"$DESKTOP", // Initial folder

{"JMP Files|jmp;jsl;jrn;csv", "All Files|*"}, // List of file filters to apply (ignored by OS/X)

1, // Initial file filter to apply (index of item in the list above)

0, // Save flag - Specify either a 'Save' or 'Open' window. Set a value of 1 or 0 respectively.

Empty(), // Default file

multiple// Multiple - allows more than one file to be opened (ignored by OS/X)

);

// If only a single file is selected, fileList will not be a list, so we need to build it for ourselves

If( !Is List( fileList ),

fileList= Eval List( {fileList} )

);

// Open the files and store their corresponding JMP table names

tableNames = {};

For( f = 1, f <= N Items( fileList ), f++,

dttemp= Open( fileList );

Insert Into( tableNames, dt << getName );

// Append the data tables together

If( f == 1,

dt= dttemp,

dt<< concatenate( dttemp, append to first table( 1 ) );

Close( dttemp, nosave );

);

//Create Column Copier to copy files

col_dlg = New Window( "Column Copier",

Panel Box( "Select four columns to create a new table with:", col_clist = Col List Box( all, width( 200 ), max selected( 4 ) ), ),

Button Box( "OK",

selected_column_list= col_clist << getselected;

col1= selected_column_list[1];

col2= selected_column_list[2];

col3= selected_column_list[3];

col4= selected_column_list[4];

// Create a new table using just the two selected columns

dt2= dt << Subset( columns( Column( dt, col1 ), Column( dt, col2 ), Column( dt, col3 ), Column( dt, col4 ) ) );

col_dlg<< close window;

)

);

Re: Copying Columns, Column Names and Table name to a New Table

luqi — Fri, 05 Feb 2016 19:11:48 GMT

Hello Jim,

Thanks for the help but it still doesn't work. When I run the new code this time, the column copier does not show up and the only one of the files I want to open shows up.

Re: Copying Columns, Column Names and Table name to a New Table

txnelson — Fri, 05 Feb 2016 20:07:44 GMT

I modified the code, to read in each of the 4 files, and to append them to the first file. Therefore, the end result should be that you only have one file, but all of the data from each of the files is in it.

I could not fully test out your code, so there may be a slight problem. The only code I changed was in the For loop. Please run the code again, and then look in the log View==>Log and see what the error is. Also, check in the data table, and verify that all of the data have made it into the data table.

Re: Copying Columns, Column Names and Table name to a New Table

luqi — Tue, 09 Feb 2016 15:50:38 GMT

I checked the data table but still only opens one. Here is the error I got from the log below:

Name Unresolved: dt in access or evaluation of 'dt' , dt/*###*/

In the following script, error marked by /*###*/

Names Default To Here( 1 );

fileList = Pick File(

"Select one of more files",

"$DESKTOP",

{"JMP Files|jmp;jsl;jrn;csv", "All Files|*"},

Empty(),

multiple

);

If( !Is List( fileList ),

fileList = Eval List( {fileList} )

);

tableNames = {};

For( f = 1, f <= N Items( fileList ), f++,

dttemp = Open( fileList );

Insert Into( tableNames, dt/*###*/ <<getName );

If( f == 1,

dt = dttemp,

dt << concatenate( dttemp, append to first table( 1 ) );

Close( dttemp, nosave );

);

col_dlg = New Window( "Column Copier",

Panel Box( "Select four columns to create a new table with:",

col_clist = Col List Box( all, width( 200 ), max selected( 4 ) )

Button Box( "OK",

selected_column_list = col_clist << getselected;

col1 = selected_column_list[1];

col2 = selected_column_list[2];

col3 = selected_column_list[3];

col4 = selected_column_list[4];

dt2 = dt << Subset(

columns(

Column( dt, col1 ),

Column( dt, col2 ),

Column( dt, col3 ),

Column( dt, col4 )

)

);

col_dlg << close window;

)

Re: Copying Columns, Column Names and Table name to a New Table

txnelson — Tue, 09 Feb 2016 16:09:55 GMT

The error....which was my error....is in the line

Insert Into( tableNames, dt << getName );

The line should read:

Insert into( tableNames, dttemp << getName );

Re: Copying Columns, Column Names and Table name to a New Table

luqi — Tue, 09 Feb 2016 16:56:54 GMT

Thank you so much, that worked. I figured that was the problem, just one last help. The concatenation was vertical. I am try to Horizontally concatenate the data table. So I used "Join" instead of "concatenate" but it opens 2 separate data table instead of combining them together.