How to convert a .txt to a .xls?

I have many tab delimited .txt files that contain text and numerical values. I need to convert these to .xls files using a matlab script. I have looked into dlmread, txtscan, etc. The problem is that it contains both text and numbers, and also that it is not a perfect table. It has some random text and numbers above the actual data. I need it to be universal so it can convert any tab delimited text file into a .xls regardless of format. So really all I need to know is how to read this type of file into Matlab. Is this even possible in Matlab? Thanks!

-Matt

Answer 1

[Walter Roberson]

fopen() the file. Start a loop. fgetl() a single line from the file. If that fails, break out of the loop. regexp() with 'split' on '\t' to split the line in to a cell string of individual fields. Loop checking all of the fields. If any character in the field is not '-', '+', 'e', 'E', 'd', 'E', '.', a leading space, a trailing space, or one of the digits, then the field is definitely a string so store it; otherwise try to str2double() the field, and if that works store the floating point value and otherwise store the string. End of loop over the fields. Save the result of converting the fields of the line. End loop over the lines.

You know have a cell array of vectors of cell arrays. To convert it to .xls, you are going to have to find the longest of the lines and pad the other lines with empty fields. Once you have all of the lines the same, length, you can convert to a cell array matrix. Once you have that, submit it to xlswrite()

Answer 2

[Fangjun Jiang]

If you don't need to process the data, there might be a different way to do it. I can open a delimited .txt file directly with Excel. All the delimited data are right in the cells of the spread sheet. You can test it manually. If that is what you want, then you can use Matlab to make this automation so it can convert multiple .txt files. Look into the Excel actxserver, all you need to do is open the text file and then save as Excel file.

[Cordelle]

@Fangjun Jiang

How can I do the automation of open a .txt file directly with Excel through matlab?