BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
solpark
Community Trekker

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.

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
vince_faller
Super User

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
0 Kudos
3 REPLIES 3
vince_faller
Super User

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
0 Kudos
solpark
Community Trekker

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.

 

0 Kudos
vince_faller
Super User

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
0 Kudos