topic Re: How to save text file open setting into script in Discussions

How to save text file open setting into script

Kathorvath — Sat, 10 Jun 2023 23:29:30 GMT

Hello,

Problem:

I have numerous .txt files. I only care about the data in column 4, starting with row 7 to infinity. I would like to extract all of the data from column 4 from the numerous .txt files and concatenate them into a single table. What is the best way to do this? Ideally, I would like to create a script to execute this.

Along the same vein, when I use the File - open method to open the .txt files with Data (using preview), I can properly format my column 4 data, BUT I can't figure out how to find or save these 'open' setting. I would like to save to the script so I can open the other .txt files with the same settings.

How do I do this?

I've attached one of my sample .txt files for reference.

Thanks for the help!

Re: How to save text file open setting into script

txnelson — Thu, 29 Apr 2021 18:27:05 GMT

I believe the route to take is to use Import Multiple Files

File=>Import Multiple Files

You will read in all files in a folder, and concatenate them(if you want). Then all you will have to do, is to delete the columns you don't want.

Also, after running the whole folder, or maybe just a few files for a test case, you can go to the Source entry in the data table's upper left panel and right click on it and select Edit, and you will get all of the JSL required to run from a script.

Re: How to save text file open setting into script

Kathorvath — Thu, 29 Apr 2021 20:45:33 GMT

Sorry, I did not describe my goal clearly enough. The suggestion does not give me what I need. I've also realized I will need to use the join command not the concatenate command.

I have 39 .txt files. I only want the data from the 4th column of each .txt file. I want to extract that data and add it to a new table such that I have 39 separate columns (one for each 4th column in all 39 .txt files).

Re: How to save text file open setting into script

txnelson — Thu, 29 Apr 2021 21:22:27 GMT

For example purposes, I made 4 copies of your example file, and placed them all into a folder under my Discussion Group folder

Documents\Discussion Group\textfile

I then went to

File=>Import Multiple File

I selected the check box for "Add file name column"

I then clicked on "Settings"

I unchecked "Has Headers"

I changed the "First Data Line" to 7

I unchecked in the End Of Field box, "Comma" and checked "Spaces"

Clicked on OK

Clicked on Import

The data table opened with Col1-Col6 columns plus the FileName columns

The only columns that are wanted are columns Col5 and File Name so delete all others

Now all that has to be done, is to separate the data from the 4 input files into columns

Go to

Tables=>Split

Select File Name as the "Split By" column

Select Col 5 as the "Split Columns" column

Click on OK

The new table will have one column for each of the input files

This should be the final form of the table you need. You can rename the columns to whatever you want.

Re: How to save text file open setting into script

ms — Thu, 29 Apr 2021 23:38:20 GMT

Thanks Jim for showing Open Multiple files. It seems to be very powerful. Strangely, I have never used it – probably because of old habit of scripting tasks like this.

If all files have exactly the same number of rows, the below script should give the same final table.

Names Default To Here( 1 );
path = "path to directory of text files";
files = Files In Directory( path );

open_expr = Expr(
	dt = Open(
		path || Expr( files[i] ),
		Import Settings(
			End Of Line( CRLF, CR, LF ),
			End Of Field( Spaces, CSV( 0 ) ),
			Data Starts( 7 ),
			Lines To Read( "All" )
		),
		private
	)
);

M = []; //Matrix to hold data from multiple files
L = {}; // List of imported file names

For( i = 1, i <= N Items( files ), i++,
	If( Ends With( files[i], ".txt" ),
		Eval( Eval Expr( open_expr ) );
		Insert Into( L, files[i] );
		M ||= dt[0, 5]; // Add column 4 (5 becaue of fronting spaces in files)
		Close( dt, no save );
	)
);
// Make into table
dt = As Table( M, <<Column names( L ) );

Re: How to save text file open setting into script

Kathorvath — Fri, 30 Apr 2021 02:42:32 GMT

Thank you both for the suggestions! I think the scripting option was a little bit more like what I was hoping to do and worked like a charm! I'll have to practice with importing multiple files too.