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

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

gow — Wed, 09 May 2012 19:29:31 GMT

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

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

ms — Wed, 09 May 2012 22:21:53 GMT

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 )

);

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

gow — Thu, 10 May 2012 12:28:20 GMT

Thanks this was very helpful

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

jorgeramosdealm — Wed, 25 Jul 2012 12:53:11 GMT

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