xlswrite is truncating the values from the input matrix to the output files

Hi,

I'm having a problem with xlswrite truncating the matrix for after output to 5 decimal places. Unfortunately I need more than that, 9 probably.

I've seen some previous discussions of this here and the consensus seems to be that it's not xlswrite's problem. A demand for a screenshot is usually made. I have provided one showing the matrix (finalRaw) on the left, and the excel output on the right. I am calling the function using:

xlswrite('output.xls',finalRaw(:,:));

Please let me know if there's a way to get more precision out of this function.

James

Answer 1

[Kirby Fears]

Hi James,

There's no reason xlswrite would truncate your values.

In Excel, try clicking a "truncated" number -- the formula bar should show the non-truncated value. This would indicate that the Excel display settings are your problem.

Your screenshot is helpful, but maybe you can also post a screenshot showing the excel formula bar after selecting one of these truncated numbers. I'm attaching an example with the formula bar circled.

Also, if you want to reference an entire array you don't need to use ":" in each dimension. Saying finalRaw(:,:) is equivalent to saying finalRaw without indexing.

Hope this helps.

[James]

Please see my comment below. James

[Kirby Fears]

Hi James,

Try using dlmwrite() to write directly to .csv files. Be sure to specify a 'precision' argument.

I'm going to guess you're running on a Mac machine. Short answer is xlsread and xlswrite are intended for Windows. They don't function the same way outside of Windows because the COM server can't be opened (as you saw in your earlier error).

When the COM server fails to open, xlswrite() calls dlmread() instead. This function has default 'precision' value of 5, meaning 5 significant digits are used.

You can re-create the bug you've encountered by simply running this one line of code:

 dlmwrite('test.csv',1.23456789*[1e9, 1, 1e-9]);

Since xlswrite() does not take a 'precision' argument to pass to dlmwrite(), it simply uses the default value of 5 unless you call csvwrite or dlmwrite directly.

[James]

I'm running it on a Windows VM partition. So it is the windows version of Matlab. The excel is on the mac partition, but that shouldn't affect the function within MATlab, unless Matlab is somehow using excel in the function call.

I would prefer output in an .xlsx file, but will take CSV if I have to as it can be imported into excel easily. But yes; it appears that xlswrite fails (for whatever reason) then feeds the matrix to csvwrite, which truncates it to 5 digits of precision. dlmwrite appears to do what I need it to do, so thanks for that.

James

[Kirby Fears]

There are some community-made functions that write to excel using Java libraries instead of a COM server. If you really want to write to Excel format, you can try running xlwrite .

I've used it successfully in the past.

[James]

Yes, thanks. I've made dlmwrite create it with a precision of 12. I can work with that. Thanks for your help.

Answer 2

[James]

Hi, Thanks for your reply. I know that I shouldn't need the indexing for this function to work (:,:), but had to add it after it didn't run without the index call. Not sure why it didn't work, but it does this way, so I'm happy.

As for the excel display settings not showing it, that's simply not the case. I include the first screenshot showing the number in the function bar has (being a date in the excel "days past 1900" format) has all the decimal-days information stripped out. After some investigation, all of the columns have been truncated to 5 digits, or 4 decimal places in scientific notation. In the first picture, the function bar listing should read 41480.0208333333 for cell A2, but instead reads 41480. Cell B2 should read 6114.10300000000, but in the excel formula bar reads 6114.1.

When processing I receive the error that Matlab couldn't start the Excel server, so it was writing in CSV format. So, the second screenshot is the same data portion in the CSV file opened in a text editor. Any information past the 5th digit is clearly not there.

So, is there any way to fix this without trying to cut off the first 5 digits and write the remainder in extra columns and then stitch them back together in excel?

Thanks again, James

Answer 3

[Ariel Steele]

I had this same problem, and the error that James mentions about MATLAB having to write to CSV instead of Excel is precisely the problem. Convert the array to a table then write the table to Excel. The correct number of digits will be used.