cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
gow
gow
Level I

Open all txt files in a folder, concat the data in to one file with source name

I am trying to open set of text files from a folder and concatinate the data in to one file, with name of txt file as one of the column.

I have attached code which opens all the text files in a selected folder. However, i am not able to create the column with file name.

can anyane help in script for isereting data (values) in to a column of a data table.

thaks

gowri

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Open all txt files in a folder, concat the data in to one file with source name

This should work (edided excerpt from your script)

name = Left( y[1], Length( y[1] ) - 4 );

Open(

  "C:jmp\" || y[i],

  columns( Acct num = Character, . = Omit ),

  Import Settings(

  End Of Line( CRLF, CR, LF ),

  End Of Field( Comma ),

  Strip Quotes( 1 ),

  Use Apostrophe as Quotation Mark( 0 ),

  Scan Whole File( 1 ),

  Labels( 0 ),

  Column Names Start( 1 ),

  Data Starts( 1 ),

  Lines To Read( All ),

  Year Rule( "10-90" )

  )

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

Only change is the placement of and the code inside New Column().

But you can also use the "Create source column" option of Concatenate() instead of adding a new column to each table.

Try the script below. an alternative approach to this problem that also lets the built-in functionalty of Concatenate() handle the source column.

mypath = "C:\jmp";

y = Files In Directory( mypath );

//remove all non-text files from y

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

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

  ,

  Remove From( y, i )

  )

);

n = N Items( y );

//Make list y1 of open() expressions

y1 = {};

For( i = 1, i <= n, i++,

  y1[i] = Parse(

  Eval Insert(

  "Open(mypath||\!"^y^\!",columns(Acct num = Character, . = Omit ),

  Import Settings(

  End Of Line( CRLF, CR, LF ),

  End Of Field( Comma ),

  Strip Quotes( 1 ),

  Use Apostrophe as Quotation Mark( 0 ),

  Scan Whole File( 1 ),

  Labels( 0 ),

  Column Names Start( 1 ),

  Data Starts( 1 ),

  Lines To Read( All )

  ))"

  )

  )

);

// (Open and) Concatenate files

Eval( y1[1] ) << Concatenate(

  Eval List( y1[2 :: n] ),

  Create source column

);

//Close sub-files

For( i = 1, i <= n, i++,

  Close( Data Table( y[i] ), nosave )

);


View solution in original post

3 REPLIES 3
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Open all txt files in a folder, concat the data in to one file with source name

This should work (edided excerpt from your script)

name = Left( y[1], Length( y[1] ) - 4 );

Open(

  "C:jmp\" || y[i],

  columns( Acct num = Character, . = Omit ),

  Import Settings(

  End Of Line( CRLF, CR, LF ),

  End Of Field( Comma ),

  Strip Quotes( 1 ),

  Use Apostrophe as Quotation Mark( 0 ),

  Scan Whole File( 1 ),

  Labels( 0 ),

  Column Names Start( 1 ),

  Data Starts( 1 ),

  Lines To Read( All ),

  Year Rule( "10-90" )

  )

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

Only change is the placement of and the code inside New Column().

But you can also use the "Create source column" option of Concatenate() instead of adding a new column to each table.

Try the script below. an alternative approach to this problem that also lets the built-in functionalty of Concatenate() handle the source column.

mypath = "C:\jmp";

y = Files In Directory( mypath );

//remove all non-text files from y

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

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

  ,

  Remove From( y, i )

  )

);

n = N Items( y );

//Make list y1 of open() expressions

y1 = {};

For( i = 1, i <= n, i++,

  y1[i] = Parse(

  Eval Insert(

  "Open(mypath||\!"^y^\!",columns(Acct num = Character, . = Omit ),

  Import Settings(

  End Of Line( CRLF, CR, LF ),

  End Of Field( Comma ),

  Strip Quotes( 1 ),

  Use Apostrophe as Quotation Mark( 0 ),

  Scan Whole File( 1 ),

  Labels( 0 ),

  Column Names Start( 1 ),

  Data Starts( 1 ),

  Lines To Read( All )

  ))"

  )

  )

);

// (Open and) Concatenate files

Eval( y1[1] ) << Concatenate(

  Eval List( y1[2 :: n] ),

  Create source column

);

//Close sub-files

For( i = 1, i <= n, i++,

  Close( Data Table( y[i] ), nosave )

);


gow
gow
Level I

Re: Open all txt files in a folder, concat the data in to one file with source name

Thanks this was very helpful

jorgeramosdealm
Level III

Re: Open all txt files in a folder, concat the data in to one file with source name

You can also do it with this script that is an alteration from

//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

In order to incorporate the source file name.

Also a thread here

I also incorporated some things from MS answer. MS, I was unable to use your script to finalize the concatenation.


mypath = "C:\jmp\";


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" );