JStop
Community Trekker

Use JSL to pull misaligned table-like data from txt file

Hi,

I am having a lot of difficulty extracting the data I need from a text file. Data is a .txt file of format below (file attached), and is repeated in a large (100 Mb) file :

 

Temperature 0x100
PART ID 0x5A6A111F
5626 0 PASS DC_VoLevels.4 GPIO_32 312 1.0000 V 3.1638 V 3.3000 V -4.0000 mA 0


Site Sort Bin
------------------------------------
0 123 4

 

I need to create a dat table with 2 columns and headings Part ID and Bin and data listed below:

PART ID                        Bin

0x5A6A111F                  4

 

I can pull the part ID, but the Bin and number below is what I am having problems with. Any help would be very much appreciated.

 

Jen

 

 

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions

Re: Use JSL to pull misaligned table-like data from txt file

Importing data like this is tricky, but it can be done by reading the file line by line, checking for keywords to determine what to do with the data. See example below for details.


// load the file to be imported as a string
str = Load Text File( "$downloads\SampleFile.jsl" );

// create a table to add to
nt = New Table( "Example",
	New Column( "PartID", "Character" ),
	New Column( "Bin", "Numeric" )
);

// create a list with strings of each line of data
lines = Words( str, "\!N" );

// loop through each line of the file
for(i=1,i<=nitems(lines),i++,
	
	if( // If the line starts with PART ID, 
		starts with(lines[i], "PART ID" ),
			// it's the start of a new row
			nt << add rows(1);
			// grab the PartID at the end of the line
			nt:PartID = Word( -1, lines[i] );
	,
		// if it starts with a bunch of dashes
		starts with(lines[i], "---" ),
			// the next line must contain column values
			// so grab them and split them by spaces
			colVals = Words( lines[i+1], " " );
			
			// set the Bin column to the value in the third position
			nt:Bin = colVals[3];
	);
	
)

This script creates the following table:

Capture.PNG

Justin

View solution in original post

2 REPLIES 2

Re: Use JSL to pull misaligned table-like data from txt file

Importing data like this is tricky, but it can be done by reading the file line by line, checking for keywords to determine what to do with the data. See example below for details.


// load the file to be imported as a string
str = Load Text File( "$downloads\SampleFile.jsl" );

// create a table to add to
nt = New Table( "Example",
	New Column( "PartID", "Character" ),
	New Column( "Bin", "Numeric" )
);

// create a list with strings of each line of data
lines = Words( str, "\!N" );

// loop through each line of the file
for(i=1,i<=nitems(lines),i++,
	
	if( // If the line starts with PART ID, 
		starts with(lines[i], "PART ID" ),
			// it's the start of a new row
			nt << add rows(1);
			// grab the PartID at the end of the line
			nt:PartID = Word( -1, lines[i] );
	,
		// if it starts with a bunch of dashes
		starts with(lines[i], "---" ),
			// the next line must contain column values
			// so grab them and split them by spaces
			colVals = Words( lines[i+1], " " );
			
			// set the Bin column to the value in the third position
			nt:Bin = colVals[3];
	);
	
)

This script creates the following table:

Capture.PNG

Justin

View solution in original post

JStop
Community Trekker

Re: Use JSL to pull misaligned table-like data from txt file

Thanks, this looks great I will give it a try.
0 Kudos