Compiling .txt files with different length names into one excel file

I have about 60 .txt files that all have different length names (named for participant ID numbers and date and time) I am trying to compile all of them into a single excel file. The .txt files have data that I want to fill 7x80 cells one right below the next. If possible I want to have a header line between each dataset with the filename. Heres the code that I got from someone else. It works if I do the files one at a time but oof that is going to take forever. Any tips?

inputfile(i) = 'C:\Users\Owner\Documents\Masters Thesis\Data\*.txt';
outputfile = 'C:\Users\Owner\Documents\Masters Thesis\Data\Dataoutput.xlsx';  %I'd recommend xlsx over xls
data = readtable(inputfile, 'ReadVariableNames', false);
writetable(data, outputfile, 'WriteVariableNames', false, 'Range', 'A2');
filecontent = strsplit(fileread(inputfile), {'\n', '\r'}); 
writetable(cell2table(filecontent(1)), outputfile, 'WriteVariableNames', false, 'Range', 'A1')

[dpb]

"_about 60 .txt files that all have different length names (named for participant ID numbers and date and time)..."

There's where the trouble starts -- putting metadata in the file names instead of storing the data in the file.

You need first to have a way to select all the files programmatically -- best is with a specific naming convention that lends itself to using wildcard pattern matching, but you can live with making a workding dierectory that contains nothing but these files that can then iterate over all files in the directory.

The rest is pretty straightforward although if you write Excel files you will have to keep track of position at which to write subsequent files -- if there really are only 60-ish and only 7-ish records per file, it's probably simplest to just read the complete set into memory and then write the whole thing at once.

Answer 1

[Ravi]

Hi Sophie

Based on my understanding of your inquiry, it appears that you possess 60 structured data files in .txt format that you wish to consolidate into a single Excel file. To address this concern, kindly adhere to the subsequent procedure and code provided below.

On a high level, the tasks need to be performed are;

1. Read data from a .txt file
2. Write the table to an excel file
3. Repeat the steps 1 and 2 for every input data file (in your case, 60).

To illustrate the solution, I have replicated this issue by utilizing three distinct files (fruits.txt, vegetables.txt, and others.txt), each containing three properties (ID, name, and quantity) with varying row quantities. Please note the prescribed data organization.

In the example, the data folder comprises three files, whereas in your particular scenario, it would encompass 60 files. The compiled file will be saved as collection.xlsx in the designated output directory. Below is an example of the fruits.txt file:

The primary objective is to extract data from each text file and write it to an Excel sheet. It is crucial to ensure that the Excel sheet is in append mode during the writing process to prevent overwriting of previous data. To accomplish this, we define a function called "read_and_write" that accepts two arguments: ’infile’ and ’outfile’. ’infile’ represents the path to the input data file, while ’outfile’ represents the path to the Excel file.

function read_and_write(infile, outfile)
    % the variable names in the table (.txt file)
    varnames = {'ID', 'Name', 'Quantity'};
    
    % read the input data from .txt file
    % into a table and add variable names
    data = readtable(infile);
    data.Properties.VariableNames = varnames;

    % create a header to represent the filename
    % it has the same number of columns as the
    % data in the .txt file
    header = cell(1, width(data));
    % the first entry of the header is set to the filename
    header{1,1} = infile;
    header = cell2table(header);
    header.Properties.VariableNames = varnames;
    
    % header and data are written to the excel file
    % as mentioned by outfile
    writetable(header, outfile, 'WriteMode','append')
    writetable(data, outfile, 'WriteMode','append');
end

To apply the ”read_and_write” function to every file within the data directory, we can iterate over the files present in the directory using the following code snippet:

outfile = 'output\collection.xlsx';
datadir = 'data';
datafiles = dir(datadir);
for i=1:numel(datafiles)
    if ~datafiles(i).isdir
        % not a directory
        infile = datafiles(i).name;
        filepath = [datadir, '\', infile];
        read_and_write(filepath, outfile);
    end
end

Please update the dimensionality and variable names according to your specific requirements. I hope this explanation proves beneficial in resolving your issue.