cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Check out the JMP® Marketplace featured Capability Explorer add-in
%3CLINGO-SUB%20id%3D%22lingo-sub-798399%22%20slang%3D%22en-US%22%20mode%3D%22UPDATE%22%3EJ'ai%20pu%20invoquer%20des%20fichiers%20csv%20et%20les%20concat%C3%A9ner%20dans%20une%20table%20JMP.%20J'ai%20ajout%C3%A9%20quelques%20modifications%20(modification%20des%20noms%20de%20colonnes%2C%20ajout%20de%20noms%20de%20colonnes%2C%20etc.).%20Je%20souhaite%20invoquer%20un%20autre%20fichier%20csv%20d%C3%A9fini%20par%20l'utilisateur%20comme%20une%20autre%20table%20jmp%20et%20extraire%20des%20informations%20de%20celui-ci%20pour%20les%20ajouter%20%C3%A0%20la%20premi%C3%A8re%20table%20JMP%20concat%C3%A9n%C3%A9e%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798399%22%20slang%3D%22en-US%22%20mode%3D%22UPDATE%22%3E%3CDIV%3EVeuillez%20consulter%20mon%20script%20jusqu'%C3%A0%20pr%C3%A9sent.%20%C3%80%20la%20fin%20du%20script%2C%20je%20souhaite%20ouvrir%20un%20autre%20fichier%20csv%20dans%20une%20table%20jmp%20et%20extraire%20les%20colonnes%20dans%20la%20table%20jmp%20cr%C3%A9%C3%A9e%20%C3%A0%20l'origine%20%C3%A0%20partir%20de%20la%20premi%C3%A8re%20invocation.%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CPRE%3E%3CCODE%20class%3D%22%20language-jsl%22%3E%0ANames%20Default%20To%20Here(1)%3B%0A%2F%2Fdt%20%3D%20Open(%20%22C%3A%5CUsers%5CVISHAL.SANGHAI%5COneDrive%20-%20MiniCircuits%5CDocuments%5CMini%20Circuits%20Work%20Folder%5CUS-AMP%5CUS-AMP-2%5CPkg%20data%20V1%20old%20epi%20June%202024%20testing%5CPower%20with%20new%20epi%20boards%20-%20Jul%202024%5CUSAMP2_newepi_U2_VG_np465_VD_15_ID_500mA_VC_9_P5dB_biasteeoutsideTC_postdecap.csv%22%20)%3B%0A%0A%0A%2F%2Fconcatenate%20files%0A%2F%2F%20Setup%20an%20empty%20table%20to%20start%20to%20concatenate%20other%20tables%20to%0Adtbase%20%3D%20New%20Table(%22Base%20Table%22)%3B%0A%2F%2F%20Have%20the%20user%20select%20the%20starting%20folder%0Adirpath%20%3D%20Pick%20Directory(%22Pick%20the%20Top%20Level%20Directory%22)%3B%0A%2F%2F%20Get%20all%20of%20the%20files%20in%20all%20of%20the%20folders%20that%20are%20under%20the%20picked%20folder%0ATheFileList%20%3D%20Files%20In%20Directory(dirpath%2C%20%22recursive%22)%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%2F%2F%20Go%20through%20all%20of%20the%20files%20and%20read%20in%20append%20those%20that%20meet%20your%0A%2F%2F%20requirement.%20%20You%20will%20need%20to%20amend%20the%20IF%20statement%20to%20meet%20your%20criteria%0AFor(i%20%3D%201%2C%20i%20%26lt%3B%3D%20N%20Items(TheFileList)%2C%20i%2B%2B%2C%0A%09If(Uppercase(Word(-1%2C%20TheFileList%5Bi%5D%2C%20%22.%22))%20%3D%3D%20%22CSV%22%20%26amp%3B%20Is%20File(dirpath%20%7C%7C%20TheFileList%5Bi%5D)%20%3D%3D%201%2C%0A%09%09dt%20%3D%20Open(dirpath%20%7C%7C%20TheFileList%5Bi%5D)%3B%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2Fto%20add%20filename%20as%20separate%20column%20in%20datatable%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%09%09dtname%20%3D%20dt%20%26lt%3B%26lt%3B%20get%20name()%3B%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2Fto%20add%20filename%20as%20separate%20column%20in%20datatable%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%09%09dtname_list%20%3D%20Repeat(%7Bdtname%7D%2C%20N%20Row(dt))%3B%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2Fto%20add%20filename%20as%20separate%20column%20in%20datatable%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%09%09dt%20%26lt%3B%26lt%3B%20New%20Column(%22Filename%22%2C%20character%2C%20set%20values(dtname_list))%3B%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2Fto%20add%20filename%20as%20separate%20column%20in%20datatable%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%09%09dtbase%20%3D%20dtbase%20%26lt%3B%26lt%3B%20concatenate(dt%2C%20append%20to%20first%20table(1))%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%09%09Close(dt%2C%20nosave)%3B%0A%09)%0A)%3B%0A%0A%0A%2F%2F%20Add%20Global%20info%20as%20table%20variables%0A%2F%2F%20Scan%20the%20data%20table%20based%20on%20column%20and%20assign%20the%20right%20row%0AtheRow%20%3D%201%3B%0AWhile(Column(dtbase%2C%201)%5BtheRow%5D%20!%3D%20%22Freq(Hz)%22%2C%0A%09If(Column(dtbase%2C%202)%5BtheRow%5D%20!%3D%20%22%22%2C%0A%09%09dtbase%20%26lt%3B%26lt%3B%20new%20table%20variable(Column(dtbase%2C%201)%5BtheRow%5D%2C%20Column(dtbase%2C%202)%5BtheRow%5D)%0A%09)%3B%0A%09theRow%2B%2B%3B%0A)%3B%0A%0A%2F%2F%20Get%20rid%20of%20unwanted%20rows%20(removal%20of%20blanks)%0Adtbase%20%26lt%3B%26lt%3B%20select%20where(As%20Column(dtbase%2C%205)%20%3D%3D%20%22%22%20%26amp%3B%20As%20Column(dtbase%2C%206)%20%3D%3D%20%22%22)%3B%0Adtbase%20%26lt%3B%26lt%3B%20delete%20rows%3B%0A%0A%2F%2F%20Rename%20columns%0AtheRow%20%3D%20(dtbase%20%26lt%3B%26lt%3B%20get%20rows%20where(As%20Column(dtbase%2C%201)%20%3D%3D%20%22Freq(Hz)%22))%5B1%5D%3B%0AFor(i%20%3D%201%2C%20i%20%26lt%3B%3D%20N%20Cols(dtbase)%2C%20i%2B%2B%2C%0A%09If(%0A%09%09Column(dtbase%2C%20i)%5BtheRow%5D%20%3D%3D%0A%09%09%22USAMP2_newepi_U1_VG_np467_VD_15_ID_500mA_VC_9_P5dB_biasteeoutsideTC_postdecap%22%2C%20%22Filename%22%2C%0A%09%09Column(dtbase%2C%20i)%5BtheRow%5D%20!%3D%20%22Filename%22%2C%0A%09%09%09Column(dtbase%2C%20i)%20%26lt%3B%26lt%3B%20set%20name(Column(dtbase%2C%20i)%5BtheRow%5D)%0A%09)%0A)%3B%0Adtbase%20%26lt%3B%26lt%3B%20delete%20rows(theRow)%3B%0A%0A%2F%2FConvert%20columns%20to%20numeric%0ALocal(%7Bold%20dt%20%3D%20Current%20Data%20Table()%7D%2C%0A%09Current%20Data%20Table(dtbase(%22base_table%22))%3B%0A%09For%20Each(%7Bcol%2C%20index%7D%2C%0A%09%09%7B%3A%22Freq(Hz)%22n%2C%20%3A%22S21%20Log%20Mag(dB)%22n%2C%20%3A%22S11%20Log%20Mag(dB)%22n%2C%20%3A%22S12%20Log%20Mag(dB)%22n%2C%0A%09%09%3A%22S22%20Log%20Mag(dB)%22n%2C%20%3A%22CompIn21%20Log%20Mag(dBm)%22n%2C%20%3A%22CompOut21%20Log%20Mag(dBm)%22n%2C%0A%09%09%3A%22DeltaGain21%20Log%20Mag(dB)%22n%2C%20%3A%22CompDMM%20VC%20Lin%20Mag(A)%22n%2C%20%3A%22CompDMM%20VD%20Lin%20Mag(A)%22n%7D%2C%0A%09%09col%20%26lt%3B%26lt%3B%20Data%20Type(Numeric)%20%26lt%3B%26lt%3B%20Set%20Modeling%20Type(%22Continuous%22)%20%26lt%3B%26lt%3B%20Set%20Field%20Width(12)%0A%09)%3B%0A%09Current%20Data%20Table(old%20dt)%3B%0A)%3B%0A%0A%0A%2F%2F%20Add%20New%20column%3A%20Freq(GHz)%0AData%20Table(%22Base%20Table%22)%20%26lt%3B%26lt%3B%20New%20Column(%22Freq(GHz)%22%2C%0A%09Numeric%2C%0A%09%22Continuous%22%2C%0A%09Format(%22Best%22%2C%2012)%2C%0A%09Formula(%3A%22Freq(Hz)%22n%20%2F%201000000000)%0A)%20%26lt%3B%26lt%3B%20Move%20Selected%20Columns(%7B%3A%22Freq(GHz)%22n%7D%2C%20after(%3A%22Freq(Hz)%22n))%3B%0AData%20Table(%22Base%20Table%22)%20%26lt%3B%26lt%3B%20Text%20to%20Columns(columns(%3AFilename)%2C%20Delimiters(%22_%22))%3B%0A%0A%2F%2FRename%20columns%0A%3AFilename1%20%26lt%3B%26lt%3B%20Set%20Name(%22%20Product%20ID%22)%3B%0A%3AFilename%202%20%26lt%3B%26lt%3B%20Set%20Name(%22%20Variant*%22)%3B%0A%3AFilename%203%20%26lt%3B%26lt%3B%20Set%20Name(%22%20SN%23%22)%3B%0A%3AFilename%204%20%26lt%3B%26lt%3B%20Set%20Name(%22%20VG%20%22)%3B%0A%3AFilename%205%20%26lt%3B%26lt%3B%20Set%20Name(%22%20VG%20value%20%22)%3B%0A%3AFilename%206%20%26lt%3B%26lt%3B%20Set%20Name(%22%20VD%20%22)%3B%0A%3AFilename%207%20%26lt%3B%26lt%3B%20Set%20Name(%22%20VD%20value%20%22)%3B%0A%3AFilename%208%20%26lt%3B%26lt%3B%20Set%20Name(%22%20ID%20%22)%3B%0A%3AFilename%209%20%26lt%3B%26lt%3B%20Set%20Name(%22%20ID%20value%20%22)%3B%0A%3AFilename%2010%20%26lt%3B%26lt%3B%20Set%20Name(%22%20VC%20%22)%3B%0A%3AFilename%2011%20%26lt%3B%26lt%3B%20Set%20Name(%22%20VC%20value%20%22)%3B%0A%3AFilename%2012%20%26lt%3B%26lt%3B%20Set%20Name(%22%20Parameter%20%22)%3B%0A%0A%0A%2F%2FSave%20JMP%20table%0Adtbase%20%26lt%3B%26lt%3B%20save(%22%22)%0A%0A%0A%2F%2Fthru_line_file%20%3D%20Pick%20File%20(%22Pick%20a%20Data%20File%22)%3B%0A%0A%2F%2FAfter%20this%20point%2C%20I%20need%20to%20allow%20user%20to%20pick%20a%20file.%20once%20he%20picks%20it%2C%20I%20would%20like%20to%20extract%20a%20column%20from%20that%20file%20by%20matching%20one%20column%20(common%20to%20both%20files)%20and%20get%20that%20column%20into%20the%20base%20table%20I%20created%20before.%0A%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CEM%3EEdit%202024-09-13%20jthi%20%3A%20Ajout%20du%20formatage%20JSL%3C%2FEM%3E%3C%2FDIV%3E%0A%3CDIV%3E%20%3C%2FDIV%3E%0A%3CDIV%3EMerci%2C%3C%2FDIV%3E%0A%3CDIV%3EVishal%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-798399%22%20slang%3D%22en-US%22%20mode%3D%22UPDATE%22%3E%3CLINGO-LABEL%3EAutomatisation%20et%20scripts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798483%22%20slang%3D%22en-US%22%20mode%3D%22CREATE%22%3ERe%3A%20J'ai%20pu%20invoquer%20des%20fichiers%20csv%20et%20les%20concat%C3%A9ner%20dans%20une%20table%20JMP.%20J'ai%20ajout%C3%A9%20quelques%20modifications%20(modification%20des%20noms%20de%20colonnes%2C%20ajout%20de%20noms%20de%20colonnes%2C%20etc.).%20Je%20souhaite%20invoquer%20un%20autre%20fichier%20csv%20d%C3%A9fini%20par%20l'utilisateur%20comme%20une%20autre%20table%20jmp%20et%20extraire%20des%20informations%20de%20celui-ci%20pour%20les%20ajouter%20%C3%A0%20la%20premi%C3%A8re%20table%20JMP%20concat%C3%A9n%C3%A9e%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798483%22%20slang%3D%22en-US%22%20mode%3D%22CREATE%22%3E%3CP%3EVous%20pouvez%20utiliser%20%3CA%20href%3D%22https%3A%2F%2Fwww.jmp.com%2Fsupport%2Fhelp%2Fen%2F17.2%2F%23page%2Fjmp%2Ffile-functions.shtml%3Fos%3Dwin%26amp%3Bsource%3Dapplication%23ww4961728%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EPick%20File()%3C%2FA%3E%20pour%20permettre%20%C3%A0%20l'utilisateur%20de%20s%C3%A9lectionner%20un%20fichier%20et%20ensuite%20vous%20pourrez%20peut-%C3%AAtre%20utiliser%20Update%20pour%20ajouter%20les%20nouvelles%20informations%20%C3%A0%20votre%20tableau.%20Effectuez%20d'abord%20la%20mise%20%C3%A0%20jour%20de%20mani%C3%A8re%20interactive%20dans%20JMP%2C%20puis%20r%C3%A9cup%C3%A9rez%20le%20script%20cr%C3%A9%C3%A9%20par%20JMP%3C%2FP%3E%3C%2FLINGO-BODY%3E
Choose Language Hide Translation Bar

