Code To Extract Data From .txt File to Excel

50 vues (au cours des 30 derniers jours)
Jack Morgan
Jack Morgan le 16 Mar 2020
Commenté : Jack Morgan le 19 Mar 2020
I have several text files with two columns and usually 2000+ rows (picture below). I would like to have some code to extract the value from the bottom row of the second column and put it into an Excel file in a certain place. Each of the text files has a different heading/name (same format as picture below though) and I would like to put the value from the text file into Excel under these headings. If necessary, I can create the headings myself and would be happy to just have code which outputs the same heading's value in the same column each time.
Currently I am doing this manually which takes a long time and will need doing lots more in the future.
Does anyone know how to do this and could help me? Happy to answer more questions or give more details.
Many thanks
  4 commentaires
Jack Morgan
Jack Morgan le 17 Mar 2020
Modifié(e) : Jack Morgan le 17 Mar 2020
Thank you for your reply, it is really helpful. What I have done currently is mostly what I’ve picked up googling things.
I am going to be away from my computer for the next few hours so can’t access MATLAB but I’ve attached three files. These three have very similar names but the others won’t have. All of them will be similar though in that column 1 will be a count from 1 to n and column 2 will be the data. I am only interested in the final entry.
Thank you if you’re able to help at all.

Connectez-vous pour commenter.

Réponse acceptée

Guillaume le 17 Mar 2020
As we don't yet know how to identify the files to be processed, nor how exactly the data is to be exported to excel, the below just focuses on the file import:
filelist = {'b1-moment-z-rfile.txt', 'b2-moment-z-rfile.txt', 'b3-moment-z-rfile.txt'}; %it's not clear yet how this is to be obtained
datatoexport = cell(size(filelist)); %container for data to export as this is also not clear
for filenum = 1:numel(filelist)
wholecontent = readtable(filelist{filenum}); %read whole file, readtable automatically detects formatting and header and uses the header to name the table variables
datatoexport{filenum} = wholecontent(end, :); %keep last row of table (header always come with it)
With the above, you will get a warning for each file that matlab modifies the header to make it valid variable names (it removes the ( and " and replaces the - by _). The warning can be turned off with
warning('off', 'MATLAB:table:ModifiedAndSavedVarnames');
or matlab can be told not to do the replacement (thus keeping the (" in the variable names) with the 'PreserveVariableNames', false option of readtable.
Once details of how to identify files and what export is desired, the above can be modified accordingly.
  13 commentaires
Jack Morgan
Jack Morgan le 19 Mar 2020
This worked fantastic, much better than I expected. Thank you for all your help.

Connectez-vous pour commenter.

Plus de réponses (2)

Kevin Chng
Kevin Chng le 17 Mar 2020
Accept my answer if it is working for you. Thanks
fid = fopen('12.txt','rt');
indata = textscan(fid, '%f %f', 'HeaderLines',3);
%your data is in indata
  2 commentaires
Jack Morgan
Jack Morgan le 17 Mar 2020
Modifié(e) : Jack Morgan le 17 Mar 2020
So with the following I get the value from the last row (in this case 2500) as indata.
fid = fopen('b2-moment-z-rfile.out','rt');
indata = textscan(fid, '%f %f', 'HeaderLines',2502);
%your data is in indata
What I would like is to repeat this for several text files and all the data be in a way I can copy it into Excel in one go.
Also worth noting the number of rows may change so is there a way to account for this?

Connectez-vous pour commenter.

Image Analyst
Image Analyst le 17 Mar 2020
To process a sequence of files, place your code in the middle of a for loop over files.
For code samples, see the FAQ: FAQ#How_can_I_process_a_sequence_of_files?
  2 commentaires
Walter Roberson
Walter Roberson le 17 Mar 2020
Is there any pattern that can be used to distinguish the files you want to process from the other ones you do not want to process in the same directory? For example do you want to process all of the *moment-z-rfile.txt files? If so then you can use
dinfo = dir('*moment-z-rfile.txt');
filenames = {};
numfiles = length(filenames);
for K = 1 : numfiles
thisfile = filenames{K};
[folder, basename, ext] = fileparts(thisfile);
outfile = fileparts(folder, [basename '.out']);

Connectez-vous pour commenter.


En savoir plus sur Text Data Preparation dans Help Center et File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by