topic open xls data in Discussions

open xls data

SC1 — Tue, 22 Jan 2019 13:32:48 GMT

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

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(

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 )

)　

);

Re: open xls data

txnelson — Tue, 22 Jan 2019 16:49:53 GMT

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.

Re: open xls data

briancorcoran — Tue, 22 Jan 2019 18:58:57 GMT

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

Re: open xls data

SC1 — Tue, 22 Jan 2019 23:00:22 GMT

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

Re: open xls data

SC1 — Tue, 22 Jan 2019 23:10:22 GMT

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

Re: open xls data

briancorcoran — Wed, 23 Jan 2019 12:58:52 GMT

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

Re: open xls data

pmroz — Wed, 23 Jan 2019 13:59:58 GMT

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.

Re: open xls data

SC1 — Wed, 23 Jan 2019 14:01:19 GMT

xls 2013