Why is xlsread not working with text file? Documentation says it should work with other any file that Excel can open.

2 次查看（过去 30 天）
Alexei
Alexei 2014-7-31
编辑： per isakson 2014-8-2
Documentation for the xlsread command says:
" On Windows® systems with Microsoft Excel software, xlsread reads any file format recognized by your version of Excel. "
So that sounds to me like xlsread should be able to open some .txt files that I exported from AFM software. The text file consists of a column header and a column of numbers written in scientific notation. I can open said text files directly in Excel if I use the Open with -> command in the right-click context menu in Windows. Excel interprets the contents cleanly, placing the header text at the top of the column and each subsequent number into a cell.
However, when I try to use xlsread to read the text file, I get the error: File my file.txt not in Microsoft Excel Format. (I substituted "my file" for the actual name.)

请先登录，再回答此问题。

采纳的回答

per isakson
per isakson 2014-8-1
编辑：per isakson 2014-8-1
My system: R20113a, 64bit, Win7, Excel2010
&nbsp
My first trial resulted in
Error using xlsread (line 247)
File c:\tmp\1.5% PVAc 500nm SS 1.5Hz SR 5nm lift 28C.116.txt
not in Microsoft Excel Format.
the line causing the error is
openExcelWorkbook(Excel, file, readOnly);
Next, I replaced .txt by .csv and tried again
Name Size Bytes Class Attributes
alldata 16385x1 2818220 cell
filespec 1x55 110 char
ndata 0x0 0 double
text 16385x1 2818220 cell
where my script is
filespec = 'c:\tmp\1.5% PVAc 500nm SS 1.5Hz SR 5nm lift 28C.116.csv';
[ndata, text, alldata] = xlsread(filespec);
whos
Why ndata is empty I cannot guess. Ok, it's the way Excel works.
&nbsp
recognized by your version of Excel &nbsp There seems to be two levels of recognizing. I tried to open the file with the two different extensions from inside Excel. With .txt the interactive Wizard was invoked and with .csv it worked automagically. However, the magic didn't recognize the "numerical data" as numeric.
&nbsp
&nbsp
&nbsp
Yes, there certainly are better ways to read this file, e.g. textscan
  2 个评论
Alexei
Alexei 2014-8-1
编辑：Alexei 2014-8-1
Thank you very much! Following your post, I also tried changing it to a .csv file, and it works beautifully. It works even better than in your case because my numeric data variable actually fills up, unlike in your case.
[data,text,raw] = xlsread(FileName);
Where FileName is 1.5% PVAc 500nm SS 1.5Hz SR 5nm lift 28C.116.csv. Running that, data shows up as a 16384x1 double in my workspace.
But, I guess I will still have to resort to using textscan() or some other function (any other suggestions?). Because the software I use makes these .txt files, and it's inconvenient to rename them all to .csv before running code.
My system: R2014a 64bit, Windows 8 Pro 64 bit, Excel 2013 64bit
per isakson
per isakson 2014-8-2
编辑：per isakson 2014-8-2
textscan is my first choice when reading text files containing some headerlines together with numerical (and text) data in columns. xlsread is not on the list. It give you more control and it is relatively fast, however it requires a tiny bit more reading of the manual. When textscan fails it is easier to understand why. importdata is a wrapper to textscan. Little demo:
>> tic, val = cssm(); toc, whos
Elapsed time is 0.063142 seconds.
Name Size Bytes Class Attributes
val 16384x1 131072 double
where
function val = cssm
filespec ...
= 'c:\tmp\1.5% PVAc 500nm SS 1.5Hz SR 5nm lift 28C.116.txt';
fid = fopen( filespec );
cac = textscan( fid, '%f', 'Headerlines', 1 );
fclose(fid );
val = cac{1};
end

请先登录，再进行评论。

更多回答（1 个）

Image Analyst
Image Analyst 2014-7-31
Try something else, like importdata() or textscan() or readtable().
  16 个评论
显示 14更早的评论
Alexei
Alexei 2014-8-1
编辑：Alexei 2014-8-1
Hmm. My Excel is 2013! (Windows 8) My Excel opens it just fine too. It's xlsread that has issues. Excel can open the text file directly without invoking any special "data import wizard" or whatever, so why can't xlsread ? When using xlsread I get the error,
File C:\Users\Master\Desktop\1.5% PVAc 500nm SS 1.5Hz SR 5nm lift 28C.116.txt not in Microsoft Excel Format.
My Matlab is also R2014a. I tried your suggestion of cutting down the file to have under 1000 lines. Still same error.
---
BUT, you changed the file to a .csv file, right? I tried doing the same thing, and it works like a charm! Just as for poster per isakson. Except it worked even better, because my numeric data filled up, as it's supposed to. I got a 16384x1 double column of data in my workspace.
So it appears that it works very well if the file is renamed to a .csv file, but keeping it as a text file has issues no matter what. Both for me and for poster per isakson. That's a shame... I will need to either use a different function altogether, or create some kind of automatic renaming script that renames all my .txt files into .csv files and then run xlsread.
Which other function do you think is best? textscan() ?

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by