Export data table to Excel using scripting

[#1]

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

[#2]

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

[#3]

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.

[#4]

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?

[#5]

Yes that would solve the issue.

[#6 · Accepted 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]

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

Regards.

Simon

[#8]

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