Good morning, So I'm new to jsl and trying to figure this out. The data sets I'm getting from our servers have a ton of extra columns, often col's that are blank. I'm hoping to start most of my scripts with a user selected dialogue box allowing them to select/add/drop columns of their choice and possibly rearrange them into a preferred order? Any help? Let's say the col's are: Col#ColName 1date_export 2 product_type 3 part_Number 4 recipe 5date_run 6measurement_type 7Value 8Tool 9Target OK, so some of these col's aren't needed and they all are definitely out of order, just from a human viewing perspective. Ideally, I'd like the script to autoselect and order something like: Col#: 2, 8, 4, 5, 6, 7, 9 and then maybe have a user dialogue box allowing user to select any other column to add to the reformatted output if they desire. what do you think?

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

import: a lot of extra col's. User select the ones they want?

saspecvd

Good morning,

So I'm new to jsl and trying to figure this out. The data sets I'm getting from our servers have a ton of extra columns, often col's that are blank. I'm hoping to start most of my scripts with a user selected dialogue box allowing them to select/add/drop columns of their choice and possibly rearrange them into a preferred order? Any help?

Let's say the col's are:

Col#	ColName
1	date_export
2	product_type
3	part_Number
4	recipe
5	date_run
6	measurement_type
7	Value
8	Tool
9	Target

OK, so some of these col's aren't needed and they all are definitely out of order, just from a human viewing perspective. Ideally, I'd like the script to autoselect and order something like:

Col#: 2, 8, 4, 5, 6, 7, 9

and then maybe have a user dialogue box allowing user to select any other column to add to the reformatted output if they desire.

what do you think?

Accepted answers

All comments

Mark_Bailey

Are the source files flat or relational? What is the source file type? What kind of files are you importing? Do you have to import the entire file?

Can you query a database for the result? If so, you can build a query to give you what you want.

saspecvd

Hey Mark,

All good questions. I wish I could just script a query on the front end, but my company's DB are locked down. We have to put in requests and we get out a flat excel .csv file that has all these unnecessary columns and poorly named headers in a mismatched order (like 35 columns). I'm working on getting read-only access, but that's a different problem.

Mark_Bailey

Hopefully the nature of the foreign file format is stable. You might try using this argument to the Open() function for importing:

Select Columns( "col", ... )

Assuming that you stored the data reference in a variable (such as dt) when it opened, you can then iteratively re-arrange the columns using this pattern:

dt	<< Go to Column( col ref )		// select column to move
	<< Move Selected Columns( ... )	// move column to new location
	<< Clear Column Selection;		//

Hope this reply gets you moving on your way.

uday_guntupalli

dt = Open( "$SAMPLE_DATA\Big Class.jmp" ); 

// Get Column Names 
ColNames = dt << Get Column Names("String");

// Open a window  for user selection 
NW1 = New Window("User Selection",
					<< Modal, 
					V List Box(
						Lineup Box(
								Lb1 = List Box(ColNames);
								  )
							  ),
					H List Box( 
						Button Box("OK",
									// Unload User Inputs
									Rb1Val = Lb1 << Get Selected(); 
								   )
							  )	  
                );
             
// Select Columns 
for(i = 1, i <= N Items(ColNames), i ++, 
		If(Contains(Rb1Val,ColNames[i]),
				Col = Column(dt,i); ;
				Col << Set Selected(1);
		  );
   );
   
// Delete Unwanted Columns 
dt << Invert Column Selection ;
dt << Delete Columns ;

David_Burnham

Here is a piece of code that I use to create a table script that will zap empty columns when I'm reading data in:

	// add a table script that will remove empty columns
	dt << Set Property( "Remove Empty Columns",
		dt = Current Data Table();
		lstToDelete = {};
		For (i=1,i<=NCols(dt),i++,
			col = Column(dt,i);
			If (Col Number(col)==0,
				InsertInto(lstToDelete,i)
			)
		);
		If (NItems(lstToDelete)>0,
			dt << Delete Columns(lstToDelete)
		);		
	);

saspecvd

awesome! what about a pre-determined list of column names? Like my data tables come with dozens or extra columns (often not empty).

select colname (crap1, crap2, crap3...)

delete selected columns

then go on to allow user selection of the trimmed down remainder?

uday_guntupalli

Presuming I understand your question :

// Open Sample Data Table 
dt = Open( "$SAMPLE_DATA\Air Traffic.jmp" ); 

// Define list of columns to delete 
DelList = {"Carrier Code","Flight Number"}; 

// Delete Columns 
dt << Delete Columns(DelList);