Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Mar 4, 2015 8:52 AM
| Last Modified: May 31, 2018 6:09 AM
Combine txt tables, with import settings.jsl
Combine txt tables.jsl
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.
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 = Convert File Path( prefilepath, Windows )
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!