I was able to invoke csv files and concatenate them to a JMP table. I added some editing (modify column names, add column names, etc). I want to invoke another user defined csv file as another jmp table and extract information from that to add to the first concatenated JMP table

Please see my script so far. At the end of the script, I want to open another csv file into a jmp table and extract columns into the originally created jmp table from the first invocation.

Names Default To Here(1);
//dt = Open( "C:\Users\VISHAL.SANGHAI\OneDrive - MiniCircuits\Documents\Mini Circuits Work Folder\US-AMP\US-AMP-2\Pkg data V1 old epi June 2024 testing\Power with new epi boards - Jul 2024\USAMP2_newepi_U2_VG_np465_VD_15_ID_500mA_VC_9_P5dB_biasteeoutsideTC_postdecap.csv" );


//concatenate files
// Setup an empty table to start to concatenate other tables to
dtbase = New Table("Base Table");
// Have the user select the starting folder
dirpath = Pick Directory("Pick the Top Level Directory");
// Get all of the files in all of the folders that are under the picked folder
TheFileList = Files In Directory(dirpath, "recursive");

               
// Go through all of the files and read in append those that meet your
// requirement.  You will need to amend the IF statement to meet your criteria
For(i = 1, i <= N Items(TheFileList), i++,
	If(Uppercase(Word(-1, TheFileList[i], ".")) == "CSV" & Is File(dirpath || TheFileList[i]) == 1,
		dt = Open(dirpath || TheFileList[i]);                                                               //to add filename as separate column in datatable
             		dtname = dt << get name();                                                                            //to add filename as separate column in datatable
             		dtname_list = Repeat({dtname}, N Row(dt));                                                        //to add filename as separate column in datatable
             		dt << New Column("Filename", character, set values(dtname_list));                                 //to add filename as separate column in datatable
             		dtbase = dtbase << concatenate(dt, append to first table(1));
              
             		Close(dt, nosave);
	)
);


