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

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.)

Answer 1

[per isakson]

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

[Alexei]

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]

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

Answer 2

[Image Analyst]

Try something else, like importdata() or textscan() or readtable().

[Alexei]

Alright, I will, but xlsread is supposed to work with more than only Excel files, right?? Is it not very good at it?

[Image Analyst]

Why don't you attach your text file so we can see what's wrong with it?

[Alexei]

Absolutely! Here's an example. Thanks!

[Star Strider]

FWIW, Excel 2010 (Win 8) won’t open it as an Excel file (it brings up Excel’s Text Import Wizard). Save it with an .xls extension and see if that works.

I got importdata to open it, only to find its contents to be:

    '<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">'
    '<html><head>'
    '<title>400 Bad Request</title>'
    '</head><body>'
    '<h1>Bad Request</h1>'
    '<p>Your browser sent a request that this server could not understand.<br />'
    '</p>'
    '</body></html>'

[Image Analyst]

I can't even see it. Firefox says:

Bad Request

Your browser sent a request that this server could not understand.

Probably because from what Star said, it's actually an XML or HTML file and it's trying to display it as a web page.

[Alexei]

@Star ... What does all that mean?? Are you saying that you did not actually get any data out of the file by using importdata ? Instead, you got this nonsense? Does it suggest anything to you?

Btw, I have little experience/understanding of all the various file read or file import commands that Matlab has. I've pretty much only ever used xlsread previously to import data. I'm also not really familiar with it from other languages (I only have the most basic knowledge of C++/C). So I'm basically a noob when it comes to this.

[Image Analyst]

I don't think you attached the correct file.

[Star Strider]

I have no idea what it means, other than that the file you attached it not the file you likely wanted to attach.

Choose a different file.

[Alexei]

Let me try it again. I suspect this site is screwing things up somehow. If it fails again I'll generate a download link from one of those sites like rapidshare or whatever.

[Alexei]

Yeah. Nothing that I'm doing -- it's this site...

[Image Analyst]

Paste it into the editor window then. See if that works.

[Star Strider]

I doubt it’s the TMW site. How did you try to upload your file?

----------

EDIT — I just uploaded an old Excel file that I got from somewhere, closed this comment, clicked on the file link to open the file, and it worked fine. It’s not the site here. (I’ll take the file down in a few minutes, since there’s no other reason for it to be here, other than for testing purposes.)

[Alexei]

I'm still thinking it's the site. Anyway let's try this:

https://docs.google.com/file/d/0ByNZKeXoCKhRQkZJUUpYX21QTzg/edit?pli=1

I put the same text file on google drive and enabled sharing so it's publicly accessible for anyone with the link. You can download it from the File menu. I tested downloading -- all seems fine.

---

Can you guys open it in Excel now? What do you think about my original question, regarding using xlsread to open text files that Excel can handle? Thank you so much for your help! Sorry about the diversion.

[Image Analyst]

I could read it just fine with Excel 2010 and MATLAB R2014a. I suspect you have an old version of Excel, like 2003 or something, where the number of rows was limited to 16384. Your text file has slightly over that and perhaps that's causing the problem. Can you cut the number of lines down to 100 or 1000 or so and test it? Here's my code:

[num, txt, raw] = xlsread('text.csv');

and attached is the file it successfully ran with (copied and pasted from your web site). num, txt, and raw all have exactly what you'd expect to be in them.

[Alexei]

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() ?

[Star Strider]

To rename them in a script, movefile is likely your best friend. See Examples —> Renaming a File in the Current Folder.