How to read multiple text files and save data from text files to excel files?

Hello Everyone,

I have text file like one_1.txt, one_2.txt, one_3.txt, ....one_1000.txt..

where one_1.txt looks like below. I want to save data in individual excel sheet (high_1.xlsx) with 5 columns, say first coumn save 1 to 40, second coumn save 0.0125 to 0.9875, soon. Then I want to average value like C3 (which is 492.275 in high_1.txt) from all files high_2.xlsx, high_3.xlsx and save into another excel sheet (Average.xlsx). Actually, I want to average each cell over multiple excel files. How can I do this in Matlab?

one_1.txt include data as follows:

1 0.0125 473.825 293.313 1.10803

2 0.0375 472.725 327.064 1.07873

3 0.0625 492.275 318.358 1.16238

4 0.0875 485.725 295.258 1.11332

5 0.1125 456.75 300.546 1.02791

6 0.1375 472.675 297.955 1.12186

7 0.1625 465.7 301.555 1.0413

8 0.1875 492.45 307.457 1.17438

9 0.2125 485.425 299.251 1.07715

10 0.2375 470.975 304.504 1.0963

11 0.2625 482.65 320.761 1.10028

12 0.2875 462.5 292.22 1.05614

13 0.3125 480.4 307.054 1.12391

14 0.3375 480.8 296.696 1.14749

15 0.3625 454.625 299.25 1.05861

16 0.3875 474.7 298.206 1.06614

17 0.4125 498.4 303.517 1.13904

18 0.4375 489.2 296.877 1.15323

19 0.4625 469.75 293.689 1.0641

20 0.4875 481.125 303.019 1.16578

21 0.5125 463.8 308.737 1.03015

22 0.5375 491.95 289.304 1.18222

23 0.5625 444.85 306.6 1.01817

24 0.5875 484.275 298.557 1.11105

25 0.6125 483.15 306.337 1.10713

26 0.6375 486.95 304.554 1.12692

27 0.6625 471.175 294.856 1.07038

28 0.6875 493.95 304.967 1.18124

29 0.7125 481.575 306.198 1.0536

30 0.7375 506.5 287.533 1.15343

31 0.7625 441.85 302.649 1.0025

32 0.7875 488.4 299.192 1.12691

33 0.8125 461.95 299.732 1.05046

34 0.8375 511.1 302.969 1.18894

35 0.8625 481.875 289.707 1.10525

36 0.8875 486.225 301.802 1.14574

37 0.9125 458.15 295.292 1.07566

38 0.9375 487.925 298.33 1.15556

39 0.9625 444.475 281.917 1.06453

40 0.9875 458.2 300.741 1.01993

[Mario Malic]

Hi Ankit,

Split your work into writing a code for a single file and use that same code to loop over all files. Questions related to importing data and processing multiple files are frequently asked, so you should use the search tool to find an answer.

Answer 1

[Ankit Chauhan]

It takes data (from lines to specific lines) from different files and merged into one file.

clear all; close all; clc; fclose all;

%% Specify output filename:

all_text_file = dir('*.txt');

iteration = numel(all_text_file);

b = 1;

textfilenameout =['Final_nvt_loop_' num2str(b) '.txt'];

fid2 = fopen(textfilenameout,'w');

%% LAMMPS INFO:

timestep = 0.5; % in fs:

thermonvt = 200; % 200 steps:

eachloop = 100000; % runnvt_eachloop:

thermo_disp = eachloop/thermonvt; % output data in thermo number times:

%%

for i=1:iteration

textfilenamein =['disp_nvt_' num2str(i) '.txt'] %filename from which data will be copied

fid1 = fopen(textfilenamein,'r')

str = textscan(fid1,'%s','Delimiter','\n');

%row_length = length(str{1});

fclose(fid1);

% Condition applied to taken out;

if i ==1

k = 28; % line from which data need to takeout: step 0 included here;

l = k+thermo_disp; % line upto which data need to takeout:

else

k = 22; % line from which data need to takeout: step 0 not included here;

l = k+thermo_disp-1; % line upto which data need to takeout:

end

str2 = str{1}(k:l);

fid2 = fopen(textfilenameout,'a');

fprintf(fid2,'%s\n', str2{:});

fclose(fid2);

end

fclose all;

% End

Answer 2

[Monisha Nalluru]

For first step of converting the txt files into excel files use readtable and writetable functions. Loop all the required files and perform the operation.

As an example

n=100; % number of txt files
for i=1:n
    filename=["one_" num2str(i) ".txt"];
    T=readtable(filename);
    outputfilename=["high_" num2str(i) ".xlsx"];
    writetable(T,outputfilename)
end

For calculating the average use mean function on required column and store that in new file using writetable, writecell