// Add Global info as table variables
// Scan the data table based on column and assign the right row
theRow = 1;
While(Column(dtbase, 1)[theRow] != "Freq(Hz)",
	If(Column(dtbase, 2)[theRow] != "",
		dtbase << new table variable(Column(dtbase, 1)[theRow], Column(dtbase, 2)[theRow])
	);
	theRow++;
);

// Get rid of unwanted rows (removal of blanks)
dtbase << select where(As Column(dtbase, 5) == "" & As Column(dtbase, 6) == "");
dtbase << delete rows;

// Rename columns
theRow = (dtbase << get rows where(As Column(dtbase, 1) == "Freq(Hz)"))[1];
For(i = 1, i <= N Cols(dtbase), i++,
	If(
		Column(dtbase, i)[theRow] ==
		"USAMP2_newepi_U1_VG_np467_VD_15_ID_500mA_VC_9_P5dB_biasteeoutsideTC_postdecap", "Filename",
		Column(dtbase, i)[theRow] != "Filename",
			Column(dtbase, i) << set name(Column(dtbase, i)[theRow])
	)
);
dtbase << delete rows(theRow);

//Convert columns to numeric
Local({old dt = Current Data Table()},
	Current Data Table(dtbase("base_table"));
	For Each({col, index},
		{:"Freq(Hz)"n, :"S21 Log Mag(dB)"n, :"S11 Log Mag(dB)"n, :"S12 Log Mag(dB)"n,
		:"S22 Log Mag(dB)"n, :"CompIn21 Log Mag(dBm)"n, :"CompOut21 Log Mag(dBm)"n,
		:"DeltaGain21 Log Mag(dB)"n, :"CompDMM VC Lin Mag(A)"n, :"CompDMM VD Lin Mag(A)"n},
		col << Data Type(Numeric) << Set Modeling Type("Continuous") << Set Field Width(12)
	);
	Current Data Table(old dt);
);


