xlsread converting TRUE to 1
4 views (last 30 days)
Show older comments
Does anyone have a work around for when xlsread reads in the raw data and converts the string TRUE into a 1???
It appears that it is doing automatic conversion to boolean, when I need to keep it as a string/char value. This doesn't happen when there are other characters in the field. I'm storing the data into cell arrays, but cannot seem to preserve a string of TRUE from an Excel spreadsheet.
Thanks,
-Mike
3 Comments
Accepted Answer
Oleg Komarov
on 13 Mar 2012
What I found so far:
- typing in excel true converts the value to 1 (boolean true) if any numeric or general format is selected
- typing in excel true when the format was preselected to text, keeps the string (thus importing with xlsread keeps the string)
- having typed true with numeric or general format and then converting to text format does not affect the saved value unless you press F2 then Enter. Unfortunately this last operation is aplpicable to single cells.
The solution:
- in Excel, select the whole column with the boolean TRUE or FALSE
- under the tab panel Data > Text To Columns > Next > Next
- select Text (Column data format) > Finish
- use [a,b,raw] = xlsread(...)
1 Comment
owr
on 13 Mar 2012
Nice! Not even my question but I was getting frusturated trying to figure this out.
More Answers (2)
owr
on 12 Mar 2012
Interesting - I never ran into this before but just reproduced it myself. I dont think the issue is with MATLAB, but with Excel. If the cells are formatted (in Excel) as "text" rather than "general", this doesnt seem to happen.
7 Comments
Image Analyst
on 13 Mar 2012
So you mean that the users pasted stuff in there, like the word TRUE, and Excel did the conversion to 1, and it got saved as a 1? So when you open it up again in Excel it should be 1 not TRUE. If so, then that's an Excel issue that happens even before MATLAB is involved in any way. Is that what you're saying?
Image Analyst
on 12 Mar 2012
Make up a cell array, like this:
ca = {'True','False'; 12,98; 13,99; 14,97};
xlswrite('deleteMe.xlsx', ca, 1, 'E1');
The only trick is that you have to have the same number of text cells as numerical cells. Note that the above example (adapted from the help for xlswrite) has exactly two columns. But you can have empty cells if you want, like this:
ca = {'True',''; 12,98; 13,99; 14,97};
xlswrite('deleteMe.xlsx', ca, 1, 'E1');
4 Comments
Image Analyst
on 13 Mar 2012
I'll see if I have time to make up my own spreadsheet to try it tomorrow.
See Also
Categories
Find more on Data Import from MATLAB in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!