Subscribe Bookmark RSS Feed

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

gow

Community Trekker

Joined:

May 8, 2012

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
Solution

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 )

);


3 REPLIES
Solution

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

Community Trekker

Joined:

May 8, 2012

Thanks this was very helpful

jorgeramosdealm

Community Trekker

Joined:

Feb 20, 2012

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