I have about 95 csv files with different column names saved under main data folder and 95 different sub-folders like \data\dataset1\dataset1.csv, \data\dataset2\dataset2.csv, etc. id1 and id2 columns are common variables across all files. I want to to do the following:
- Import all 95 csv files to JMP (using JMP Pro 16)
- Update a value based on a condition across all 95 files: if (id1 = 'a001' then id2='b001')
- Save updated csv files under different names in respective folders
First row is the file title and can't be removed. Column/variable names start at row 2 and data on row 3.
I managed to import all files by:
dt: Multiple File Import(
<<Set Folder(
"C:\Data\"
),
<<Set Show Hidden( 0 ),
<<Set Subfolders( 1 ),
<<Set Name Filter( "*.csv" ),
<<Set Name Enable( 1 ),
<<Set Size Filter( {522, 2096152} ),
<<Set Size Enable( 0 ),
<<Set Date Filter( {3715322235, 3718948571} ),
<<Set Date Enable( 0 ),
<<Set Add File Name Column( 0 ),
<<Set Add File Size Column( 0 ),
<<Set Add File Date Column( 0 ),
<<Set Import Mode( "CSVData" ),
<<Set Charset( "Best Guess" ),
<<Set Stack Mode( "Table Per File" ),
<<Set CSV Has Headers( 1 ),
<<Set CSV Allow Numeric( 1 ),
<<Set CSV First Header Line( 2 ),
<<Set CSV Number Of Header Lines( 1 ),
<<Set CSV First Data Line( 3 ),
<<Set CSV EOF Comma( 1 ),
<<Set CSV EOF Tab( 0 ),
<<Set CSV EOF Space( 0 ),
<<Set CSV EOF Spaces( 0 ),
<<Set CSV EOF Other( "" ),
<<Set CSV EOL CRLF( 1 ),
<<Set CSV EOL CR( 1 ),
<<Set CSV EOL LF( 1 ),
<<Set CSV EOL Semicolon( 0 ),
<<Set CSV EOL Other( "" ),
<<Set CSV Quote( "\!"" ),
<<Set CSV Escape( "" ),
<<Set XML Method( "guess" ),
<<Set XML Guess( "huge" ),
<<Set XML Settings( XML Settings() ),
<<Set JSON Method( "guess" ),
<<Set JSON Guess( "huge" ),
<<Set JSON Settings( JSON Settings() ),
<<Set Import Callback( Empty() )
) << Import Data;
In this code, there is no formula for where column names and data start.
Then, I tried to update a value by using:
dt: subjid <<
:id1 << Set Formula(
If( :id2 == "a001", :id1 == "b001")
);
result, values prior to "a001" in id1 column are deleted and id1 value of "a001" is not updated.
I am stock in this phase and can't even think of next stage of saving files under different names in their respective folders. Any help/guidance is greatly appreciated.
Thank you