cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar

Open Multiple Files and Then Rename Columns in all of the Tables

I have script that gives the user the option to select multiple files of the same type, open them and then rename the columns. Selecting and opening the files is working fine, but renaming the columns has been an issue. Below is what I have so far.

Names Default To Here( 1 );
Files = Pick File(
	"Select File(s)",
	"C:\Users\dkraeuter\Desktop\Temp\Test",
	{"All Files|*"},
	1,
	0,
	"",
	"multiple"
);
y = {"Test", "Run", "Model"};
For( i = 1, i <= N Items( Files ), i++,
	Try( Open( Files[i], "text" ) );
	dt = Current Data Table() // Use Current Data Table;
	For( n = 1, n < 56, n++,
		:Column( n ) << Set Name( y[n] )
	);
);

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: Open Multiple Files and Then Rename Columns in all of the Tables

The errors I found were the following

1. The path to the default directory was not found.  So I corrected it to point to a folder I knew I had, and then the Pick File worked fine

2. There was a missing ";" at the end of the "y =" statement.  Once that was fixed the code parsed correctly.

3. The Column() function is not like the :Name() function.  There is not a ":"  in the beginning.

4. I also got rid of the "dt = current data table():".  By placing it as the result of the Open() function, the setting of it is no longer necessary.

5. I also added in the data table reference to the Column() function.  It is the most formal way to specify the Column() function, and it avoids any question on which column it is pointing to.

6. The Wait( 0 ); is just a safety precaution.  It insures the data table has been completly read in before the For() loop is run.  You also may want to use

dt << run formulas;

It also will make sure the data table has been run in.

Here is my version of your code:

Names Default To Here( 1 );
Files = Pick File( "Select File(s)", "C:\Users\<your logonID>", {"All Files|*"}, 1, 0, "", "multiple" );
y = {"Test", "Run", "Model"};
For( i = 1, i <= N Items( Files ), i++,
	Try( dt = Open( Files[i], "text" ) );
	wait( 0 );
	For( n = 1, n < 56, n++,
		Column( dt, n ) << Set Name( y[n] )
	);
);

 

Jim

View solution in original post

txnelson
Super User

Re: Open Multiple Files and Then Rename Columns in all of the Tables

Your Y list only has 49 elements in it, and you are attempting to go to 56.  The other posibility is that the 50 data table you are reading is > 49 columns for the first time.  You should check for that in the code, and only loop through 49 columns, or if the data table has less columns, then only loop 1 though the number of columns.

Jim

View solution in original post

7 REPLIES 7
txnelson
Super User

Re: Open Multiple Files and Then Rename Columns in all of the Tables

What error message are you getting in the log?  My guess is that it is stating that your subscript is out of range.  You are looping your variable 'n' from 1 to 55 and referencing your variable 'y'.  As soon as 'n' is equal to 4, you have run out of values in your 'y' list.

Jim

Re: Open Multiple Files and Then Rename Columns in all of the Tables

My error says something about an unrecognized ";" or ")". As far as I can tell it has something to do with the open command right after the For loop. When I comment out the Open command, then the second for loop seems to be okay.

 

The 55 came from the actual number of columns that I am renaming. When I reposted I changed the total number of column names I listed, but forgot to change the total "n" count allowed.

txnelson
Super User

Re: Open Multiple Files and Then Rename Columns in all of the Tables

The errors I found were the following

1. The path to the default directory was not found.  So I corrected it to point to a folder I knew I had, and then the Pick File worked fine

2. There was a missing ";" at the end of the "y =" statement.  Once that was fixed the code parsed correctly.

3. The Column() function is not like the :Name() function.  There is not a ":"  in the beginning.

4. I also got rid of the "dt = current data table():".  By placing it as the result of the Open() function, the setting of it is no longer necessary.

5. I also added in the data table reference to the Column() function.  It is the most formal way to specify the Column() function, and it avoids any question on which column it is pointing to.

6. The Wait( 0 ); is just a safety precaution.  It insures the data table has been completly read in before the For() loop is run.  You also may want to use

dt << run formulas;

It also will make sure the data table has been run in.

Here is my version of your code:

