merging columns to a table from multiple text files

Hi everyone,

I'm trying to create a for loop in order to merge the 8th column of several text files to one table.

files = dir('*.txt');

for i = 1:length(files)

files(i).name

tables = readtable(files(i).name)

XXX

end

Thank your for any help!

[Walter Roberson]

To confirm, the resulting table should contain exactly one variable, and that will be the merged column 8? Or should there be additional variables in the table?

[Franziska Motka]

No, I would also need a column for the code for each participant, thanks for asking!

Answer 1

[Sarah Gilmore]

Hi Franziska,

You could collect the eith column from each file and temporarily store the data in a cell array before constructing your final table.

files = dir("*.txt");
numTotalFiles = numel(files);
columnData = cell(1, numTotalFiles);
for i = 1:numTotalFiles
    temp = readtable(files(i).name);
    columnData{i} = temp.(8);
end

finalTable = table(columnData{:});  

finalTable will contain the 8th column from each file.

Also, you may want to consider calling detectImportOptions to ensure each file is read in the same way (I am assuming the files have the same structure). readtable calls detectImportOptions by default if an Import Options object is not passed in order to determine how to import the data. Here's how you could do this:

files = dir("*.txt");

opts = detectImportOptions(files(1).name);

% Tell readtable to import only the 8th column
opts.SelectedVariableNames = 8;

numTotalFiles = numel(files);
columnData = cell(1, numTotalFiles);

for i = 1:numTotalFiles
    temp = readtable(files(i).name, opts);
    columnData{i} = temp.(1);
end

finalTable = table(columnData{:});  

[Franziska Motka]

Thank you so much!!! It worked :)

[Franziska Motka]

Now I'am trying to give names to the header of each column. So the first column is the rating of participant CBM20602 and the second column of participant CBM22201. The command files = dir("*.txt"); prints CBM20602.txt and CBM22201.txt. Could you give me a hint for this too? I tried it with the array2table command, but I can not find out how it is done automatically within the loop.

[Sarah Gilmore]

Hi Franziska,

If you want to use the filenames as the variable names for each column, you could modify your code to save the filenames (without the extensions) in a string array. When creating the final table, you just have to pass this string array as the VariableNames name-value pair.

files = dir("*.txt");
opts = detectImportOptions(files(1).name);

% Tell readtable to import only the 8th column
opts.SelectedVariableNames = 8;
numTotalFiles = numel(files);
columnData = cell(1, numTotalFiles);

% allocate a 1 by N string array
variableNames = strings(1, numTotalFiles);

for i = 1:numTotalFiles
    temp = readtable(files(i).name, opts);
    columnData{i} = temp.(1);
    
    [~, name, ~] = fileparts(temp);
    variableNames(i) = name;
end

% pass variableNames in as the VariableNames nv-pair
finalTable = table(columnData{:}, "VariableNames", variableNames); 

[Franziska Motka]

Thank you again for your answer! Unfortunatelly, now I get the following error message for the line "[~, name, ~] = fileparts(temp);"

Error using fileparts

Input must be a row vector of characters, or a string scalar, or a cellstr, or a string matrix.

Can you help here?

[Sarah Gilmore]

Hi Franziska,

I had a typo in my last response. That line should have been:

[~, name, ~] = fileparts(files(i).name);

Sorry for any confusion I caused.

[Franziska Motka]

No I reseave an error message for the last line:

Error using table

All table variables must have the same number of rows.

Caused by:

You might have intended "VariableNames" to specify a parameter name. However, the table constructor

requires parameter names to be specified as character vectors. Use table(..., VariableNames=Value) or

table(..., 'VariableNames', Value) instead.

If I type in the two suggestions, I receive 'Value' requires Model-Based Calibration Toolbox.

Any ideas?

[Franziska Motka]

variableNames is a 1x2 string and columnData is a 1x2 cell

[Sarah Gilmore]

Hi Franziska,

Sorry again. I should have specified the VariableNames name-value pair as a char array instead of a string.

finalTable = table(columnData{:}, 'VariableNames', variableNames); 

table requires parameter names to be specified as character vectors. Most other functions do not have this requirement. You can also use this syntax if you prefer:

finalTable = table(columnData{:}, VariableNames=variableNames); 

I wasn't sure what release you were using, so I didn't want to suggest the syntax above since it was only introduced in R2021a. Either one of these lines should resolve the error.

[Franziska Motka]

Fantastic, it works. Thank you so much for all your help!!