topic Re: Import (open) data from excel in Discussions

Import (open) data from excel

sirihofstadtrap — Thu, 09 Jun 2016 09:02:53 GMT

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

Re: Import (open) data from excel

r23426 — Thu, 09 Jun 2016 17:37:58 GMT

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(

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

sirihofstadtrap — Fri, 10 Jun 2016 06:56:55 GMT

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(

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 )

)

);

Re: Import (open) data from excel

Mark_Bailey — Fri, 10 Jun 2016 15:50:50 GMT

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.

Re: Import (open) data from excel

Paulibis — Thu, 10 May 2018 17:42:51 GMT

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

Paulibis — Thu, 10 May 2018 17:45:27 GMT

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?