MATLAB Answers

dpb
0

WRITETABLE to spreadhseet mysterious failure

Asked by dpb
on 12 May 2019
Latest activity Edited by dpb
on 15 May 2019
>> writetable(tMismatch)
>> type tMismatch.txt
Fund,Acct,B_Student,SID,B_Paid,R_Student,R_Paid,R_Award,MatchStatus,R_Formula,Recipient
"ALEXANDER, HELEN WILLIAMS",A63032,Total,,482,Total,0,NaN,NOT MATCH,=0.00,GED graduate -
,,,,0,=$0.00,0,NaN,NOT BILLED,,
>>
succeeds and writes what is expected...but
>> writetable(tMismatch,'tmismatch.xlsx')
Error using writetable (line 124)
Unable to write to sheet 'Sheet1' in file 'tmismatch.xlsx'. Disable sheet protection,
disable workbook's Mark as Final option, and ensure input does not exceed cell capacity.
>> whos tMismatch
Name Size Bytes Class Attributes
tMismatch 2x11 5259 table
>> tMismatch
tMismatch =
2×11 table
Fund Acct B_Student SID B_Paid R_Student R_Paid R_Award MatchStatus R_Formula Recipient
___________________________ ________ _________ ___ ______ _________ ______ _______ ____________ _________ ________________
'ALEXANDER, HELEN WILLIAMS' 'A63032' 'Total' '' 482 'Total' 0 NaN 'NOT MATCH' '=0.00' 'GED graduate -'
[] [] [] [] 0 '=$0.00' 0 NaN 'NOT BILLED' [] []
>>
fails spectacularly with a string of messages I've no idea what causes...
Stepping through writetable to the bottom level call, everything looks as expected preparing to write the data but the write() itself errors out. I can't see why it shouldn't be able to do so...
R2017b, Win7/Pro
ADDENDUM
BTW,
xlswrite('tMismatch.xlsx',table2cell(tMismatch))
also fails but with just an "object failed" error number.

  1 Comment

dpb
on 13 May 2019
BTW, if instead of writing each donor fund result, append them and then write after another couple, the larger table including the same data for the first records is successful
I think it's a bug...will attach the first that fails and another that doesn't and submit bug report...
Alos attached the first as .mat file if anybody w/ later release installed wants to give it a go before I do to see if there's a system or version difference...

Sign in to comment.

Tags

1 Answer

Answer by Jeremy Hughes on 14 May 2019

"=$0.00"
This is probably the culprit.
The writetable function has two modes: 'UseExcel' = true/false. If you're using Excel®, then the running Excel process attempts to convert this text into a formula or literal number, but the $ is invalid in a formula, so Excel errors.
If you set UseExcel to false, it will literally write the text "=$0.00" to the file, but since there's no Excel process, it remains as literal text. Opening the file in Excel and then executing that cell again (i.e. clicking in the contents and pressing enter) will result in an error again, but in Excel.
A workaround is to append with a single-quote character "'=$0.00".

  1 Comment

dpb
on 14 May 2019
Hmmm....I guess I can see that being a logical explanation but doesn't explain why it works with the same content for that cell (and many others) when there's more records in the table to be written.
And, to be even more perverse, for other reasons I had to reboot and after restart when I went to create the test case for the bug submittal I threatened above, the symptom went away and hasn't returned (to date, albeit I have moved on to further stages in the process so haven't tried the specific case very many times since first occurrence).
The text is, in fact, Excel formula retrieved from another spreadsheet; I subsequently inserted the $ signs for normalizing a split operation that needed a unique character for every term so since this was for visual use only, I just inserted the $ symbol as is, in fact, currency.
There was no need to reinsert into Excel other than the bookkeeper for whom I'm doing the consistency checks doesn't know or even have Matlab so this was way to let her open the tables for viewing. Thus, the tick won't hurt I think if I were to have to revert to that if the symptom comes up again...
Thanks for looking, don't know if that's really what it was or not...
ADDENDUM
Ideal would be for writetable have way to write the command window to a text file...there's little more to be done with these data--what is there is a comparison of two spreadsheets that should having matching entries that are done independently -- it finds those that don't match and displays for user to decide which is correct set of data (if either); it's not possible in general to ascertain which is correct with the limited data -- it takes the independent judgment from other knowledge to determine that--this just limits the number of cases that need to be manually looked at from all 1000 or so down to a few 10s...

Sign in to comment.