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
shs
shs
Level III

Export data table to Excel using scripting

Dear community members,

 

I encountered a problem with the JMP EXCEL export to xlsx files and the reimport to Filemaker PRO. Saving a JMP data table as an xlsx file and trying to import it FileMaker results in the correct number of imported rows and columns, but no data is being transfered. Open the xlsx file in EXCEL and saving the file again in excel solves the problem.

 

Any suggestions what could cause this effect?

 

Help is much appreciated.

 

Kind regards, Simon

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Byron_JMP
Staff

Re: Export data table to Excel using scripting

Ok, so maybe, if you're not Python averse, there might be a relatively simple thing to try. We could have JMP call a short Python script that treats the txt file as an object and replaces all the periods with commas. 

#this is python script not JSL
f = open('/Users/path/Byron Wingerd/Jupyter/Data/intable.txt','r') filedata = f.read() f.close() newdata = filedata.replace(".",",") f = open('/Users/path/Byron Wingerd/Jupyter/Data/outtable.txt','w') f.write(newdata) f.close()

We could even wrap the python script in some JSL to make this a little more automatic.

//bw
names default to here(1);
text="\[
f = open('/Users/path/Byron Wingerd/Jupyter/Data/intable.txt','r')
filedata = f.read()
f.close()
newdata = filedata.replace(".",",")
f = open('/Users/path/Byron Wingerd/Jupyter/Data/outtable.txt','w')
f.write(newdata)
f.close()
]\";

path=Get Current Directory();
save text file(path||"replace_periods_with_commas.py", text);

x = RunProgram(
    executable( "/Users/path/anaconda/bin/python" ),
    options(path||"replace_periods_with_commas.py")
    ,readfunction("blob")
 
);

Then if you really wanted to you could pull apart that "text" string that has the python script and assemble specific strings like the path to your file and things like that.  (but I'd need another cup of coffee this morning before I got that far)

JMP Systems Engineer, Health and Life Sciences (Pharma)

View solution in original post

7 REPLIES 7
Byron_JMP
Staff

Re: Export data table to Excel using scripting

Instead of exporting as xlsx, try using the older xls format?

JMP Systems Engineer, Health and Life Sciences (Pharma)
shs
shs
Level III

Re: Export data table to Excel using scripting

Thanks for the answer. I tried, but Filemaker does not support xls any more.

 

txt works. But unfortunately being forced to use decimal "," results in JMP putting quotation marks at the beginning and end of every number.

 

 

Byron_JMP
Staff

Re: Export data table to Excel using scripting

Just to make sure I uunderstand.  if you could get a .txt file where all the "." were "," then it would work with FM?

Also, are you on a Mac?

 

JMP Systems Engineer, Health and Life Sciences (Pharma)
shs
shs
Level III

Re: Export data table to Excel using scripting

Yes that would solve the issue.

Byron_JMP
Staff

Re: Export data table to Excel using scripting

Ok, so maybe, if you're not Python averse, there might be a relatively simple thing to try. We could have JMP call a short Python script that treats the txt file as an object and replaces all the periods with commas. 

#this is python script not JSL
f = open('/Users/path/Byron Wingerd/Jupyter/Data/intable.txt','r') filedata = f.read() f.close() newdata = filedata.replace(".",",") f = open('/Users/path/Byron Wingerd/Jupyter/Data/outtable.txt','w') f.write(newdata) f.close()

We could even wrap the python script in some JSL to make this a little more automatic.

//bw
names default to here(1);
text="\[
f = open('/Users/path/Byron Wingerd/Jupyter/Data/intable.txt','r')
filedata = f.read()
f.close()
newdata = filedata.replace(".",",")
f = open('/Users/path/Byron Wingerd/Jupyter/Data/outtable.txt','w')
f.write(newdata)
f.close()
]\";

path=Get Current Directory();
save text file(path||"replace_periods_with_commas.py", text);

x = RunProgram(
    executable( "/Users/path/anaconda/bin/python" ),
    options(path||"replace_periods_with_commas.py")
    ,readfunction("blob")
 
);

Then if you really wanted to you could pull apart that "text" string that has the python script and assemble specific strings like the path to your file and things like that.  (but I'd need another cup of coffee this morning before I got that far)

JMP Systems Engineer, Health and Life Sciences (Pharma)
shs
shs
Level III

Re: Export data table to Excel using scripting

Thank you very much. That is a fantastic help for me.

 

Regards.

Simon

Byron_JMP
Staff

Re: Export data table to Excel using scripting

there might be one other really direct and potentially very simple way to get a table back to your data base.

 

Take a look at this white paper about using ODBC connections between JMP and a database. The last example talks about pushing a table from JMP directly into a database connection. That might be a lot more robust, not to mention easier, that saving and opening a file.

https://www.jmp.com/content/dam/jmp/documents/en/white-papers/wp-jmp-odbc-102427.pdf

 

JMP Systems Engineer, Health and Life Sciences (Pharma)