topic Re: Concatenating large number of files in Discussions

Concatenating large number of files

Mon, 22 Mar 2010 17:12:20 GMT

Hi Guys,

I am trying to concatenate several files in a folder as below:

Re: Concatenating large number of files

mattf — Mon, 22 Mar 2010 19:51:34 GMT

See:
Jump into JMP Scripting
By: Wendy Murphrey and Rosemary Lucas
// http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=61733
Page 122, Soultion 5.16

Set Current Directory( "C:\temp2" );
myFiles = Files In Directory( "C:\temp2" );
For( i = 1, i <= N Items( myFiles ), i++,
/* If the file is the first in the list, open it. Otherwise, open the table, concatenate with the main table, and close the table just opened. */
If( i == 1,
mainDt = Open( "C:\temp2\" || myFiles ),
dt = Open( "C:\temp2\" || myFiles );
mainDt = mainDt << Concatenate( dt, Append to First Table );
Close( dt, NoSave );
Wait( 0 );
)
);
/* Give the final table a name. */
mainDt << Set Name( "Concatenated Files" );

Best regards,
-Matt

Re: Concatenating large number of files

mattf — Mon, 22 Mar 2010 19:53:41 GMT

Re: Concatenating large number of files

Mon, 22 Mar 2010 20:38:19 GMT

Thanks. Can you please use the following to post your code?

Re: Concatenating large number of files

mpb — Mon, 22 Mar 2010 22:46:38 GMT

You may find this trick useful:

After logging in, "reply" to the posting and "Quote Original". You will the have the complete code which you can copy / paste into a JMP script window. Then remove the first 2 characters on each line ("> ") to get the code as it was intended. Then cancel the reply.

Re: Concatenating large number of files

Mon, 22 Mar 2010 23:33:44 GMT

Thanks Matt, that worked. I will take a look at the book.

Thanks, mpb for the tip.

Re: Concatenating large number of files

mattf — Tue, 23 Mar 2010 13:21:10 GMT

Re: Concatenating large number of files

jorgeramosdealm — Wed, 25 Jul 2012 13:13:24 GMT

I made a variation of that code that incorporates the Source table name for each file. In this case it takes all the txt files but you can also do it with csv or other format.


mypath ="C:\temp\";

Set Current Directory( mypath );  

y = Files In Directory( mypath );  

For( i = N Items( y ), i > 0, i--, 

  If( Ends With( y[i], "txt" ), 

  , 

  Remove From( y, i ) 

  ) 

); 

n = N Items( y ); 

For( i = 1, i <= N Items( y ), i++, 

  sname=left(y[i],length(y[i])-4); 

/* If the file is the first in the list, open it. Otherwise, open the table, concatenate with the main table, and close the table just opened. */  

    If( i == 1, 

        mainDt = Open( mypath || y[i] ); 

        mainDt << New Column ("source", character, set each value ( sname )), 

        dt = Open( mypath || y[i] ) ; 

        dt << New Column ("source", character, set each value ( sname )); 

        mainDt = mainDt << Concatenate( dt, Append to First Table ); 

        Close( dt, NoSave ); 

        Wait( 0 ); 

    ) 

  );    

/* Give the final table a name. */  

mainDt<< Set Name("Concatenated Files");

Re: Concatenating large number of files

mjvincent87 — Wed, 25 Mar 2020 14:16:08 GMT

I am hoping to find out where I am going wrong with this one. The debugger won't start but I have about 20,000 files, could that be the problem?

Set Current Directory( mypath );  


y = Files In Directory( mypath );  


For( i = N Items( y ), i > 0, i--, 


  If( Ends With( y[i], "csv" ), 


  , 


  Remove From( y, i ) 


  ) 


); 


n = N Items( y ); 


For( i = 1, i <= N Items( y ), i++, 


  sname=left(y[i],length(y[i])-4); 


/* If the file is the first in the list, open it. Otherwise, open the table, concatenate with the main table, and close the table just opened. */  


    If( i == 1, 


        mainDt = Open( mypath || y[i] ); 


        mainDt << New Column ("source", character, set each value ( sname )), 


        dt = Open( mypath || y[i] ) ; 


        dt << New Column ("source", character, set each value ( sname )); 


        mainDt = mainDt << Concatenate( dt, Append to First Table ); 


        Close( dt, NoSave ); 


        Wait( 0 ); 


    ) 


  );    


/* Give the final table a name. */  


mainDt<< Set Name("Terminal");

Re: Concatenating large number of files

stan_koprowski — Wed, 25 Mar 2020 16:12:06 GMT

hi @mjvincent87

The original post is several years old and some better options exist for importing multiple files from a directory.

Have you seen the multiple file import that was introduced in JMP 14?

The new multiple file import platform launches a dialog to load several files into a single JMP data table. In addition, it allows filtering of files by size, date, name and type and supports one-column import, useful for Text Explorer document preparation. Also the platform offers support for editing text import options.

cheers,

Stan

Re: Concatenating large number of files

mjvincent87 — Wed, 25 Mar 2020 16:22:28 GMT

That sounds great, unfortunately I am using JMP13

Re: Concatenating large number of files

Craige_Hales — Wed, 25 Mar 2020 22:57:33 GMT

There is a JSL error, I think, here

If( Ends With( y[i], "csv" ), 
 0 // need something here, a zero is good enough
, 
  Remove From( y, i ) 
)

the two adjacent commas need something between them. A 0 is a very tiny expression that evaluates to 0 and does nothing else. (Or switch the logic around with !EndsWith(...) where ! means NOT.)