// Add New column: Freq(GHz)
Data Table("Base Table") << New Column("Freq(GHz)",
	Numeric,
	"Continuous",
	Format("Best", 12),
	Formula(:"Freq(Hz)"n / 1000000000)
) << Move Selected Columns({:"Freq(GHz)"n}, after(:"Freq(Hz)"n));
Data Table("Base Table") << Text to Columns(columns(:Filename), Delimiters("_"));

//Rename columns
:Filename1 << Set Name(" Product ID");
:Filename 2 << Set Name(" Variant*");
:Filename 3 << Set Name(" SN#");
:Filename 4 << Set Name(" VG ");
:Filename 5 << Set Name(" VG value ");
:Filename 6 << Set Name(" VD ");
:Filename 7 << Set Name(" VD value ");
:Filename 8 << Set Name(" ID ");
:Filename 9 << Set Name(" ID value ");
:Filename 10 << Set Name(" VC ");
:Filename 11 << Set Name(" VC value ");
:Filename 12 << Set Name(" Parameter ");


//Save JMP table
dtbase << save("")


//thru_line_file = Pick File ("Pick a Data File");

//After this point, I need to allow user to pick a file. once he picks it, I would like to extract a column from that file by matching one column (common to both files) and get that column into the base table I created before.
;
Edit 2024-09-13 jthi: Added JSL formatting
 
Thanks,
Vishal
1 REPLY 1
jthi
Super User

Re: I was able to invoke csv files and concatenate them to a JMP table. I added some editing (modify column names, add column names, etc). I want to invoke another user defined csv file as another jmp table and extract information from that to add to the first concatenated JMP table

You can use Pick File() to let user select a file and after that you might be able to use Update to add the new information to your table. Do the update first interactively in JMP and then get the script created by JMP

-Jarmo