Subscribe Bookmark RSS Feed
Byron_JMP

Staff

Joined:

Apr 26, 2012

Combine multiple txt or csv files

This script will combine multiple txt or csv files into one file. The script asks for a target directory, opens the first file and then iteratively adds the subsequent files to the first using the Table Concatenate function.  One new column is created in the final file: this column will contain the name of the file that each row came from. If all the the text files have the same (identical) column names, those columns will be stacked (concatenated) all in one column. Unique columns will also be transferred to the final file.

The script has several sections block commented out, these sections of the script could be used for combining multiple Excel files. When combining Excel files, there is an assumption that these .xls or .xlsx files will only have one worksheet.

-------Updated 01Jun2016

I had an interesting question about this script. Someone had a pile of CSV files where the column names were on the second row, and to make matters worse, the column names were semicolon delimited while the text was comma delimited.  One way to deal with this is to add Import Settings to the script.  This is one of those things that I'm happy to have JMP write for me.  I manually opened and imported the file (using File>Open>Open with Preview, then in the dialog set the columns to start on the second row, and added an "other" delimiter of  ";".  After importing the file I went to the source script and found the Import Settings argument.

To make the script open this files, I added an expression, importset=expr(import settings(...paste script here...)); up near the top of the script. (its an expression because its not useful to have the import settings evaluated until I need them later when I open the files.)

Then down near the bottom, I added the expression to the open argument, open(filename,importset). 


IMPORTANT NOTE:  If you're going to download this second script with the import filter and expect it to work automagically on your unusually formatted CSV tables..  its just not going to work, you have to make you own import setting and substitute them into the settings that are here for and example. 

--------There was also a question about how to make this work on a MAC a while back.

I added one other bit so that it works on a mac too (which means I didn't test this on a PC)

If( Host is( Mac ),

  filepath="/"||prefilepath,

filepath = Convert File Path( prefilepath, Windows )

  );


-----Updated 10Oct2016

Justin C noticed that file names with multiple "."s didn't work so well and suggested changing the "item" to be -1 so that the file extensions rather than the second item are used for file extensions. Nice fix, and an important idea to keep in mind when working with file names. Thanks Justin!


Comments
dale_lehman

Where is the script?  I can't seem to find it anywhere.

Byron_JMP

Dale,

Would you mind checking the attachment again?

I re-attached it.

-Byron

dale_lehman

Thanks, I got it now.

seanlee

Bryan,


I use the JMP 10.0.0 on OS X 10.10.2. There will be a window launched when I run the script for selecting the files. However, I can not select any .txt or .csv file in the launched window. I am not sure if the script needs further revision for JMP (OS X ver.). Could you help for solving this issue? Thank you.

Sean

Byron_JMP

Sean,

On the MAC, the dialog window to select a directory or to select a file are identical. The scrip is looking for you to identify a directory containing csv and/or txt files. The script currently allows both csv and txt files in the list of files to concatenate. If you want it to be more restrictive, then just edit out the file type you would like excluded from this part of the script.

//filter out any non-txt or csv files

For( i2 = 1, n2 >= i2, i2++,

  file=(prefilelist[i2]);

  If( Item( 2, prefilelist[i2], "." ) == "txt" | Item( 2, prefilelist[i2], "." ) == "csv",

  Insert Into( filelist,file),

  show(file)

  )

);

What would be kind of cool is if the script would return a list of the file extensions in the selected directory and then let the user check off the types of files to add to the filelist variable.

If you have time to do some scripting, please post the upgraded script!

Best regards,

Byron

kaushik_pushpen

Hi Byron,

Thank you for this valuable script. I am using this script to add multiple .txt files. But it is running after copying the same script into new script. I want to ask that why it is not running when opening the same script in first attempt. Thank you for the answer in advance. This script is very helpful.

Byron_JMP

I'm not sure I understand the question. Is this behavior replicated with other scripts?

kaushik_pushpen

Hello Byron,

It is happening only in this script. It is showing "NItem()" blank error in log window after running the script. So i just copy the whole script and paste it in new script window, then it is running ok.

Thanks

mhegazy

Hi Byron,

Nice script and works like a charm in JMP 12. Was wondering about a good way to begin reading the source data files from a specific row, truncate header info if you will.  For instance, begin with row that starts with the word "CH" if all source files format will be the same?

mhegazy

Hi Bryon and all,

I was able to add a small update: remove trailing file extensions like .csv or .txt from the source column name :

so in the last section instead of :

set each value (filenow)

 

I added :

set each value (remove (filenow,-4,-1))

I was surprised that the negative reference worked !

Byron_JMP

yes, the negative ref is handy, and it works in nearly all the functions that reference character position, as well as in some places you might not expect ;like,  imagine having -1 decimal places.

jiangrong_cao_g

This script doesn't run on my system: Mac OS X 10.10.5 + JMP Pro 12.1.0

tsolomon

Hi Byron, this script works great. I modified slightly to select a subset of the data from the files opened, add columns, change characters to numeric, etc. One question I had, is there a simple way to modify the script to specify the folder where the files are and not have a prompt pop up?

Thanks

TS

smap10060

Is there any easy way to combine multiple txt or csv files side by side using JOIN instead of vertically combining with Concatenate in Combine txt tables.jsl?

Byron_JMP

The same approach will work with "join". Just try substituting the concat argument with a join argument.  (do the join manually, and then copy and the Source script in the resulting table for an example of what the join argument would look like

Diag

Just want to say thank you Byron. That's a great and practical example.

This script saves me so much time !!!

That script does within seconds what was taking me hours and hours of work.

Thank you!!!

Article Labels