Subscribe Bookmark RSS Feed

How could I save multiple open .jmp data tables as .csv files?

ana_v

Community Trekker

Joined:

Oct 6, 2015

As a result of table subset I have multiple .jmp data tables that I need to save as .csv files. I have tried a script for saving all opened tables but I only can make it work for saving as .jmp files.

openDTs=List ();For (i=1, i<=N Table (), i++, Insert Into (openDTs, Data Table (i)););

For(i=1, i<=N Items(openDTs),i++,openDTS<<Save(););

From the subset operation I will have a large number of tables opened so maybe it would be better to avoid having that many tables opened and try to incorporate the saving procedure after the subset operation. I am very new to scripting to any ideas are very much appreciated.

6 REPLIES
mdawson69

Community Trekker

Joined:

Aug 26, 2015

Ideally, this would be a simple single line fix, but looking into this, that does not appear to be the case. Save and Save As specifically save a JMP data table, as beyond the path/filename, there are no additional parameters for those functions, such as file type. There is another file output function, Save Text File, but it only outputs a specific string to a text file. Fortunately, I have considerable programming experience, so the method for accomplishing this task was rather straightforward to me after giving it some thought, as most programming languages would create a CSV file in this way.

Below is a script that accomplishes what you want, you would just need to adapt it to your data table; you would need to specify your particular columns in your data table. What this script does is first determine the number of rows in the data table. Then a For loop is used to capture data from specific columns in each row in the data table and write it to a text file. On the first row, a new file is created or an existing file is overwritten. For all remaining rows, data is appended to the existing file. Also, so you do not wind up with a file that is just one continuous line of data, line feeds are added to all but the last row in the file.

The one advantage of having to write text files in this way is that you can choose which columns to export if you only want certain data. Obviously, this process is tedious if you want all of your data and you have several columns, but as I mentioned earlier, this technique is typical in most programming languages.

Names Default to Here(1);

dt = Current Data Table();

lastRow = N Rows();

For(i = 1, i <= lastRow, i++,

    //get columns

    location = :Location[i];

    day = :Day[i];

    time = :Time[i];

    count = :Count[i];

    mean = :Mean[i];

    max = :Max[i];

    //create comma-delimited row

    csvRow = Concat Items(

        {location, Char(day), Char(time), Char(count), Char(mean), Char(max)},

        ","

    );

    //save row to text file

    If(i == 1,

        Save Text File("$DESKTOP/Test File.csv", csvRow, Mode("Replace")),

        Save Text File("$DESKTOP/Test File.csv", csvRow, Mode("Append"))

    );

    //export line feed for all but last row

    If(i != lastRow, Save Text File("$DESKTOP/Test File.csv", "\!n", Mode("Append")));

);

ana_v

Community Trekker

Joined:

Oct 6, 2015

Thanks a lot for the script. I tried it and worked on one of the .jmp files. The problem now is that I have more than 400 jmp tables that I would need to save as .csv files with the same name as the original corresponding jmp tables. I can't figure out a way to do this.

pmroz

Super User

Joined:

Jun 23, 2011

dt1 = open("$sample_data\Big Class.jmp");

dt2 = open("$sample_data\Bands Data.jmp");

dt3 = open("$sample_data\Auto Raw Data.jmp");

dt4 = open("$sample_data\Fitness.jmp");

openDTs = {};

For( i = 1, i <= N Table(), i++,

     Insert Into( openDTs, Data Table( i ) )

);

file_dir = "c:\temp\";

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

     one_name = openDTS[i] << get name;

     file_path = file_dir || one_name || ".csv";

     openDTS[i] << Save(file_path);

);

mdawson69

Community Trekker

Joined:

Aug 26, 2015

Neat. PMroz's script works perfectly if you want to capture all of the data from each data table indiscriminately. The script I provided gives you a little more control because you can select which columns are written out; it can also be tweaked to select specific rows or even specific cells if need be. You should be able to insert code from script into PMroz's script for selecting multiple data tables if you need that level of control.

ana_v

Community Trekker

Joined:

Oct 6, 2015

Thank you very much. That script is what I was looking for!

pmroz

Super User

Joined:

Jun 23, 2011

If you provide a path that has a .csv extension, it will be saved as a csv file.

dt = New Table( "CSV Example",  Add Rows( 3 ),

     New Column( "Column 1", Numeric, Continuous, Format( "Best", 12 ), Set Values( [1, 2, 3] ) ),

     New Column( "Column 2", Character, Nominal, Set Values( {"a", "b", "c"} ) )

);

fp = "c:\temp\csv_example.csv";

dt << save(fp);