Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
Importing .xlsx files in parallels

Subject: Importing .xlsx files in parallels

From: Nathan

Date: 1 Mar, 2013 01:58:09

Message: 1 of 9

Hi,

I am operating Matlab 7.11.0 windows version through parallels on my Mac. I am working on a data set that automatically outputs a separate .xlsx file for each trial, and each file has a set format, which includes several lines of header. I dont specifically need any of this header data, but I do want to be able to systematically import each file and take out the data I need. The problem is that I have Microsoft Office for mac, and so it seems that matlab wont let me import .xlsx files, nor will it let me import .xls files with and character data in it. Is there any way to do this without having to manually go through each .xlsx file, delete the header, then save as a .xls file?

Thanks,
Nathan

Subject: Importing .xlsx files in parallels

From: Gadi Reinhorn

Date: 1 Mar, 2013 17:16:35

Message: 2 of 9

The functionality you need was added in R2012a:
http://www.mathworks.com/help/releases/R2012a/techdoc/rn/bs7oakc-1.html#btau8hq

xlsread Reads XLSX Files on All Platforms

The xlsread function now reads data from XLSX files on all platforms,
including support for specifying the range and worksheet number.
Previously, this functionality was available only on Microsoft Windows
systems with Excel® software.


Gadi

Subject: Importing .xlsx files in parallels

From: Ryan

Date: 8 Mar, 2013 19:48:07

Message: 3 of 9

I hate Macs so much!! What exactly is the problem? You need to convert a bunch of XLSX files to XLS files b/c you don't have Matlab 2012a? I can crate an Excel Macro for you to go into all XLSX files in a specific folder, and convert all files to XLS files. Will that do it for you?

Regards,
Ryan--

Gadi Reinhorn <greinhorn@mathworks.com> wrote in message <5130E273.8090508@mathworks.com>...
> The functionality you need was added in R2012a:
> http://www.mathworks.com/help/releases/R2012a/techdoc/rn/bs7oakc-1.html#btau8hq
>
> xlsread Reads XLSX Files on All Platforms
>
> The xlsread function now reads data from XLSX files on all platforms,
> including support for specifying the range and worksheet number.
> Previously, this functionality was available only on Microsoft Windows
> systems with Excel® software.
>
>
> Gadi

Subject: Importing .xlsx files in parallels

From: Ryan

Date: 8 Mar, 2013 20:00:08

Message: 4 of 9

Run this code to convert all .XSLX files in a specific folder, of your choosing, to .XSL files.

Sub Convert_xls_Files()

Dim strFile As String
Dim strPath As String

    With Application
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
'Turn off events, alerts & screen updating

        strPath = "C:\Users\rshuell\Desktop\Excel_Files\"
        strFile = Dir(strPath & "*.xls")
'Change the path as required

    Do While strFile <> ""
        Workbooks.Open (strPath & strFile)
        strFile = Mid(strFile, 1, Len(strFile) - 5) & ".xls"
        ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close True
        strFile = Dir
    Loop
'Opens the Workbook, set the file name, save in new format and close workbook

    With Application
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
'Turn on events, alerts & screen updating

End Sub

Try to import the .XLS files into your Matlab.



You won't be able to load them all at once but you could easily use a for-loop to process them one at a time. Something like this:

source_dir = 'path/to/source/'
dest_dir = '/path/to/dest'
source_files = dir(fullfile(source_dir, '*.xls'));
for i = 1:length(source_files)
  data = xlsread(fullfile(source_dir, source_files(i).name)));
  #do something with data
  xlswrite(fullfile(dest_dir, source_files(i).name)));
end