Names Default To Here( 1 );
Files = Pick File( "Select File(s)", "C:\Users\<your logonID>", {"All Files|*"}, 1, 0, "", "multiple" );
y = {"Test", "Run", "Model"};
For( i = 1, i <= N Items( Files ), i++,
	Try( dt = Open( Files[i], "text" ) );
	wait( 0 );
	For( n = 1, n < 56, n++,
		Column( dt, n ) << Set Name( y[n] )
	);
);

 

Jim

Re: Open Multiple Files and Then Rename Columns in all of the Tables

I am using the script and have run into an issue. When the value for "n" exceeds 50, then it just opens one file and renames the colums, no matter the number of files selected. The error I recieve in the log is: Subscript Range in access or evaluation of 'y[n]' , y[/*###*/n].

Here is my current script.

 

Names Default To Here( 1 );
Files = Pick File(
	"Select File(s)",
	"C:\Users\dkraeuter\Desktop\Temp\Azure",
	{"All Files|*"},
	1,
	0,
	"",
	"multiple"
);
y = {"Blank", "Feed Step 2", "Cycle Stopped", "Spindle Speed 1", "Spindle Speed 2", "Number of Stones",
"Percent of Surface Interuptions", "Honing Time ", "Tool Type", "Fault Code", "Serial Number", "Feed Force 1",
"Feed Force 2", "Bore Compensation", "Feed Position at Bore", "Feed Position at End", "Feed Position at Target",
"Wedge Angle", "Part Count", "Setup Name", "Part Material", "Part Hardness", "Stone Type", "Stone Length",
"Stone Width", "Coolant Type", "Cycle Run Status", "Cycle Stone Condition", "Cycle Incoming Condition",
"Machine Model", "Stroke Speed 1", "Stroke Speed 2", "Feed Rate 1", "Feed Rate 2", "Stroke Length", "Bore Length",
"VSRR Calc", "Average Torq Spindle", "Average Torq Stroker", "Average Torque Feed", "Average Feed Force",
"Average Part Torque", "Target Final Diameter", "Surface Finish Ra", "Surface Finish Rz", "Surface Finish Rt",
"1", "2", "3"};
For( i = 1, i <= N Items( Files ), i++,
	Try( dt = Open( Files[i], "text" ) );
	Wait( 0 );
	For( n = 1, n < 56, n++,
		Column( dt, n ) << Set Name( y[n] )
	);
);
txnelson
Super User

Re: Open Multiple Files and Then Rename Columns in all of the Tables

Your Y list only has 49 elements in it, and you are attempting to go to 56.  The other posibility is that the 50 data table you are reading is > 49 columns for the first time.  You should check for that in the code, and only loop through 49 columns, or if the data table has less columns, then only loop 1 though the number of columns.

Jim
Roselle_Grant
Level II

Re: Open Multiple Files and Then Rename Columns in all of the Tables

Hello Jim, could you please help me with the below:

 

What if I have 2 data tables which I am importing and I want to change the name of columns in both... will the section of the script below not work? As it is, it only changes the name of the columns in one table but not the other. 

 


dt1 = Data Table(1);
dt1 << select where( :Result_Valid != 1 );
dt1 << hide and exclude;
dt1 << select excluded;
dt1 << delete rows;
dt1 << set name ("Data 2");
dt1 << Delete Columns( "Part Number", "Unique ID");
dt1 << Text To Columns(delimiter( "_" ),columns( :File Name ),);
dt1 = :File Name 1 << set name ("Run Time");
dt1 = :File Name 2 << set name ("Dimension");
dt1 = :File Name 3 << set name ("Data Type");

dt2 = Data Table(2);
dt2 << select where( :Result_Valid != 1 );
dt2 << hide and exclude;
dt2 << select excluded;
dt2 << delete rows;
dt2 << set name ("Data 1");
dt2 << Delete Columns( "Part Number", "Unique ID");
dt2 << Text To Columns(delimiter( "_" ),columns( :File Name ),);
dt2 = :File Name 1 << set name ("Run Time");
dt2 = :File Name 2 << set name ("Dimension");
dt2 = :File Name 3 << set name ("Data Type");

txnelson
Super User

Re: Open Multiple Files and Then Rename Columns in all of the Tables

Are you getting any errors in the log?

Jim