Subscribe Bookmark RSS Feed

Open Multiple Data Files Recursively

chfields

Community Trekker

Joined:

Apr 2, 2013

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

    )

    );

);

1 ACCEPTED SOLUTION

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

5 REPLIES
asdfasdf

Community Trekker

Joined:

Aug 1, 2012

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.

chfields

Community Trekker

Joined:

Apr 2, 2013

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.

chfields

Community Trekker

Joined:

Apr 2, 2013

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

Byron_JMP

Staff

Joined:

Apr 26, 2012

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

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