Make IMF function more efficient.

[#1]

I have thousands of file like the attached example (.CSV). Time information is in raw 102 and my measurement data start at raw 242.

Currently I am doing Importing file 2 times, one for Time information and one for test data. After that I use update function to import time information to test data table. 

This logic was working with small set of files but it is not efficient on large number of files as IMF for time information is pulling all data after raw 102. 

Is there any way I can set last data line in IMF function for time information import? So I don't import extra 141 rows/file.

[#2]

To my knowledge you cannot control that with Multiple File Import. Have you tried importing all the data and then cleaning the resulting table in JMP? Or optionally if you are just trying to export few lines from the .csv, you could do with with JSL and Load Text File

[#3]

Hi Jarmo,

I used JSL and below is the script I came up with. It is working fine but now my data set is getting bigger with more testing and this logic is not efficient (particularly importing Time table as I don't know how to only import one row). 

Names Default To Here( 1 );

dir=Pick Directory( "Select Data Directory");


// Import multiple files
Time=Multiple File Import(
	<<Set Folder( dir ),
	<<Set Show Hidden( 0 ),
	<<Set Subfolders( 1 ),
	<<Set Name Filter( "*.csv;" ),
	<<Set Name Enable( 1 ),
	<<Set Size Filter( {16380, 16468} ),
	<<Set Size Enable( 0 ),
	<<Set Date Filter( {3821780313.688, 3821780834.508} ),
	<<Set Date Enable( 0 ),
	<<Set Add File Name Column( 1 ),
	<<Set Add File Size Column( 0 ),
	<<Set Add File Date Column( 0 ),
	<<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( 111 ),
	<<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 Excel Best Guess( 1 ),
	<<Set Excel Worksheet Filter( "" ),
	<<Set Excel Has Headers( 1 ),
	<<Set Excel First Header Line( 1 ),
	<<Set Excel Number of Header Lines( 1 ),
	<<Set Excel First Data Line( 2 ),
	<<Set Excel First Data Column( 1 ),
	<<Set Excel Limit Column Type Detection( 0 ),
	<<Set Excel Column Headers As Hierarchies( 0 ),
	<<Set Excel Replicate Data In Spanned Rows( 1 ),
	<<Set Excel Suppress Hidden Rows( 1 ),
	<<Set Excel Suppress Hidden Columns( 1 ),
	<<Set Excel Suppress Empty Columns( 1 ),
	<<Set Excel Last Data Row( . ),
	<<Set Excel Last Data Column( . ),
	<<Set Excel Column Name Separator( "-" ),
	<<Set Excel Multiple Series Stack( 0 ),
	<<Set Excel Replicate Headers In Spanned Rows( 0 ),
	<<Set Excel Import Color Cells( 0 ),
	<<Set Excel Add Sheet Name Column( 0 ),
	<<Set Import Callback( Empty() )
) << Import Data;

Time << Set Name("Time");


//Import multiple files
Test Data=Multiple File Import(
	<<Set Folder(dir),
	<<Set Show Hidden( 0 ),
	<<Set Subfolders( 1 ),
	<<Set Name Filter( "*.csv;" ),
	<<Set Name Enable( 1 ),
	<<Set Size Filter( {16425, 16440} ),
	<<Set Size Enable( 0 ),
	<<Set Date Filter( {3683707407.356, 3683708516.017} ),
	<<Set Date Enable( 0 ),
	<<Set Add File Name Column( 1 ),
	<<Set Add File Size Column( 0 ),
	<<Set Add File Date Column( 0 ),
	<<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( 252 ),
	<<Set CSV Number Of Header Lines( 1 ),
	<<Set CSV First Data Line( 253 ),
	<<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;
Test Data << Set Name("Test Data");

// Delete selected rows
Data Table( "Time" ) << Select Where( :Column 2 == " TestRecord.RecordTime" ) << Invert Row Selection << Delete Rows;



// Delete Empty column from both tables

dt1 = data table("Test Data");
dt2 = data table("Time");

For( i = N Col( dt1 ), i >= 1, i--,

       If( (Col N Missing( Column( i ) ) / N Row()) == 1,

              Data Table( dt1 ) << delete columns( i )

       )

);

For( i = N Col( dt2 ), i >= 1, i--,

       If( (Col N Missing( Column( i ) ) / N Row()) == 1,

              Data Table( dt2 ) << delete columns( i )

       )

);

Data Table( dt1 ) :File Name<< Set Display Width( 300 );
Data Table( "Time" ):Column 3 << Set Name( "Test Time" );

// Update data table
Data Table( "Test Data" ) << Update(
	With( Data Table( "Time" ) ),
	Match Columns( :File Name = :File Name ),
	Add Columns from Update Table( :Test Time )
);

// Close Data Table: Time
Close( Data Table( "Time" ), NoSave );

[#4]

How many files do you have? One import should be enough and then you can just clean your data accordingly within JMP.

[#5]

Currently, I have around 20,000 files, and each day, 1,000 to 1,500 new files are added. The script takes about 7 minutes and 30 seconds to run. Since the time table has unnecessary rows, it ends up importing 2.8 million rows before cleaning them up.

[#6]

Are you just importing the new files or the whole folder with increasing amount of files?