MATLAB Answers

mb12345
0

Import an Excel File

Asked by mb12345
on 24 Sep 2017
Latest activity Commented on by Ahsan Mehmood on 16 Jun 2019
Hi,
I have given an Excel table. This Excel table I have imported with "Import Data" and saved as a cell array as "generate function". Now I want to asign the content of the imported file to a variable. Can I do that with: imp = importfile_1(file path of the created import file);
The problem is that it doesn't work atm. The entries in the variable are not equal to the excel entries.
At the end I want to have a two dimensional array with the entries of the Excel file.
I hope one of you can help me.

  4 Comments

Show 1 older comment
mb12345
on 24 Sep 2017
-------------------------------------------
imp=importfile('C:\Users\UserX\Desktop\filetoopen');
-------------------------------------------
The file "filetoopen" is a "MATLAB Code"-File. If I want to open it with the code on top matlab says: "Undefined function 'importfile' for input arguments of type 'char'." And the question is now how I can save the "filetoopen" in an array.
How I can split the content I know now. For that I can use some options in the menu "Import Data". There I can say matlab should split the text after each ".
Cedric Wannaz
on 24 Sep 2017
The problem is that none of us seems to understand what you are trying to achieve overall. So we should start from the beginning: if you are trying to read the content of a file and extract part of this content, could you attach this file to your post? The is a staple icon for this purpose in the editor. Then tell us what it is that you want to extract, and for what purpose.
mb12345
on 25 Sep 2017
Ok, it could be that I have confused you.
Let's say it again:
I have given an Excel-File. In this file the first column has a text in it. (see attachment) I want to pick out 2 words of each row (but thats another problem and should not be part of this question).

Sign in to comment.

4 Answers

Cedric Wannaz
Answer by Cedric Wannaz
on 25 Sep 2017
Edited by Cedric Wannaz
on 25 Sep 2017
 Accepted Answer

I think that I am starting to see what you did, and it is not something that we generally do actually, hence the misunderstanding.
Using the Import Data tool (and not the function), you loaded the content of an Excel workbook. Then you asked the tool to generate a function for you. Now the tool doesn't generate a function with the data. It generates a function for loading the data from the same Excel file that you initially opened, to allow you to bypass this step the next time that you want to read the data. What this function takes as an argument is the path/name of the original Excel file. So if you opened file MyData.xlsx with the Import Data tool, the correct call for loading the data from the same file is
data = importfile( 'MyData.xlsx' ) ;
or
data = importfile( 'C:\ ... whatever path ..\MyData.xlsx' ) ;
Now we never do all this with the import data tool. Instead we call directly the XLSREAD function (you can check that the importfile script does that too):
[~, data] = xlsread( 'MyData.xlsx' ) ;
where the first output of XLSREAD is a numeric array that we discard with ~ because you have no numeric data in your workbook, and the second contains the text.
If you don't want to read the original Excel file next time (because it is slow), you can now save the cell array data into a MAT-File using SAVE, and load it using LOAD (you'll have to read the doc to see how it works).
Apparently, you want to split each line into words, which you can do with STRSPLIT. You can try on the content of the first cell of data:
strsplit( data{1}, ' ' )
and see that you have a cell array of words. Then you can think about implementing e.g. a loop over all cells of data and store the words in another cell array for example.

  14 Comments

mb12345
on 28 Sep 2017
Now everything works perfect. All the words I wanted to take out are now in an cell array: Ergebnis{j,1].
But now I have one little problem. All the words are written in ". That's because the words are also wrtten in " in the excel-file.
So for example in one cell of Ergebnis stands: "wordIwant"
I just want: wordIwant
So I have to delete the first and last char.
I tried it with:
Ergebnis{j,1} = left(Ergebnis{j,1}, len(Ergebnis{j,1}) - 1);
But Matlab says: Undefined function or variable 'len'.
Any ideas? :)
Walter Roberson
on 28 Sep 2017
Ergebnis{j,1} = Ergebnis{j,1}(2:end-1);
Cedric Wannaz
on 28 Sep 2017
Almost ;-) MATLAB has functions LENGTH and NUMEL. Yet, you could works on this differently, by replacing " characters when they exist (so you don't shorten words if they are not double-quoted) by empty strings. You can use e.g. STRREP for this, which can operate on the content of flat (non-nested) cell arrays:
>> words = {'"Hello"', 'World', '"Happy"'} ;
>> words = strrep( words, '"', '' )
words =
1×3 cell array
'Hello' 'World' 'Happy'
where you can see that 'Happy' was not shortened the way it would have been if we had suppressed the first and the last characters.
Once the code works as desired, don't forget to accept an answer in this thread.

Sign in to comment.


Azzi Abdelmalek
Answer by Azzi Abdelmalek
on 24 Sep 2017

Use xlsread function

  3 Comments

mb12345
on 24 Sep 2017
Thank you for your answer. But the problem is, that I have to format the excel table before I save it as "import data". Because of that to import the excel file directly is not possible.
(The problem is that the text in the excel file is written in one column. But I need all the words in separate columns.)
Image Analyst
on 24 Sep 2017
To format the Excel worksheet before importing it into MATLAB, you need to use ActiveX. That will let you do things like format the font and how many decimal places are showing, etc.
mb12345
on 24 Sep 2017
Thanks, but I think I prefer to do it with MatLab. I don't know how to use ActiveX. Thats not the problem atm. The problem is how I can save the content of an "MATLAB Code"-File in an array.

Sign in to comment.


Image Analyst
Answer by Image Analyst
on 24 Sep 2017

Looks like you forgot the extension. Try:
fullFileName = 'C:\Users\UserX\Desktop\filetoopen.xlsx'
[~, ~, imp] = xlsread(fullFileName);
Or if the data has the same data type in every column, you could use readtable():
imp = readtable(fullFileName)

  15 Comments

mb12345
on 27 Sep 2017
Thank you for your answer. I decided to take Cedrics way to solve the problem. And all is clear for now. If I have a question with the further code I will comment again. :)
mb12345
on 28 Sep 2017
Now everything works perfect. All the words I wanted to take out are now in an cell array: Ergebnis{j,1].
But now I have one little problem. All the words are written in ". That's because the words are also wrtten in " in the excel-file.
So for example in one cell of Ergebnis stands: "wordIwant"
I just want: wordIwant
So I have to delete the first and last char.
I tried it with:
Ergebnis{j,1} = left(Ergebnis{j,1}, len(Ergebnis{j,1}) - 1);
But Matlab says: Undefined function or variable 'len'.
Any ideas? :)
Ahsan Mehmood on 16 Jun 2019
use length instead of len

Sign in to comment.


mb12345
Answer by mb12345
on 28 Sep 2017

Now everything is working.
Thank you very much!

  1 Comment

Cedric Wannaz
on 28 Sep 2017
Awesome!

Sign in to comment.