Import (open) data from excel

[#1]

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

[#2]

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

[#3 · Accepted solution]

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 )

                                        )

            );

[#4]

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?

[#5]

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.

[#6]

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?