Multiple File Import Auto-Naming Data Table

[#1]

Hello All,

I'm writing a script where I start with a 'multiple file import' of a folder of .csv files. I will periodically be getting new .csv files that I would like to add. These files have a series of letters and numbers (i.e chghistory0857-786.csv, chghistory0956-783.csv, chghistory1008-805.csv, etc). The issue I'm running into is when I run the 'multiple file import' script, JMP creates a data table name that is auto named based on the .csv file names (i.e. chghistory0956-783_chghistory1008-805). When I put more data files into the folder, this name changes and messes up all future formulas.

Is there a way to pre-establish the data table name and have the data from the MFI dumped into it? JMP16 user here if that makes a difference.

Thank you,

Greg 

[#2 · Accepted solution]

You bet. Multiple File Import returns a reference to the table that's created. You can use that to change the name, e.g.,

tblRef = Multiple File Import(...

);

tblRef << Set Name("My New Name");

You could work directly with the reference variable. This way you don't have to worry about what the table is named.

[#3]

Thank you! This worked great, I will also experiment with using reference variables but this was a quick fix.

[#4]

In Addition to @DonMcCormack you probably need to check what type of data you get back. In this case it is a list.

It's always better to work with specific references directly from the command before, because static names can be problematic. See Screenshot, as data table "Animals" already existed, the last import created "Animals 2".

Scripting Index is great to see how this works, even you can test some variations.

[#5]

MFI always returns a { list of tables } even if there is only a single table. In @DonMcCormack  answer, the <<setName is sent to every table in the list. You might want to (1) check that the list has exactly one item and (2) pull the item out of the list.

if( nitems(tblRef) != 1, throw("Bummer: MFI did not return exactly one table. The CSV files are not importing as expected.") );
tblRef = tblRef[1];
tblRef << Set Name( ... )

If you get that throw in a couple of years, it might only take an hour to fix rather than a day to track down why it no longer works.

[#6]

@DonMcCormack ,

This method works to a point.  When I do this, I can't use "Close(tblRef, NoSave)" to close the table.  Can you?  My method is to set the reference after the import as "tblRef = Current Data Table();".  This seems to be a bug in JMP 16.2 and JMP 17.0.0.

Thoughts?

Martin

[#7]

Take a look at @Craige_Hales post above. You may have to use something like Close(tblRef[1],No Save) (or index the tables from the list that Multiple File Import generates). If this doesn't work, I'd be interested to know the circumstances.

Thanks! Don

[#8]

what if multiple files are opened instead of 1. How can you address that issue? 

Thanks in advance

[#9]

actually, no issue. You can use the described approach.

You just have to take care that the N data tables can't have all the same name:
Jmp will add numbers to make them distinct.

Same issue if you want to rename a single data table - and the name is already used for another data table.

Best practice: use te table reference to reference the data table - NOT the name.
If you want to use the name - always ask a data table for the name (after set name).

Same "issue" with column names:

Don't trust the column << set name

tblRef << set name("myname");
tblRef << get name()

[#10]

It actually did not work with me and kept prompting me with the "unrecognized message" error. 

tblRef = Multiple File Import(

<<Set Folder( "c:\Docs\" ),

<<Set Show Hidden( 0 ),

<<Set Subfolders( 0 ),

<<Set Name Filter( "*;" ),

<<Set Name Enable( 0 ),

<<Set Size Filter( {40448, 80384} ),

<<Set Size Enable( 0 ),

<<Set Date Filter( {3779236215.617, 3780310431.309} ),

<<Set Date Enable( 0 ),

<<Set Add File Name Column( 1 ),

<<Set Add File Size Column( 0 ),

<<Set Add File Date Column( 1 ),

<<Set Import Mode( "CSVData" ),

<<Set Charset( "Best Guess" ),

<<Set Stack Mode( "Stack Similar" ),

<<Set CSV Has Headers( 1 ),

<<Set CSV Allow Numeric( 1 ),

<<Set CSV First Header Line( 1 ),

<<Set CSV Number Of Header Lines( 1 ),

<<Set CSV First Data Line( 2 ),

<<Set CSV EOF Comma( 1 ),

<<Set CSV EOF Tab( 0 ),

<<Set CSV EOF Space( 0 ),

<<Set CSV EOF Spaces( 0 ),

<<Set CSV EOF Other( "" ),

<<Set CSV EOL CRLF( 1 ),

<<Set CSV EOL CR( 1 ),

<<Set CSV EOL LF( 1 ),

<<Set CSV EOL Semicolon( 0 ),

<<Set CSV EOL Other( "" ),

<<Set CSV Quote( "\!"" ),

<<Set CSV Escape( "" ),

<<Set XML Method( "guess" ),

<<Set XML Guess( "huge" ),

<<Set XML Settings( XML Settings() ),

<<Set JSON Method( "guess" ),

<<Set JSON Guess( "huge" ),

<<Set JSON Settings( JSON Settings() ),

<<Set PDF Method( "guess" ),

<<Set PDF Settings( PDF All Tables( Combine( All ) ) ),

<<Set Import Callback( Empty() )

)  << Import Data

tblRef << Set Name("Test");