How can I convert my .txt files to .xls using xlswrite

Hi, I have several .txt files in a folder that all have the same header and similar data:

# Data format: " %int(Month)/%int(Day)/%int(Year), %int(Hours):%int(Minutes):%float(Seconds), %float(OrientQuatX)"
 11/03/18, 06:11:25.772522, -0.43956,0.53172,0.41249,-0.59490, 0.00465,0.03840,0.02094, 0.76172,0.28125,0.14063, 0.06372,0.42444,-0.78574,
 11/03/18, 06:11:25.791809, -0.44145,0.53031,0.40810,-0.59779, 0.00582,0.03607,0.01047, 0.89063,0.29297,0.10547, 0.07922,0.42561,-0.78807,

and several other float columns. I already have most of my code to analyze the data and would like to convert all the files at the beginning of the code before the data anlysis. I know how to loop through through all my files, however I'm having trouble actually getting the data into an xls file.

I have tried using xlswrite but I'm having diffuculty getting the data in the txt file (including the header) into the matrix data :

sample_file = fileread('C:\Users\J\sample.txt') ;
data   = textscan(sample_file, '%s %s %f %f %f %f %f %f %f %f %f %f %f %f %f', 'delimiter', ',') ;
xlswrite('C:\Users\J\sample.xls', data) ;

I have looked through other threads, one of which uses num2cell() to convert the array to a cell array, but how can I make sure it includes all my data (including the header)?

Thanks for any help

Answer 1

[Walter Roberson]

I am a bit confused about your header. You seem to show a line of header, but the format you used for textscan was as if you no header line and two columns of text at the beginning of each line. I have coded according to the format string you gave, which might have been correct for your actual file.

sample_file = fileread('C:\Users\J\sample.txt') ;
data   = textscan(sample_file, '%s %s %f %f %f %f %f %f %f %f %f %f %f %f %f', 'delimiter', ',') ;
nrow = size(data{1},1);
d2 = cell(nrow, 15);
d2(:,1) = data{:,1};  % %s format already gives cell
d2(:,2) = data{:,2};  % %s format already gives cell
% %f format gives a numeric vector. Combine all of the numeric vectors into a single array and convert the numeric array to cell.
d2(:,3:end) = num2cell( cell2mat(data(:,3:end)) );
xlswrite('C:\Users\J\sample.xls', d2) ;

[Judy S]

Hi Walter, thanks for the quick reply!

my text files are very long so I added the first few sample lines in the original post.

Since it's a comma delimiter and the header gives the type, I thought my textscan would include it, I realised it's only looking at the data.

Thanks for adding the comment in your answer, that really helped. One follow up question: d2 and data are not the same size: d2 is 1-13 and data is 0-13. Could you explain why the left side is 1-13?

[Walter Roberson]

You should add 'HeaderLines', 1 to your textscan() call so that it skips the header line. But then it becomes necessary to process the header specially to put it back into the xls file.

I will code up the fixup later; I have something else to do at the moment.

[Walter Roberson]

{filename_in = 'C:\Users\J\sample.txt';
filename_out = 'C:\Users\J\sample.xls';
sheet = 1;

fid = fopen(filename_in, 'rt');
header = fgetl(fid);
data   = textscan(fid, '%s %s %f %f %f %f %f %f %f %f %f %f %f %f %f', 'delimiter', ',') ;
fclose(fid);

nrow = size(data{1},1);
d2 = cell(nrow, 15);
d2(:,1) = data{:,1};  % %s format already gives cell
d2(:,2) = data{:,2};  % %s format already gives cell
% %f format gives a numeric vector. Combine all of the numeric vectors into a single array and convert the numeric array to cell.
d2(:,3:end) = num2cell( cell2mat(data(:,3:end)) );

xlswrite(filename_out, {header}, sheet, 'A1');
range = sprintf('A2:O%d', nrow+1);
xlswrite(filename_out, d2, sheet, range);

Caution: the xlswrite portion of this is not tested as I do not have Windows with Excel.

If this works as planned, then it will produce a slightly different file than would be produced by using Excel's import wizard. Excel's import wizard only handles comma within strings if the field begins with " but in your case the " is after the # Data format: part, so Excel's import wizard would end up importing the first line as three fields. But the code I wrote, if it works properly, would write the header as a single cell in A1.

It would be easier if you were discarding that first line, which is in an odd format and has a different number of fields compared to the rest.

Oh yes, a question: your data ends in comma on each line, which would normally signify an empty field, but sometimes people want emptiness to be distinguished from Excel's "No Value". Do you need an empty field generated or is it okay to end with the numerics?

[Judy S]

This works great! Adds the header as A1 and the rest of the data for below it. Thanks for all your help & explanations

Answer 2

[Guillaume]

An example of a text file would be very useful. What you want to do would probably be very easy to do using the modern import and export methods rather than the old cumbersome ones. It could be as easy as:

data = readtable('C:\Users\J\sample.txt'); %readtable is often smart enough to figure the format on its own. If not it can be specified
writetable(data, 'C:\Users\J\sample.xls');

[Walter Roberson]

Unfortunately the header line is a different number of columns, and consists of things that are not valid variable names. writetable() is not smart enough to use the VariableDescriptions property when creating the headers for the columns; it is going to use the mangled strings from the header together with invented variable names.

[Guillaume]

Well, it's trivial to tell readtable not to bother with the header and if that header is required write it separately. readtable would still make the parsing and writing of the rest of the file trivial:

data = readtable('C:\Users\J\sample.txt', 'HeaderLines', 1, 'ReadVariableNames', false);
writetable(data, 'C:\Users\J\sample.xls', 'WriteVariableNames', false);

An actual sample text file (with only a few lines) would still be very useful to fully understand the format of the file and experiment with.

[Judy S]

Hi Guillaume, thanks for the suggestion! This does write the data into excel, but doesn't recognize my header. It's a strange header format that was generated by the software I'm using to get all this data, I just want to keep it for reference. The accepted answer above did the job of adding it as a single row to refer to.

[Guillaume]

Yes, if the header is required, as I said it would have to be written separately. If Walter's answer does what you want for the header, then this should probably work:

inputfile = 'C:\Users\J\sample.txt';
outputfile = 'C:\Users\J\sample.xls';  %I'd recommend xlsx over xls
data = readtable(inputfile, 'HeaderLines', 1, 'ReadVariableNames', false);
writetable(data, outputfile, 'WriteVariableNames', false, 'Range', 'A2');
filecontent = strsplit(fileread(inputfile), {'\n', '\r'}); 
writetable(cell2table(filecontent(1)), outputfile, 'WriteVariableNames', false, 'Range', 'A1')

The advantage of readtable is that often it can work out the format of the file on its own, so if that format changes, you don't have to do anything to the code. With textscan you'll have to adjust the format string each time.