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

[#1]

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

[#2 · Accepted 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]

Thanks this was very helpful

[#4]

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