Subscribe Bookmark RSS Feed

Import (open) data from excel

sirihofstadtrap

Community Trekker

Joined:

Feb 17, 2016

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

3 REPLIES
r23426

Community Trekker

Joined:

Jul 3, 2014

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

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 )

                                        )

            );

markbailey

Staff

Joined:

Jun 23, 2011

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.