Import an Excel File

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.

[Cedric]

Can you attach a slice of the original file, and explain how you would like the content to be "split" (according to your comment to Azzi's answer) ultimately?

[mb12345]

-------------------------------------------

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]

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]

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

Answer 1

[Cedric]

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.

[mb12345]

Ok, then I will try it on your way. ;)

[~, data] = xlsread( 'MyData.xlsx' ) ;

With this I get a cell array with the excel table in it. Now with strsplit I can split each row. So if I put it in a loop I will get cell arrays with only one row of the excel file.

Now I want to separate each word in the row. How can I do that? The problem is I have to read out one word after a key word. For example in each row there stands the word "now". So I have to pick out the word after "now". In the exampe the key word yould be "I". So in the first row I want "have", in the second row "need", in the third row "hope", and so on. So the goal is to say: Give me the word after "I" in each row. And then the program give me "have" "need" and "hope".

Any ideas how I can do that?

[Cedric]

Several approaches are possible. One relies only on basic programming, so that would be a good training for you, and the other relies on pattern matching. The latter is more concise and it doesn't require to split sentences into words, but it relies on regular expressions for text pattern matching, and this is not a trivial topic.

First, say you loaded your sentences into a cell array data and you wonder how to proceed from there. There is word extraction, iterating through sentences, etc, and it seems complicated. In these cases, focus on small independent tasks and see if you can achieve them. For example, are able to iterate through sentences. You check out FOR loops, how to compute the length of an vector (cell array), how to index a cell array, can you can start with

 for k = 1 : length( data )
      disp( data{k} ) ;
 end

where you just check that you are able to iterate, to access the content of the cells of cell array data, etc. Then you need to learn how to split sentences. Instead of trying to integrate this in the loop, train on e.g. the first sentence. You google, read my post, discover STRSPLIT, STRREP, STRCMP, STRCMPI, etc, see how splitting works, and try splitting a sentence that you define in the call:

 words = strsplit( 'Hello happy World', ' ' )

and you look at the output, is it a cell array, does it works, etc. Then you try to operate on the first sentence and see if it works:

 words = strsplit( data{1}, ' ' )

Ok so then it would be easy to integrate this in the loop, but the next task is finding the word after a given keyword. Browsing functions for operating on strings, you realize that STRCMP and STRCMPI could work, so you try, again with a simple example:

 words = strsplit( 'Hello happy World', ' ' ) ;
 result = strcmpi( 'happy', words )

and you see that you get what seems to be an array of flags. It is in fact a vector of logicals (true/false) that are the outcome of the comparison. Googling you find that function FIND may be a good candidate for finding the position of non-false elements of a vector, etc.

I let you train a bit with this.

The second approach would use REGEXP (and a dynamic pattern with a look-behind or tokens), but it would be counter-productive to start with that.

[mb12345]

Thanks for your answer. I am actually trying all out. But now I don't know what is wrong.

The code is:

    if double (strcmp(satz(1,:),'"Aktiv"'))
        inak=strcmp(satz(1,:),'"Aktiv"');
        del=find(inak);
        Ergebnis{j,1}=satz{1,del+1}; 
  end

The problem is that it doesn't generate the strings "inak", "del" and "Ergebnis". But strcmp finds one true (1). So why is the if skipped?

EDIT: j=1

[Walter Roberson]

[tf, del] = ismember('"Aktiv"', satz(1,:));
if tf
  Ergebnis{j,1} = satz{1,del+1};
end

Note: this only finds the first occurrence.

[Cedric]

 satz(1,:)

is a cell array made of the block of cells that correspond to row 1 of cell array satz. Is satz a 2D cell array? If it is a vector cell array, satz is enough, you don't need to index all columns of row 1 with (1,:). I will assume in the rest that satz is a 1D cell array.

 strcmp(satz,'"Aktiv"')

compares strings present in cells of the former cell array with the single static string "Aktiv". It outputs a vector of logicals that are the outputs of the comparison, with one element per cell of satz.

If satz={'Hello','"Aktiv"','World'}, the comparison outputs

 0  1  0

for [false, true, false]. Note that if satz={'Hello','Aktiv','World'}, the comparison returns 0 0 0 because no entry of satz has the " and STRCMP performs a strict comparison.

So you can check that this works, and understand what happens in your case by looking at satz. Your code can be slightly improved by storing the output of the comparison in a variable, so it is performed only once:

 flagsFound = strcmp( satz,  '"Aktiv"' ) ;
 position   = find( flagsFound ) ;
 if ~isempty( position )
    if position < numel( satz )      % Check that it was not the last word.
        disp( satz{position+1} ) ;
    end
 end

If you test finding a position when there is no match, you will see that the output of FIND is an empty array, hence the test "not is empty".

[mb12345]

>> strcmp(satz,'"Aktiv"')