"Ryan" wrote in message <khdf9n$5pf$1@newscl01ah.mathworks.com>...
> I hate Macs so much!! What exactly is the problem? You need to convert a bunch of XLSX files to XLS files b/c you don't have Matlab 2012a? I can crate an Excel Macro for you to go into all XLSX files in a specific folder, and convert all files to XLS files. Will that do it for you?
>
> Regards,
> Ryan--
>
> Gadi Reinhorn <greinhorn@mathworks.com> wrote in message <5130E273.8090508@mathworks.com>...
> > The functionality you need was added in R2012a:
> > http://www.mathworks.com/help/releases/R2012a/techdoc/rn/bs7oakc-1.html#btau8hq
> >
> > xlsread Reads XLSX Files on All Platforms
> >
> > The xlsread function now reads data from XLSX files on all platforms,
> > including support for specifying the range and worksheet number.
> > Previously, this functionality was available only on Microsoft Windows
> > systems with Excel® software.
> >
> >
> > Gadi

Subject: Importing .xlsx files in parallels

From: Ryan

Date: 9 Mar, 2013 04:49:10

Message: 5 of 9

Sorry, but it looks like the last M-code sample that I posted does NOT import all Excel files in a folder. Well, at least I couldn't get it to work. This, however, does work.

% % Import all excel files in a folder into multiple Matlab variables.
% % Your variables will be num1, num2, ...etc
d = dir('C:\Users\Ryan\Desktop\Excel_Files\*.xls*');
nfiles = length(d);
for k = 1:nfiles
   n=int2str(k);
   eval(['[num' n ', txt' n ', raw' n '] = xlsread(d(k).name,1);']);
end




"Ryan" wrote in message <khdg08$80a$1@newscl01ah.mathworks.com>...
> Run this code to convert all .XSLX files in a specific folder, of your choosing, to .XSL files.
>
> Sub Convert_xls_Files()
>
> Dim strFile As String
> Dim strPath As String
>
> With Application
> .EnableEvents = False
> .DisplayAlerts = False
> .ScreenUpdating = False
> End With
> 'Turn off events, alerts & screen updating
>
> strPath = "C:\Users\rshuell\Desktop\Excel_Files\"
> strFile = Dir(strPath & "*.xls")
> 'Change the path as required
>
> Do While strFile <> ""
> Workbooks.Open (strPath & strFile)
> strFile = Mid(strFile, 1, Len(strFile) - 5) & ".xls"
> ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat:=xlOpenXMLWorkbook
> ActiveWorkbook.Close True
> strFile = Dir
> Loop
> 'Opens the Workbook, set the file name, save in new format and close workbook
>
> With Application
> .EnableEvents = True
> .DisplayAlerts = True
> .ScreenUpdating = True
> End With
> 'Turn on events, alerts & screen updating
>
> End Sub
>
> Try to import the .XLS files into your Matlab.
>
>
>
> You won't be able to load them all at once but you could easily use a for-loop to process them one at a time. Something like this:
>
> source_dir = 'path/to/source/'
> dest_dir = '/path/to/dest'
> source_files = dir(fullfile(source_dir, '*.xls'));
> for i = 1:length(source_files)
> data = xlsread(fullfile(source_dir, source_files(i).name)));
> #do something with data
> xlswrite(fullfile(dest_dir, source_files(i).name)));
> end
>
>
>
> "Ryan" wrote in message <khdf9n$5pf$1@newscl01ah.mathworks.com>...
> > I hate Macs so much!! What exactly is the problem? You need to convert a bunch of XLSX files to XLS files b/c you don't have Matlab 2012a? I can crate an Excel Macro for you to go into all XLSX files in a specific folder, and convert all files to XLS files. Will that do it for you?
> >
> > Regards,
> > Ryan--
> >
> > Gadi Reinhorn <greinhorn@mathworks.com> wrote in message <5130E273.8090508@mathworks.com>...
> > > The functionality you need was added in R2012a:
> > > http://www.mathworks.com/help/releases/R2012a/techdoc/rn/bs7oakc-1.html#btau8hq
> > >
> > > xlsread Reads XLSX Files on All Platforms
> > >
> > > The xlsread function now reads data from XLSX files on all platforms,
> > > including support for specifying the range and worksheet number.
> > > Previously, this functionality was available only on Microsoft Windows
> > > systems with Excel® software.
> > >
> > >
> > > Gadi

