Subscribe Bookmark RSS Feed

Export data table to Excel using scripting

Highlighted
shs

New Contributor

Joined:

May 17, 2017

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

Joined:

Apr 26, 2012

Solution

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)

7 REPLIES
Byron_JMP

Staff

Joined:

Apr 26, 2012

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

shs

New Contributor

Joined:

May 17, 2017

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

Joined:

Apr 26, 2012

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?

 

shs

New Contributor

Joined:

May 17, 2017

Yes that would solve the issue.

Byron_JMP

Staff

Joined:

Apr 26, 2012

Solution

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)

shs

New Contributor

Joined:

May 17, 2017

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

 

Regards.

Simon

Byron_JMP

Staff

Joined:

Apr 26, 2012

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