cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
chfields
Level II

Open Multiple Data Files Recursively

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
chfields
Level II

Re: Open Multiple Data Files Recursively

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

View solution in original post

5 REPLIES 5

Re: Open Multiple Data Files Recursively

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
Level II

Re: Open Multiple Data Files Recursively

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
Level II

Re: Open Multiple Data Files Recursively

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

Byron_JMP
Staff

Re: Open Multiple Data Files Recursively

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

JMP Systems Engineer, Health and Life Sciences (Pharma)
chfields
Level II

Re: Open Multiple Data Files Recursively

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