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
solpark
Level I

Export data tables to Excel without column name

I'm trying to save data tables as excel format (.xlsx) without column names. I've already changed the preferences, but it still does not work. (I mean the data tables are saved with column names)

 

Get Preference( Export settings);

Preferences(
Export Settings(
End Of Line( CRLF ),
End Of Field( CSV( 1 ) ),
Export Table Headers( 0 ),
Quote all column names( 0 ),
Quote all character values( 0 ),
Quote all numeric values( 0 )
)
)

 

I tried the following two ways to save my data tables:

dt << Save AS(Path||"Test.xlsx", Excel File);  // dt = DataTable(Table_1[1]);
Create Excel Workbook(Path||"Test.xlsx", Table_1, Table_2); 
// Table_1 & 2 are lists of data table names

Please let me know if I'm missing something.

1 ACCEPTED SOLUTION

Accepted Solutions
vince_faller
Super User (Alumni)

Re: Export data tables to Excel without column name

So those export settings appear to only affect .txt files.  You could export as txt and then change it to csv, but that's the best I could find.

 

Names Default to Here( 1 );
dt = open("$SAMPLE_DATA\Big Class.jmp");
dt_a = open("$SAMPLE_DATA\Abrasion.jmp");

dt << Save("$DESKTOP/Example.txt");
rename file("$DESKTOP/Example.txt","Example.csv");

If you wanted multiple sheets in a workbook you could then reimport it with column headers (as the values and then save).  

 

Names Default to Here( 1 );
dt = open("$SAMPLE_DATA\Big Class.jmp");
dt_a = open("$SAMPLE_DATA\Abrasion.jmp");

dt << Save("$TEMP/Example1.txt");
dt_a << Save("$TEMP/Example2.txt");

dt_no_headers = open("$TEMP/Example1.txt");
dt_a_nh = open("$TEMP/Example2.txt");
Create Excel Workbook(
	"$DESKTOP\MyWorkbook.xlsx",
	{dt_no_headers, dt_a_nh},
	{"Big", "Abrasive"}
);

Not a great solution.  Might be worth adding export preferences for other file types in the JMP Wish List.

Vince Faller - Predictum

View solution in original post

3 REPLIES 3
vince_faller
Super User (Alumni)

Re: Export data tables to Excel without column name

So those export settings appear to only affect .txt files.  You could export as txt and then change it to csv, but that's the best I could find.

 

Names Default to Here( 1 );
dt = open("$SAMPLE_DATA\Big Class.jmp");
dt_a = open("$SAMPLE_DATA\Abrasion.jmp");

dt << Save("$DESKTOP/Example.txt");
rename file("$DESKTOP/Example.txt","Example.csv");

If you wanted multiple sheets in a workbook you could then reimport it with column headers (as the values and then save).  

 

Names Default to Here( 1 );
dt = open("$SAMPLE_DATA\Big Class.jmp");
dt_a = open("$SAMPLE_DATA\Abrasion.jmp");

dt << Save("$TEMP/Example1.txt");
dt_a << Save("$TEMP/Example2.txt");

dt_no_headers = open("$TEMP/Example1.txt");
dt_a_nh = open("$TEMP/Example2.txt");
Create Excel Workbook(
	"$DESKTOP\MyWorkbook.xlsx",
	{dt_no_headers, dt_a_nh},
	{"Big", "Abrasive"}
);

Not a great solution.  Might be worth adding export preferences for other file types in the JMP Wish List.

Vince Faller - Predictum
solpark
Level I

Re: Export data tables to Excel without column name

Thank you for your reply. I think I need to submit these requirement to JMP Wish List :(

Because I have to save the "cell color" as well as the data itself.

 

vince_faller
Super User (Alumni)

Re: Export data tables to Excel without column name

Another workaround that would probably be a little better would be to just set the name of the columns the values of the first row.  

Still not great, but eh. 

 

Names Default to Here( 1 );
dts = {};
dts[1] = open("$SAMPLE_DATA\Big Class.jmp");
dts[2] = open("$SAMPLE_DATA\Abrasion.jmp");

for(i=1, i<=nitems(dts), i++, 
	dt = dts[i];
	items = dt[1, 0];
	dt << Delete Rows(1);
	for(k=1, k<=ncols(dt), k++, 
		Column(dt, k) << Set Name(char(items[k])); // you'll have to do something to deal with dates
	)
);
Create Excel Workbook(
	"$DESKTOP\MyWorkbook.xlsx",
	dts,
	{"Big", "Abrasive"}
);
Vince Faller - Predictum