Is it possible to configure data in a excel file from matlab? If so, How can I do it?

for example, I have an excel file with data in it, i would like to delete the first 5 rows in the first 14 columns.

Also instead of having:

2013 094 22:57:13 245387 5.002 0.006 4.992 0.001 -0.001 -0.005 7.99 5 2 0

I would like to have this in my excel file in every row:(notice i dont want the colons nor do i want the zero at the end):

2013 094 22 57 13 245387 5.002 0.006 4.992 0.001 -0.001 -0.005 7.99 5 2

How can I go about allowing matlab to do such modifications on an excel file and then save it.

thanks in advance,

Cordelle

Answer 1

[Evan]

Do you want the timestamp (22:57:13) to be split into separate rows or placed all in the same column?

Either way, a combination of xlsread and xlswrite, combined with some quick matrix modifications, will be all you'll need.

If you're not concerned about the datatypes in MATLAB (doesn't look like you are), you can pull the data in as a cell array without respect to format by specifying a third output argument to xlsread:

[~,~,rawData] = xlsread('my_file.xlsx',...)

After that, you just have to select the extent of the array you want to keep. If you don't want the first 5 rows by 14 columns of your excel data, set them to blank:

rawData(1:5,1:12) = {[]};

Finally, if you want to parse out the timestamps, use strsplit with the colon (:) as your delimiter.

for i = 1:size(rawData,1)
  rawData{i,3} = strsplit(rawData{i,3},':')
end

Alternatively, if you just want to replace the colons with spaces, use regexprep:

for i = 1:size(rawData,1)
  rawData{i,3} = regexprep(rawData{i,3},':',' ')
end

Once you've made all the changes you want, just write the data back to excel:

xlswrite('my_file.xlsx',rawData)

[Cordelle]

Thank you for the well thought out answer, i feel like i am very close to solving my problem but when i implemeted it this error message occured:

Error using strsplit (line 81)

First input must be a string.

Error in reapassdataexample*bold*

pushbutton1_Callback (line 88)

    c{i,3} = strsplit(c{i,3},':');

[Evan]

Oops, I forgot that reading in the "raw" data from excel frustratingly does not return timestamps as strings but as date numbers. In this case, though, it actually makes things easier.

Instead of using strsplit, you can just convert the numerical data to a time string with space separators instead of colons. So you wont even need the loop. Instead, you can just do the following when you read in the data:

rawData(:,3) = cellstr(datestr([rawData{:,3}],'HH MM SS'))

This, of course, is assuming you want to place each timestamp back into a cell rather than splitting it out into three.

[Cordelle]

yes, I am trying to split the time stamp into three different cells. I also tried implementing what you previous suggested last night and an error message occured:

Subscripted assignment dimension mismatch.

I do not know what matlab is refering too?

Also, I tried this code: c(:,3) = cellstr(datestr(c{:,3},'HH MM SS'));

now the error message that occurs is:

Error using datestr (line 126)

Too many input arguments.

[Evan]

The "too many inputs input arguments" error is occurring because you're leaving out the brackets [ ] around c:

c(:,3) = cellstr(datestr([c{:,3}],'HH MM SS'));

I'm not sure about your other error. What line of code causes it?

And if you want to split into three separate cells, you'll have to do another step after that in which you operate over each cell in the timestamps column and split it into three cells either with strsplit or regexp.

[Cordelle]

The error is coming from here:

Error in reapassdataexample>pushbutton1_Callback (line 88)

c(:,3) = cellstr(datestr([c{:,3}],'HH MM SS'));

Answer 2

[Image Analyst]

Evan's answer says to read in the file, modify the data, and write it back out. This is the easiest way. If you want to do it "in place" and faster, then you can use ActiveX. While more complicated, it will be faster, especially if you need to do this in a loop where you need to do it many times, since it won't have to launch Excel twice and shutdown Excel twice for each and every file - you just open Excel once, process all the files, and shutdown Excel once. Opening Excel dozens of times can bring your program to its knees. If you want to do your processing in a loop over many files, then search this forum for ActiveX. I know I've given demos many times on it.

Answer 3

[Michal Kotze]

Hi

Use Excel COM server

handel_Excel = actxserver('Excel.Application')

The help file on this can be found at the following link.

http://www.mathworks.com.au/help/matlab/ref/actxserver.html

This will allow you to control Excel directly from Matlab.

Keep well and regards