topic Re: JMP 16: Add Timestamp to Excel File Name in Discussions

JMP 16: Add Timestamp to Excel File Name

WoHNY — Thu, 08 Jun 2023 16:31:56 GMT

I am exporting jmp data tables to Excel using the script below,

//Specifying Data Tables For Export to Excel
tableList = {tab1DT,tab2DT,tab3DT,tab4DT,tab5DT,tab6DT};

//Specifying the Worksheets in the Excel Workbook
sheetList = {"WS1","WS2","WS3","WS4","WS5","WS6"};

//Send to Excel FIle
Create Excel Workbook( "C:/Users/FileName.xlsx",tableList,sheetList);

Right now the Excel file name will be FileName.xlsx. I would like to add the timestamp (Format YYYYMMDDHHMM) at the beginning of the file name so for example, 202303021550 FileName.xlsx. Will the Timestamp at Start function accomplish this? Not sure how to apply.

Re: JMP 16: Add Timestamp to Excel File Name

jthi — Fri, 03 Mar 2023 06:16:09 GMT

Many different ways of doing this and which to use depends where you are getting your timestamp, your JMP version and your regional settings.

I'm not sure if JMP will recognize that format so you might have to build it by using different functions. One option is to first build it as something JMP will recognize and then use Substitute (or Regex) to remove unnecessary characters. Format Pattern only works if you have JMP16+

Names Default To Here(1);
date_str = Format(Today(), "Format Pattern","<YYYY><-><MM><-><DD> <hh24><::><mm>");

//using regex
date_cleaned = Regex(date_str, "[^\d]", "", GLOBALREPLACE);

//using subsitute
date_cleaned = Substitute(date_str, ".", "", ":", "", "-", "", " ", "");

Quite often when I'm saving filenames with timestamp from JMP, I will just use Char(Today()) which will then be seconds from 1904-01-01 00:00:00

Re: JMP 16: Add Timestamp to Excel File Name

WoHNY — Fri, 03 Mar 2023 13:54:41 GMT

Jarmo: Thank you for the response. If I go with Char(Today()) how do I reference it in the path/file name? Whatever I place prior to FileName.xlsx in the path/name string below will just add as is to the FileName. Define

date = Char(Today()) but then how do I get this incorporated into the file name? is there a special character that will reference it?

Create Excel Workbook( "C:/Users/FileName.xlsx",tableList,sheetList);

Re: JMP 16: Add Timestamp to Excel File Name

txnelson — Fri, 03 Mar 2023 13:58:22 GMT

Create Excel Workbook( "C:/Users/" || date || "FileName.xlsx",tableList,sheetList);

The || operator allows you to concatenate strings.

Re: JMP 16: Add Timestamp to Excel File Name

WoHNY — Fri, 03 Mar 2023 14:49:47 GMT

Jim:

Thank you for the input.

Greg