cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar

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

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 )

                                        )

            );

View solution in original post

5 REPLIES 5
r23426
Level II

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

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
Level I

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?

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.

Paulibis
Level I

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?