Subject: Importing .xlsx files in parallels

From: Ryan

Date: 9 Mar, 2013 14:27:09

Message: 6 of 9

So, the last code sample that I posted will loop through several Excel files in a folder, and import each Excel file into a separately matrix for each, respectively.

A while back I tried to figure out a way to import several Excel files in a folder, and basically concatenate all files into one single matrix in Matlab, sort of like a Union Query in a database. Well, I never figured out how to do that in Matlab, but you can run the VBA code in the links below to do that in Excel, and then read that one large (concatenated) file into Matlab.

Here's the links:
http://www.rondebruin.nl/copy3.htm
http://www.rondebruin.nl/fso.htm
http://www.rondebruin.nl/merge.htm


"Ryan" wrote in message <khef06$2c0$1@newscl01ah.mathworks.com>...
> Sorry, but it looks like the last M-code sample that I posted does NOT import all Excel files in a folder. Well, at least I couldn't get it to work. This, however, does work.
>
> % % Import all excel files in a folder into multiple Matlab variables.
> % % Your variables will be num1, num2, ...etc
> d = dir('C:\Users\Ryan\Desktop\Excel_Files\*.xls*');
> nfiles = length(d);
> for k = 1:nfiles
> n=int2str(k);
> eval(['[num' n ', txt' n ', raw' n '] = xlsread(d(k).name,1);']);
> end
>
>
>
>
> "Ryan" wrote in message <khdg08$80a$1@newscl01ah.mathworks.com>...
> > Run this code to convert all .XSLX files in a specific folder, of your choosing, to .XSL files.
> >
> > Sub Convert_xls_Files()
> >
> > Dim strFile As String
> > Dim strPath As String
> >
> > With Application
> > .EnableEvents = False
> > .DisplayAlerts = False
> > .ScreenUpdating = False
> > End With
> > 'Turn off events, alerts & screen updating
> >
> > strPath = "C:\Users\rshuell\Desktop\Excel_Files\"
> > strFile = Dir(strPath & "*.xls")
> > 'Change the path as required
> >
> > Do While strFile <> ""
> > Workbooks.Open (strPath & strFile)
> > strFile = Mid(strFile, 1, Len(strFile) - 5) & ".xls"
> > ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat:=xlOpenXMLWorkbook
> > ActiveWorkbook.Close True
> > strFile = Dir
> > Loop
> > 'Opens the Workbook, set the file name, save in new format and close workbook
> >
> > With Application
> > .EnableEvents = True
> > .DisplayAlerts = True
> > .ScreenUpdating = True
> > End With
> > 'Turn on events, alerts & screen updating
> >
> > End Sub
> >
> > Try to import the .XLS files into your Matlab.
> >
> >
> >
> > You won't be able to load them all at once but you could easily use a for-loop to process them one at a time. Something like this:
> >
> > source_dir = 'path/to/source/'
> > dest_dir = '/path/to/dest'
> > source_files = dir(fullfile(source_dir, '*.xls'));
> > for i = 1:length(source_files)
> > data = xlsread(fullfile(source_dir, source_files(i).name)));
> > #do something with data
> > xlswrite(fullfile(dest_dir, source_files(i).name)));
> > end
> >
> >
> >
> > "Ryan" wrote in message <khdf9n$5pf$1@newscl01ah.mathworks.com>...
> > > I hate Macs so much!! What exactly is the problem? You need to convert a bunch of XLSX files to XLS files b/c you don't have Matlab 2012a? I can crate an Excel Macro for you to go into all XLSX files in a specific folder, and convert all files to XLS files. Will that do it for you?
> > >
> > > Regards,
> > > Ryan--
> > >
> > > Gadi Reinhorn <greinhorn@mathworks.com> wrote in message <5130E273.8090508@mathworks.com>...
> > > > The functionality you need was added in R2012a:
> > > > http://www.mathworks.com/help/releases/R2012a/techdoc/rn/bs7oakc-1.html#btau8hq
> > > >
> > > > xlsread Reads XLSX Files on All Platforms
> > > >
> > > > The xlsread function now reads data from XLSX files on all platforms,
> > > > including support for specifying the range and worksheet number.
> > > > Previously, this functionality was available only on Microsoft Windows
> > > > systems with Excel® software.
> > > >
> > > >
> > > > Gadi

