MATLAB Answers

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

1 view (last 30 days)
Ankit Chauhan
Ankit Chauhan on 6 Dec 2020
Answered: Monisha Nalluru on 11 Dec 2020
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
  1 Comment
Mario Malic
Mario Malic on 6 Dec 2020
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.

Sign in to comment.

Answers (1)

Monisha Nalluru
Monisha Nalluru on 11 Dec 2020
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

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!