Subscribe Bookmark RSS Feed

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

saspecvd

Occasional Contributor

Joined:

Dec 29, 2016

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

 
product_type 
part_Number 
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?

8 REPLIES
markbailey

Staff

Joined:

Jun 23, 2011

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.

Learn it once, use it forever!
saspecvd

Occasional Contributor

Joined:

Dec 29, 2016

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.

markbailey

Staff

Joined:

Jun 23, 2011

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.

 

Learn it once, use it forever!
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

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 ;
saspecvd

Occasional Contributor

Joined:

Dec 29, 2016

I like this a lot, selecting the desired col's, then inverting it and deleting the undesired col's.

 

Do you have advice for running a prestep, where I can do a mass 'delete' of pre-specified col's to trim down the user selection?  

Unfortunately, my data files come with a few dozen unnecessary columns for things that aren't needed.

Maybe something I could just write in to the start of every dt=open(), to delete the known 'unneeded cols'?

David_Burnham

Super User

Joined:

Jul 13, 2011

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)
		);		
	);
-Dave
saspecvd

Occasional Contributor

Joined:

Dec 29, 2016

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

Community Trekker

Joined:

Sep 15, 2014

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