Subject: Importing .xlsx files in parallels

From: dpb

Date: 9 Mar, 2013 15:44:39

Message: 7 of 9

On 3/9/2013 8:27 AM, Ryan wrote:
...

> A while back I tried to figure out a way to import several Excel files
> in a folder, and basically concatenate all files into one single matrix
> in Matlab, sort of like a Union Query in a database. Well, I never
> figured out how to do that in Matlab,...

What seemed to be the problem? Doesn't seem like any big
trick...assuming, of course, the data within the files is commensurate
such as to be amenable to such concatenation. A set of arbitrary file
formats would, of course, be much more problematical.

--

Subject: Importing .xlsx files in parallels

From: Marc

Date: 10 Mar, 2013 07:57:08

Message: 8 of 9

"Ryan" wrote in message <khdf9n$5pf$1@newscl01ah.mathworks.com>...
> I hate Macs so much!! What exactly is the problem? You need to convert a bunch of XLSX files to XLS files b/c you don't have Matlab 2012a? I can crate an Excel Macro for you to go into all XLSX files in a specific folder, and convert all files to XLS files. Will that do it for you?
>
> Regards,
> Ryan--
>
> Gadi Reinhorn <greinhorn@mathworks.com> wrote in message <5130E273.8090508@mathworks.com>...
> > The functionality you need was added in R2012a:
> > http://www.mathworks.com/help/releases/R2012a/techdoc/rn/bs7oakc-1.html#btau8hq
> >
> > xlsread Reads XLSX Files on All Platforms
> >
> > The xlsread function now reads data from XLSX files on all platforms,
> > including support for specifying the range and worksheet number.
> > Previously, this functionality was available only on Microsoft Windows
> > systems with Excel® software.
> >
> >

Is this really the op's problem... Plus your hatred for Macs seems misplaced.

From the op's post it looks like he is running Parallels for Matlab BUT has Office for Mac.

If he had a Windows version of Office, I am not sure he would have any problems. Since his Matlab version is looking for Excel under Windows, it probably does not see Excel in Mac.

As for Mac vs Windows, this is really not the forum. I was under the impression that most of the Matlab/excel issues in Mac were from Microsoft's side. 2008 Office for Mac had no support for VBA and finally they brought it back. Not sure the Mathworks is to blame for Microsoft being lazy with their applications on Mac OS.

Anyway, Matlab 2012b works well (not perfect) with Office 2011 for Mac from my early tests. Not running parallels. Matlab and Office both being Mac OS versions.

> > Gadi

Subject: Importing .xlsx files in parallels

From: Steven_Lord

Date: 11 Mar, 2013 02:46:49

Message: 9 of 9



"Ryan " <ryanshuell@gmail.com> wrote in message
news:khef06$2c0$1@newscl01ah.mathworks.com...
> Sorry, but it looks like the last M-code sample that I posted does NOT
> import all Excel files in a folder. Well, at least I couldn't get it to
> work. This, however, does work.
>
> % % Import all excel files in a folder into multiple Matlab variables.
> % % Your variables will be num1, num2, ...etc
> d = dir('C:\Users\Ryan\Desktop\Excel_Files\*.xls*'); nfiles = length(d);
> for k = 1:nfiles
> n=int2str(k);
> eval(['[num' n ', txt' n ', raw' n '] = xlsread(d(k).name,1);']);

Don't do this! See question 6 in the Programming section of the newsgroup
FAQ for an explanation why this is a Bad Idea and alternatives you should
use instead. Question 12 in that same section may also be of interest.

http://matlab.wikia.com/wiki/FAQ

*snip*

--
Steve Lord
slord@mathworks.com
To contact Technical Support use the Contact Us link on
http://www.mathworks.com

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us