Open Multiple Data Files Recursively

[#1]

I would like to find some code that will open files in a Windows directory recursively.

Let say my path is:  c:\charlie\data

I want JMP to find all the *.txt files in this and lower directories and open them all.

Ultimately I will merge them but for now I just want to open them all.

I have tried (with no luck):

path = "C:\charlie\data\data1";

file_list = Files in Directory("C:\charlie\data\data1");

for (i=1, i<=NItems(file_list), i++,

dt =

    Open(

        path || file_list,

   Import Settings(

        End Of Line( CRLF, CR, LF ),

        End Of Field( Comma, Other( "0x7c" ) ),

        Strip Quotes( 1 ),

        Use Apostrophe as Quotation Mark( 0 ),

        Scan Whole File( 1 ),

        Treat empty columns as numeric( 0 ),

        Labels( 1 ),

        Column Names Start( 1 ),

        Data Starts( 2 ),

        Lines To Read( All ),

        Year Rule( "20xx" )

    )

    );

);

[#2]

You path variable doesn't end with a slash so the resulting filename will fail.

To make it recursive and have corect filenames:

path = "C:\charlie\data\data1\";

file_list = Files In Directory(path, recursive);

for(i = 1, i <= N Items(file_list), i++,
    print(path || file_list);
);

You can modify the print statement to your needs.

[#3]

I added the "\"

Thanks!

Now I am trying to Concatenate them all.

I found a post from MJB that was very helpfully.

He posted a way to do this but with JOIN and only with JMP files.

I added my import routine and "open with preview" options.

I needed to open the first file, then set the loop to open the other files

and concatenate them with the first.

It is still not quite working but I am close.

[#4]

I should say "from MPB".  Thanks MPB.  I have another question for you along these lines if you have time.

[#5]

CHFields,

I was doing something similar, where I wanted to open only one file at a time and then concatenate it to the bottom of the previous file.

The easy method of opening all the files, and then concatenating them all at once was too messy because of the number of files I was working with.

The attached script lets you pick a dir with txt or csv files, and then it concatenates them all together, one at a time.

This helps if all the files don't fit into memory at once too.

-B

[#6 · Accepted solution]

This script works very nicely to open all the files in a directory, filter them based upon some criteria and then Concatenate all of the files.

=====================================================================================================

path = "C:\temp\data\";

file_list = {};

file_list = Files in Directory(path, recursive);

myFiles = file_list;

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

    If( Contains( file_list, ".sum" ),

        myFiles = ""

    )

);

final_files = myfiles;

///////////////////Open First File in List/////////////////////

///////additional files will be appended to this one///////////

i=1;

dtjoin = Open(

    path || final_files,

                columns(

                    Pass_Fail = Character,

                    Parameter = Character,

                    Data_Value = Numeric,

                    Units = Character,

                    LSL = Numeric,

                    USL = Numeric,

                    Note = Character,

                    Test_Cond = Character

            ),

            Import Settings(

                End Of Line( CRLF, CR, LF ),

                End Of Field( Comma, Other( "0x7c" ) ),

                Strip Quotes( 1 ),

                Use Apostrophe as Quotation Mark( 0 ),

                Scan Whole File( 1 ),

                Treat empty columns as numeric( 0 ),

                Labels( 1 ),

                Column Names Start( 1 ),

                Data Starts( 2 ),

                Lines To Read( All ),

                Year Rule( "20xx" )

            ),  

        )

        << set name( "Joined1" );

////////////////////////////////////////////////////////////////////////////

//////////Now open the remaining Files in List//////////////

////////////////////////////////////////////////////////////////////////////

i=2;

For( i = 2, i <= N Items( final_files ), i++,

    dt =

        Open(

            path || final_files,

            columns(

                Pass_Fail = Character,

                Parameter = Character,

                Data_Value = Numeric,

                Units = Character,

                LSL = Numeric,

                USL = Numeric,

                Note = Character,

                Test_Cond = Character

        ),

        Import Settings(

            End Of Line( CRLF, CR, LF ),

            End Of Field( Comma, Other( "0x7c" ) ),

            Strip Quotes( 1 ),

            Use Apostrophe as Quotation Mark( 0 ),

            Scan Whole File( 1 ),

            Treat empty columns as numeric( 0 ),

            Labels( 1 ),

            Column Names Start( 1 ),

            Data Starts( 2 ),

            Lines To Read( All ),

            Year Rule( "20xx" )

        )

        );

    dtjoin = dtjoin << Concatenate(

          dt,

          output table name( "Joined" || Char( i ) ),

        );

    Close( dt, nosave);

    Close( "Joined" || Char( i - 1 ), nosave );

    Wait( 0.1 );

); //End of For

dtjoin << set name( "All Joined" );