open xls data

[#1]

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 )

)　

);

[#2]

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.

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7 · Accepted solution]

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.

[#8]

xls 2013