JMP Live 17: refreshable multiple file import from folder via ODBC

Jonas_Rinne

Hi everyone,

one of our customers (Bundesdruckerei) previously used the windows task sheduler to publish data into JMP Live 16 regularly.

They upgraded to JMP Live 17 and now want to do that using ODBC to import multiple files from a folder on the server.

Here is an old script they provided us for reference:

current_day = Short Date( Today() );
current_week = Week of Year( Today(), 3 );
last_week = current_week - 1;
current_year = Year( Today() );

start_date = Informat( current_day ) - ( In Days (28));
start_date_pentaho = start_date - ( In Days (10));
end_date = Informat( current_day );

//daten import with only csv files
//create a dynamic request for the year folder
dt_stationlogs = Multiple File Import(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Folder(&nbsp;\\scisilon-cifs.scp.bdr.de\reporting\stationlogs\2023\&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Show Hidden(&nbsp;0&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Subfolders(&nbsp;1&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Name Filter(&nbsp;"*.csv; "&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Name Enable(&nbsp;1&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Size Filter( {54,&nbsp;65323065} ),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Size Enable(&nbsp;0&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Date Filter( {start_date,&nbsp;end_date} ),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Date Enable(&nbsp;1&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Add File Name Column(&nbsp;1&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Add File Size Column(&nbsp;0&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Add File Date Column(&nbsp;0&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Import Mode(&nbsp;"CSVData"&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Charset(&nbsp;"Best Guess"&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Stack Mode(&nbsp;"Stack Similar"&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV Has Headers(&nbsp;1&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV Allow Numeric(&nbsp;1&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV First Header Line(&nbsp;1&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV Number Of Header Lines(&nbsp;1&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV First Data Line(&nbsp;2&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV EOF Comma(&nbsp;0&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV EOF Tab(&nbsp;0&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV EOF Space(&nbsp;0&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV EOF Spaces(&nbsp;0&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV EOF Other(&nbsp;";"&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV EOL CRLF(&nbsp;1&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV EOL CR(&nbsp;1&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV EOL LF(&nbsp;1&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV EOL Semicolon(&nbsp;0&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV EOL Other(&nbsp;""&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV Quote(&nbsp;"\!""&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set CSV Escape(&nbsp;""&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set XML Method(&nbsp;"guess"&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set XML Guess(&nbsp;"huge"&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set XML Settings( XML Settings() ),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set JSON Method(&nbsp;"guess"&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set JSON Guess(&nbsp;"huge"&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set JSON Settings( JSON Settings() ),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <<Set Import Callback(&nbsp;Empty() )
) <<&nbsp;Import Data;

Has someone experience or an idea how to set this up with the JMP Live 17 sheduler? The set folder command does not work for them so far. 

Many thanks,

Jonas

Jordan_Hiller

Hi Jonas,

The server-side refresh in JMP Live 17 only works with a database query (ODBC) or API call. The multiple file import in your customer's example is not yet supported.

Here's an example with a database query that refreshes on a schedule: https://community.jmp.com/t5/JMP-SE-Knowledge-Base/JMP-Live-17-Refresh-Demo/ta-p/563123

Jordan