Create JMP datatable from C# DataTable variable

[#1]

What is the most effecient way of creating a JMP datatable from a C# DataTable variable?  I am presently using the windows clipboard to paste all the information into JMP. (C# first filling the clipboard)  This works, but Windows clipboard is not reliable and sometimes comes-up empty.

I sent a support ticket a few months back and got this messsage..

Support Message:

...........................

 Also, you can use the GetTableHandleFromIndex or GetTableHandleFromName methods to get a C# reference to DataTable automation object, as documented here.

...........................

I didn't really understand what this meant, when I read the link and was clueless as what to do?

Basically C#  JMP automation code I want to do somethign like this:

CreateJMPDatatable (DT)

where DT is a variable of type DataTable (C#)

I could try nested for loops to loop through columns and rows and have JSL populate cell by cell and create needed columns and rows, (which I will try soon) but wondering if there is a quicker and easier way to achieve the same result?   (100,000 rows and 100 columns, so I want something quick and effiecient)

Thank you,

[#2]

JMP ships with a C# sample program.  For JMP 13, this is located in C:\Program Files\SAS\JMP\13\Samples\Automation\Visual CSharp Sample.  This will show how to open a data table.  The Visual Basic automation sample for DataTable will illustrate the methods for creating a new table and populating it (just instantiate a DataTable variable and use NewColumn).  The methods are easily translated to C#.

Brian Corcoran

JMP Development

[#3]

Thanks for the link to the solution I was able to review the code fine in visual studio. BTW JMP automation runs fine in my solution, but the sample solution gave an error. I have JMP 13 and visual studio 2017 installed.


When I click any button on the GUI, I get this error:

System.BadImageFormatException occurred
HResult=0x8013101B
Message=Could not load file or assembly 'Interop.JMP, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.
Source=<Cannot evaluate the exception source>
StackTrace:
<Cannot evaluate the exception stack trace>

[#4 · Accepted solution]

Hi Chris,

I believe I was the one who helped you out a few months ago. The snippet you provided from our conversation was in reference to creating a C# DataTable object from a JMP data table, not the other way around. Below is what I said about going from C# to JMP.

There is not a direct path for converting a C# data table object to a JMP Data Table. Instead, you will need to use JMP Automation methods to create an empty table, loop through columns of the C# table, creating the JMP columns, and then loop through the rows of the C# table, adding the row/column values to the JMP Data Table.

Here is something I threw together that loops through the columns and rows to create a JMP.DataTable:

class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();
            // call method to get sample C# data table
            DataTable dt = p.GetTable();

            JMP.Application myJMP = new JMP.Application();
            myJMP.Visible = true;
            myJMP.EnableInteractiveMode(true);

            JMP.DataTable jmpDT;
            jmpDT = myJMP.NewDataTable("Example");
            Object jmpCol;
            
            // add each column to the JMP table
            foreach (DataColumn col in dt.Columns)
            {
                // if its an integer, create numeric column (you may need to check for other Types, such as Decimal or DateTime)
                if (col.DataType == System.Type.GetType("System.Int32"))
                {
                    jmpCol = jmpDT.NewColumn(col.ColumnName, JMP.colDataTypeConstants.dtTypeNumeric, 0, 8);
                }
                else
                {
                    jmpCol = jmpDT.NewColumn(col.ColumnName, JMP.colDataTypeConstants.dtTypeCharacter, 0, 8);
                }
            }
            
            // add rows to the table
            jmpDT.AddRows( (Int16) dt.Rows.Count, 0 );
            int i = 1;
            foreach (DataRow row in dt.Rows)
            {
                foreach (DataColumn col in dt.Columns)
                {
                    jmpDT.GetColumn(col.ColumnName).SetCellVal(i, row[col.ColumnName].ToString());
                }
                i++;
            }

            

        }
        // From:  https://msdn.microsoft.com/en-us/library/9ha04z01(v=vs.110).aspx
        private DataTable GetTable()
        {
            // Create new DataTable.
            DataTable table = new DataTable();

            // Declare DataColumn and DataRow variables.
            DataColumn column;
            DataRow row;

            // Create new DataColumn, set DataType, ColumnName
            // and add to DataTable.    
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.Int32");
            column.ColumnName = "id";
            table.Columns.Add(column);

            // Create second column.
            column = new DataColumn();
            column.DataType = Type.GetType("System.String");
            column.ColumnName = "item";
            table.Columns.Add(column);

            // Create new DataRow objects and add to DataTable.    
            for (int i = 0; i < 10; i++)
            {
                row = table.NewRow();
                row["id"] = i;
                row["item"] = "item " + i;
                table.Rows.Add(row);
            }
            return table;
        }
    }

I hope this is helpful. Let me know if you have any further questions.

Thanks!

[#5]

Thank you for the detailed response, JMP technical support is second to none!

[#6]

Also, sorry for the question again, you essentially had answered my question some months back, my reading old email skills was not so good yesterday! Thanks again!


There is not a direct path for converting a C# data table object to a JMP Data Table. Instead, you will need to use JMP Automation methods to create an empty table, loop through columns of the C# table, creating the JMP columns, and then loop through the rows of the C# table, adding the row/column values to the JMP Data Table.

[#7 · Accepted solution]

Just an update, Justin_Chilton, while your solutions works, it was extremely slow for very large tables (and turned out to be inadequate for my needs)

It is much faster to use

JmpDT.GetColumn(ColName).SetDataVector (which populates 1 column at a time)

instead of

jmpDT.GetColumn(col.ColumnName).SetCellVal

Which only populates 1 cell at a time

So you just need code to convert data columns in datatables into array to feed into the SetDataVector method

example:  string[] ColArray = Summary.AsEnumerable().Select(rw => rw.Field<string>(ColName)).ToArray();

So with this approach,you only need to loop through columns once:

sample code:

for (int i = 0; i < Summary.Columns.Count; i++)
{
string ColName = Summary.Columns[i].ColumnName;

var datatype = Summary.Columns[i].DataType;

if (datatype==typeof(string))
{

string[] ColArray = Summary.AsEnumerable().Select(rw => rw.Field<string>(ColName)).ToArray();
JmpDT.GetColumn(ColName).SetDataVector(ColArray);


}
else if (datatype == typeof(double))
{
double[] ColArray = Summary.AsEnumerable().Select(rw => rw.Field<double>(ColName)).ToArray();
jmpDT.GetColumn(ColName).SetDataVector(ColArray);
}
else if (datatype == typeof(int))
{
int[] ColArray = Summary.AsEnumerable().Select(rw => rw.Field<int>(ColName)).ToArray();
jmpDT.GetColumn(ColName).SetDataVector(ColArray);
}

else if (datatype == typeof(float))
{
float[] ColArray = Summary.AsEnumerable().Select(rw => rw.Field<float>(ColName)).ToArray();
jmpDT.GetColumn(ColName).SetDataVector(ColArray);
}

}

While this solution works (and is orders of magnitude faster than using "SetCellVal" method, it still is almost twice as slow as pasting a tab delimted string from windows clipboard for my application.

I did time trials: ~9000 rows and 175 columns

pasting clipboard ~2.5s, while technique above ~4.5s

So I would like to request in the future a more direct method to bring data into JMP from C# datatables.

I would use the windows clipboard, but even  with error trapping, the Windows clipboard can be flakey and unreiliable on some people PC's hence would not like to use that technique.

FYI WIndows clipboard would be populated with a tab delimited string, then use this code to get data into JMP.

command = "dts << Bring Window To Front << Clear Column Selection << clear select; dts=currentdatatable();";
myJMP.RunCommand(command);
command = "main menu(\"Paste With Column Names\");";

myJMP.RunCommand(command);

It would be nice if this pasting could be done from something other than the windows clipboard. (i.e. a tab delimited string passed from a method).  Or a method that can take datatable variable directly and convert to JMP datatable.  

Thank you for your consideration,

Chris

[#8]

Hi Chris,

Thanks for the update. I didn't think about the SetDataVector method when working on the example. Good catch.

---

It would be nice if this pasting could be done from something other than the windows clipboard. (i.e. a tab delimited string passed from a method).  Or a method that can take datatable variable directly and convert to JMP datatable.  

---

If you already have the table in the form of a tab-delimited string, why not just save the string out to a file and open that file in JMP? That would remove reliance on the Windows clipboard.

Or even better, you could just set a JSL variable to the tab-delimted string and use the Open function to open the table directly from the string. Below is an example of how to do that.

tabDelimitedStr = "name	age	sex	height	weight
KATIE	12	F	59	95
LOUISE	12	F	61	123
JANE	12	F	55	74";

Open( Char to Blob( tabDelimitedStr ), "text" );

Basically, you can use a JSL variable instead of the clipboard to pass the string, and the Open function to create your table instead of the "Paste with Column Names" option.