topic JSL Saving EXCEL format and Keeping Leading Zeroes in Discussions

JSL Saving EXCEL format and Keeping Leading Zeroes

fat_angus — Sat, 10 Jun 2023 23:44:01 GMT

Greetings,

I have a JMP file that has leading 0s in several of the columns that I want to keep.

Data Type is Character... and Modeling Type is Nominal

When I save the file into EXCEL format using the JSL code below, the leading 0s disappear. Is there anyway to keep them in?

du = Cust_join << Save( Concat("C:\file",Format( Today(), "yyyymmdd" ),".xlsx") );

I suppose script some code that puts an apostrophe in front of every instance but there are some row

Re: JSL Saving EXCEL format and Keeping Leading Zeroes

txnelson — Thu, 10 Feb 2022 21:50:09 GMT

Here is the file name that I get using your JSL......where are the leading zeros that have been dropped

"C:\file20220210.xlsx"

What release of JMP are you using?

回复： JSL Saving EXCEL format and Keeping Leading Zeroes

lala — Fri, 11 Feb 2022 06:02:59 GMT

dt << Save( "C:\file" || Char( Format( Today(), "y/m/d" ) ) || Char( Hour( Today() ) ) || Char( Minute( Today() ) ) || ".xlsx" );

Re: JSL Saving EXCEL format and Keeping Leading Zeroes

fat_angus — Fri, 11 Feb 2022 13:22:15 GMT

Hi Txnelson...

Using JMP16.

I am not at all concerned about the file name. I am concerned about the columns in the file that have leading 0's. They look look fine in JMP but when I save as an EXCEL file using the script above or even using File... Save... As, the columns delete the leading 0's.

Example:

IN JMP I have file that contains a column that looks like this;

When I save it in EXCEL, the leading 0's are trimmed... thus, 0001585350 gets trimmed to 1585350

Re: JSL Saving EXCEL format and Keeping Leading Zeroes

txnelson — Fri, 11 Feb 2022 14:50:06 GMT

JMP is exporting all of the leading zeros. It is Excel that is stripping them off. Your suggestion of placing a non numeric value in of the column's values would be one way to preserve the leading zeros.

Maybe another community member has a better solution

回复： JSL Saving EXCEL format and Keeping Leading Zeroes

pmroz — Fri, 11 Feb 2022 15:09:27 GMT

It does appear that Excel is stripping off the leading zeroes. Guess you'll have to stay in JMP and not use Excel LOL. But if you must use Excel, you can preface the values with a single quote. Here are the JMP table and Excel spreadsheet: