Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
SC1
SC1
Level I

open xls data

I have a tab in xls  sheet wich combines data from different xls tabs into one which I try to open via jsl for further data processing in jmp. When I use the open function the data downloaded into jmp is wrong. Either no values are copied into jmp are fake dates (1900s). 

However, when I use xls add in from jmp to convert table into jmp table I get the correct data. Is there a method to change the open function to get the right data or can the xls addin of jmp be called in a jsl script.?

 

thanks

 

SC

 

Openfunction I am using to collect the xls data

Open(Filename,

 Worksheets( "xls to jmp"),

 Use for all sheets( 0 ),

 Concatenate Worksheets( 0 ),

 Create Concatenation Column( 1 ),

 Worksheet Settings(

1,

Has Column Headers( 1 ),

Number of Rows in Headers( 1 ),

Headers Start on Row( 1 ),

Data Starts on Row( 2 ),

Data Starts on Column( 1 ),

Data Ends on Row( 0 ),

Data Ends on Column( 0 ),

Replicated Spanned Rows( 1 ),

Suppress Hidden Rows( 1 ),

Suppress Hidden Columns( 1 ),

Suppress Empty Columns( 1 ),

Treat as Hierarchy( 0 )

) 

);

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
pmroz
Super User

Re: open xls data

I would upgrade to JMP 14 if you can.  JMP 12 cannot read later versions of MS-Office Excel.  BTW what version of MS-Office are you using?

 

We're still using JMP 12 and use a workaround whereby we save the .xlsx file to either .csv or .xls, and then it reads into JMP OK.

View solution in original post

7 REPLIES 7
Highlighted
txnelson
Super User

Re: open xls data

What version of JMP are you using?

When you say "Excel Addin", is it the Excel Wizard?

 

If you use the Excel Wizard to open one of the workbook spreadsheets, you can go to the "Source" triangle in the Table Panel of the data table that it produced, and get the code required to open the spreadsheet.  Cut and paste that into your script, and it should work.

Jim
Highlighted
SC1
SC1
Level I

Re: open xls data

Hi,

jmp is version 12.2 pro.

xls add in is jmp profiler gui com add in.

When I juste copy paste values of the tab to other tab in xls and open in
jmp there is no issue but copy and paste values is not something workable
due to the amount of files.

SC1
Highlighted

Re: open xls data

If you could share a sample of the data and let us know what version of JMP you are using and on what operating system it would help a lot.

 

Brian Corcoran

JMP Development

Highlighted
SC1
SC1
Level I

Re: open xls data

Hi,

I realise sharing would help but the values in the tab are queried values
to a wonderware database system which I cannot share unfortunately. When I
copy and paste the values into xls and open in jmp 12.2 pro via jsl open
command it works but I would like to open the tab directly with jsl. I have
a windows 8 system 64 bit.

When I use the xls com add in of jmp directly on the queried tab it works
but the script then only gives the column names and the values so nothing I
could leverage in a jls script.

Regards,

SC1
Highlighted

Re: open xls data

It may be an extra step, but opening this with the Excel Wizard in JMP should work.  You would probably need to get JMP 14 with the latest improvements. 

 

Brian

Highlighted
pmroz
Super User

Re: open xls data

I would upgrade to JMP 14 if you can.  JMP 12 cannot read later versions of MS-Office Excel.  BTW what version of MS-Office are you using?

 

We're still using JMP 12 and use a workaround whereby we save the .xlsx file to either .csv or .xls, and then it reads into JMP OK.

View solution in original post

Highlighted
SC1
SC1
Level I

Re: open xls data

xls 2013
Article Labels

    There are no labels assigned to this post.