topic Re: Export data tables to Excel without column name in Discussions

Export data tables to Excel without column name

solpark — Wed, 31 Jul 2019 05:48:04 GMT

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.

Re: Export data tables to Excel without column name

vince_faller — Wed, 31 Jul 2019 11:47:40 GMT

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.

Re: Export data tables to Excel without column name

solpark — Thu, 01 Aug 2019 05:03:08 GMT

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.

Re: Export data tables to Excel without column name

vince_faller — Thu, 01 Aug 2019 17:11:47 GMT

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