ans =

Columns 1 through 14

     0     0     0     0     0     0     0     0     0     0     0     1     0     0

Columns 15 through 19

     0     0     0     0     0

So there is one 1. But the if is still skipped.

[mb12345]

>> if double (strcmp(satz,'"Aktiv"'))
        inak=strcmp(satz,'"Aktiv"');
        del=find(inak);
        Ergebnis{j,1}=satz{del+1}; end

>> inak

Undefined function or variable 'inak'.

[Walter Roberson]

The result of the strcmp() is a vector. The double() is not doing anything useful there. "if" applied to a vector is considered true only if all the members of the vectors are non-zero. If you are going to insist on using that inefficient pattern of code then you should use

inak = [];
if any( double (strcmp(satz,'"Aktiv"')) )
        inak=strcmp(satz,'"Aktiv"');
        del=find(inak);
        Ergebnis{j,1}=satz{del+1};
end

However I would instead recommend my ismember() version:

[tf, del] = ismember('"Aktiv"', satz(1,:));
if tf
  Ergebnis{j,1} = satz{1,del+1};
end

[mb12345]

So here I need (1,:) ?

[Cedric]

Look at the example that Walter or I gave (we did not use double()):

 flagsFound = strcmp( satz,  '"Aktiv"' ) ;
 position   = find( flagsFound ) ;
 if ~isempty( position )
    ...

or

 [tf, del] = ismember('"Aktiv"', satz(1,:));
 if tf
    ...

The first will spot matches and it is useful for counting them (checking if there is more than one for example). The second, as Walter says, tests if there is one (or more) occurrence.

[mb12345]

Ok, thanks. It works. I will continue my code tomorrow. If I have questions (what can be very likely) I will comment again.

[mb12345]

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]

Ergebnis{j,1} = Ergebnis{j,1}(2:end-1);

[Cedric]

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.

Answer 2

[mb12345]

Now everything is working.

Thank you very much!

[Cedric]

Awesome!

Answer 3

[Azzi Abdelmalek]

Use xlsread function

[mb12345]

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]

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]

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.

Answer 4

[Image Analyst]

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)

[mb12345]

imp = readtable(fullFileName);

MatLab says:

Error using readtable (line 143) The file extension '.m' is not recognized.

[mb12345]

[~, ~, imp] = xlsread(fullFileName);

MatLab says:

Error using xlsread (line 251) File C:\Users\UserX\Desktop\filetoopen.m not in Microsoft Excel Format.

[Image Analyst]

Well obviously you have to change "filetoopen" with the actual filename!

You're the one who gave that pseudocode name to it originally, not me, so I assumed you knew that.

OK, let's say your ACTUAL filename was "image analysis raw measurements.xlsx". So then you'd do

fullFileName = 'C:\Users\UserX\Desktop\image analysis raw measurements.xlsx'
[~, ~, imp] = xlsread(fullFileName);

[mb12345]

Thank you, but the problem is that I don't want to use the excel file. I want to use the generated matlab code (from the option "import data") . (Because in that matlab code I already have separated the words that were in one column in the excel file.) So I think this is the problem.

Have you an idea how I can import that MATLAB-Code?

[Image Analyst]

Then what kind of file is your data IN? I was under the impressions it was in an Excel workbook file with a .xlsx extension. If it has nothing to do with Excel at all, then there are a variety of other functions you can use. You might be able to use several and it just depends on what your choice is. For example, maybe one or more of these will work: importdata, readtable, dlmread, csvread, fileread, fgetl, fread, textscan, fscanf, etc.

[Walter Roberson]

You do not import code: you execute code. You use importdata to generate importfile.m and then you call

   result = importfile('AppropriateDataFileNameGoesHere.txt');

[mb12345]

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

[Image Analyst]

So, what about xlsread(), like I showed, is not working? Attach an actual workbook if you want me to try to read it in for you.

[Walter Roberson]

If you told it to generate code into filetoopen.m then to read your file whose name is stored in mystring then:

result = filetoopen(mystring);

[Image Analyst]

Try

[~, strings, imp] = xlsread(fullFileName);
columnA = strings(:, 1); % Extract column 1 (A)
% Print words
for row = 1 : length(columnA)
    thisString = columnA{k}
    words = strsplit(thisString, ' ')
    for w = 1 : length(words)
        fprintf('In row #%d, word #%d = %s\n', row, w, words{w});
    end
end

[mb12345]

@Walter: The programm says: Undefined function or variable 'filetoopen'.

@Image: It seems like your answer is (more or less) similar to Cedrics. I will try it out, ty.

[Walter Roberson]

Is C:\Users\UserX\Desktop on your MATLAB path?

And did you generate into filetoopen with no extension or into filetoopen.m ?

[mb12345]

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]

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]

use length instead of len