xlsread converting TRUE to 1

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

[Oleg Komarov]

Why would you want to preserve 'true' as a string? I think it's more convenient to have it in boolean format. Can you give a counterexample?

[Mike Bystedt]

The data is actually getting written out into a script file.

The functions getting called specifically require a string value of TRUE. But it's getting lost in the translation from xlsread.

[Mike Bystedt]

Of course there are numeric values as well, so I can't simply convert a 1 or 0 back to TRUE and FALSE...

Answer 1

[Oleg Komarov]

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:

1. in Excel, select the whole column with the boolean TRUE or FALSE
2. under the tab panel Data > Text To Columns > Next > Next
3. select Text (Column data format) > Finish
4. use [a,b,raw] = xlsread(...)

[owr]

Nice! Not even my question but I was getting frusturated trying to figure this out.

Answer 2

[owr]

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.

[Mike Bystedt]

Hmmm. I formatted the entire sheet to use "text" and I'm still getting the same issue...

[owr]

Sorry, was hoping I would have more tiem yesterday to elaborate further - never happened. I had the same "sticky" format issue, just changing the format of the pre-populated cells to text and/or copying pasting data into ranges of cells set to format "text" didnt work for me either. Try taking a brand new spreadsheet, format a range as text, and then write in a few test "true" and "false" and read these into MATLAB. You can tell if the logical XL format kicks in if it converts your a lowercase "true" into "TRUE". This is what convinced me that the issue is an automatic formatting issue with XL. As far as the real problem of converting your already existing data into a non XL-logical format, I havent figured that out yet. Id try cutting and pasting your data into a "text" formatted range in XL with "use destimation format" or someting similar. Im not an expert in XL and always get frusturated in how it assumes I want to do something other than what Im trying to do. Good luck.

[owr]

Yuo can try the "TEXT" function in Excel. If you have boolean "TRUE" in cell A1, type =TEXT(A1,"") in B1 and repeat this formula for your whole column of TRUE/FALSE. When you xlsread the B column it comes into ML correctly.

[Mike Bystedt]

Thank you "owr" and "Image Analyst" for responding.

I thought I was crazy for a while and just completely doing some inadvertant casting of types that wasn't correct.

I'm still in the process, but testing out all theories here.

The issue IS with the Excel formatting. No question.

I have already tried setting a blank entire sheet and setting the format to TEXT. Then I copied and pasted the values from my original into the new fresh one. xlsread still converted them.

But when I open the "fresh", "TEXT formatted" sheet and simply typed in a TRUE. xlsread retained it. So I'm still iffy about the copy and paste. It remains a problem.

My main issue is that there seems to be some ways to handle this, but that my users are preparing data from customers using an Excel "template" that I have created. I release the Matlab code, but can't quite be sure if my engineers doing a cut and paste of the data will work. As I won't be there to "massage" the input files...

Thanks to both of you for your input! And I will report back on an Excel flow that works.

[Oleg Komarov]

Doesn't my method work?

[Mike Bystedt]

Oleg. It works. It's just that I'm not preparing the data myself. I can give my users a template with some column headers, and have the whole sheet formatted as text, but as soon as they copy/paste into it, Excel is preserving their "generic" format properties. I've spent the last 2 hours looking specifically at the Excel Q/A and no user can seem to get around this factor without writing special 3rd party code to "fix" the format once the paste has been done.

Your gent's input has been great. But I had to resort to finding other "keys/clues" in my data to determine when I know that Excel is changing my TRUE and FALSE into 1's and 0's. Then I switch them back before printing out my statements into my script file.

So no perfect solution to this issue. And I don't think that MicroSoft is going to change their methods of how they handle copy and paste within their Excel spreadsheets.

Thanks to all. Your help was greatly appreciated.

-Mike

[Image Analyst]

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?

Answer 3

[Image Analyst]

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');

[Mike Bystedt]

Problem is that I can't control the customer data. I'm just processing it... Works great if the are "multiple" values.

ie, 'TRUE, COLOR, BLAH'

But the 'TRUE' all by itself keeps getting converted.

-Mike

[Image Analyst]

Oh, sorry - you're using xlsread - I misread and thought you were trying to use xlswrite. So, did you take all three outputs of xlsread()? Especially the "raw" cell array?

[Mike Bystedt]

Hey thanks for the input.

Yeah, I'm using the raw data. I looked at Excel issues and found that sometimes if you update the cells to a different format (ie. generic to text), sometimes they are "sticky" and stay. Especially numeric, but I thought that may be the culprit based on another response above.

So I created a brand new sheet. Made all of the cell's format to be "text", and then copy and pasted the old into the new. xlsread is still converting the darn "TRUE" into 1's.

Maybe I have to use import or something (as maybe it's keeping the old properties of the original cells somehow).

I'm very new to Matlab and importing stuff from Excel.

(One month). Although I've got 20 years of programming under my belt, some of these nuisances of Matlab are throwing me off big time.

Again, thanks for any advice. Very much appreciated.

-Mike

[Image Analyst]

I'll see if I have time to make up my own spreadsheet to try it tomorrow.