BookmarkSubscribeRSS Feed
sirihofstadtrap

Community Trekker

Joined:

Feb 17, 2016

Import (open) data from excel

Hi,

I'm trying to import data from excel by using the open function

My code is as follows:

Open("C:\Users\"username"\Documents\Statistics\Test.xlsx");

I use my username instead of "username"

I don't get any error messages, but nothing happens when I run the script.

Why does it not open?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
sirihofstadtrap

Community Trekker

Joined:

Feb 17, 2016

Solution

Re: Import (open) data from excel

Thank you!

It works if I use the following code:

open("C:\Users\......\Test.xlsx",

                        Worksheets( "Data" ), 

                        Use for all sheets( 1 ),

                        Concatenate Worksheets( 0 ),

                        Create Concatenation Column( 0 ),

                        Worksheet Settings(

                                            1,

                                            Has Column Headers( 1 ),

                                            Number of Rows in Headers( 2 ),

                                            Headers Start on Row( 1 ),

                                            Data Starts on Row( 3 ),

                                            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 ),

                                            Treat as Hierarchy( 0 )

                                        )

            );

5 REPLIES
r23426

Community Trekker

Joined:

Jul 3, 2014

Re: Import (open) data from excel

Hi,

Nothing in the log ?

I use the following to open xls file.

The full path  (C:\Users\...............\filname.xls) is in my names variable as my script was to open and concatenate several excel files.

The code below will open the 1st table only.

dt1st = open(names[1],

                        Worksheets( "Data" ),  // do not change as it is an excel worksheet name

                        Use for all sheets( 1 ),

                        Concatenate Worksheets( 0 ),

                        Create Concatenation Column( 0 ),

                        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 ),

                                            Treat as Hierarchy( 0 )

                                        )

            );

f2S=concat(path,dataset_name,".jmp"); // nom fichier

dt1st << save(f2S);  // save summary global

sirihofstadtrap

Community Trekker

Joined:

Feb 17, 2016

Solution

Re: Import (open) data from excel

Thank you!

It works if I use the following code:

open("C:\Users\......\Test.xlsx",

                        Worksheets( "Data" ), 

                        Use for all sheets( 1 ),

                        Concatenate Worksheets( 0 ),

                        Create Concatenation Column( 0 ),

                        Worksheet Settings(

                                            1,

                                            Has Column Headers( 1 ),

                                            Number of Rows in Headers( 2 ),

                                            Headers Start on Row( 1 ),

                                            Data Starts on Row( 3 ),

                                            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 ),

                                            Treat as Hierarchy( 0 )

                                        )

            );

Paulibis

New Contributor

Joined:

May 10, 2018

Re: Import (open) data from excel

This script works when I use it to open an excel file.  However when I update the excel file, the script does not open the updated file in jmp.  It keeps opening the original file.  How do i fix this?

markbailey

Staff

Joined:

Jun 23, 2011

Re: Import (open) data from excel

I realize that you have your answer this time but I still want to reply to this thread with a 'best practice' that we promote when we teach scripting: whenever possible, first complete the task interactively. Why? First of all, it will test the feasibility of your approach. In this case, can JMP import this particular spreadsheet? Is the spreadsheet ready to be imported? Does JMP require any changes to the default settings? Second, JMP will save the correct script for this step as the Source table script, which you can simply copy from the data table and paste into your script editor. Easy! And most importantly, you know that it works.

Even if you have performed a similar task many times in the past, rarely are two situations exactly the same, so give a try 'by hand' before you start to write any code.

Learn it once, use it forever!
Paulibis

New Contributor

Joined:

May 10, 2018

Re: Import (open) data from excel

This script works when I use it to open an excel file.  However when I update the excel file, the script does not open the updated file in jmp.  It keeps opening the original file.  How do i fix this? Help?