Exporting large number as string to an Excel (.xlsx) file.

Hello,

> A. TASK:

1. Import a number from .xlsx file.

Number is stored in file in string format.

>> Steps adopted:

[ipnumNUM,ipTXT,ipRAW] = xlsread('ipFile.xlsx);
ip1 = char(ipRAW)
ip1Num = uint64(str2num(ip1));

2. Process the number.

% some arithmetic operation
ip2Num = ip1Num + 1;

3. Export result to Excel file .xlsx in string format.

> B. ISSUE:

1. In step# 3 above, we intend to save result (ip2Num) in string format.

a. It is not known how to convert number to raw format in Matlab.

2. We wish to export full number. However number is being exported in exponential format; and loosing values.

It is not known how to save number correctly.

_Example: 1234567891234 is saved as 1.2345e+12

When read in Excel, number shows 1234500000000_

> OPEN QUERIES:

1. Please assist any optimum way to import large number as string from Excel file (.xlsx).

2. Please assist any optimum way to export large number as string to an Excel file (.xlsx).

(All numbers MUST be saved TOGETHER in one Excel block).

Please assist.

Sincerely,

A

[Walter Roberson]

Is the number an integer?

What is the maximum number that might be stored?

How are you doing the saving to .xlsx ?

Answer 1

[dpb]

"I would still be interested to see why 'csvwrite' gives '1234600000000' as output"

>> help csvwrite
   csvwrite Write a comma-separated value file.
   ...
   Notes:

      * csvwrite writes a maximum of five significant digits.  For greater
      precision, call DLMWRITE with a precision argument.

[dpb]

And to illustrate one last regarding Excel and double precision (as well as Matlab, of course)

>> num=uint64(realmax)
num =
18446744073709551615
>> xlswrite('AD.xls', num)
>> val=xlsread('AD.xls')
val =
 1.8447e+19
>> num2str(num,'%20.0f')
ans =
18446744073709552000
>>

Answer 2

[dpb]

The first is a fignewton of the Excel display; Excel treats everything as a double unless you force it to be something different. Therefore you have roughly 15 decimal digits of precision available; if your values are larger than that you'll have to treat them as character unless newer releases of Excel have a long integer format as well; I don't know about that, don't have a recent release. Check MS documentation for the specifications.

As for Matlab and xlsread|xlswrite, the following demonstrates how they work...

Write your value just as a numeric...

>> % unless you change format in Excel, this shows up as you note

>> xlswrite('AD.xls', 1234567891234)

What's actually in the spreadsheet?

>> [num,txt]=xlsread('ad.xls')
num =
 1.2346e+12
txt = 
   {}
>> [num,txt,raw]=xlsread('ad.xls')
num =
 1.2346e+12
txt = 
   {}
raw = 
  [1.2346e+12]

Looks suspicious on the surface...

>> num==1234567891234
ans =
   1
>> num2str(num,'%d')
ans =
1234567891234
>> delete 'ad.xls'

but as the above shows, it's stored internally in full double precision and is identical to the value written.

OK, moving on, what are alternatives if the values are bigger? Try encapsulating as cell...

>> xlswrite('AD.xls', {num2str(1234567891234,'%d')})
>> [num,txt,raw]=xlsread('ad.xls')
num =
 1.2346e+12
txt = 
   {}
raw = 
  [1.2346e+12]
>> num2str(num,'%d')
ans =
1234567891234
>> delete 'ad.xls'
>>

OK, that's treated the same way, get same results.

Write as a cell string in string format Excel will recognize...

>> xlswrite('AD.xls', {num2str(1234567891234,'''%d')})
>> [num,txt,raw]=xlsread('ad.xls')
num =
   []
txt = 
  '1234567891234'
raw = 
  '1234567891234'
>>

Note that now the internal cell inside Excel is not numeric but is character. This will let you store arbitrarily long numeric strings as strings thus getting around the double precision limitation on significant digits but, of course, you can't do anything with them with ordinary Excel operations on numerics unless, as noted, there is a way to handle them in later releases.

[dpb]

BTW, note that when writing a string unless it's encapsulated in a cell string the individual characters are expanded one per Excel cell, not written into a single cell (I'm guessing you discovered this already from the latter question/requirement).

That's an assumption made in the xlswrite routine so even changing the Excel formatting via COM won't solve it altho I'm sure there are ways directly via COM that don't use xlsread|xlswrite at all that will transfer the data natively but that's an Excel programming question, not Matlab.

>> xlswrite('AD.xls', num2str(1234567891234,'''%d'))
>> [num,txt,raw]=xlsread('ad.xls')
num =
   1     2     3     4     5     6     7     8     9     1     2     3     4
txt = 
  ''    ''    ''    ''    ''    ''    ''    ''    ''    ''    ''    ''    ''    ''
raw = 
Columns 1 through 13
  ''    [1]    [2]    [3]    [4]    [5]    [6]    [7]    [8]    [9]    [1]    [2]    [3]
Column 14
  [4]
>>

Answer 3

[A D]

Thanks for the response. Issue is functional now.

Earlier 'csvwrite' was being used that was causing error.

'xlswrite' seems to work without any issues now.

I would still be interested to see why 'csvwrite' gives '1234600000000' as output instead of '1234567891234'.

Sincerely,

A

------------------------------------------------------

%Sample Integer Number: 1234567891234

A. PREVIOUS FAIL RESULT:

ipRAW = '1234567891234';
ip1Num = uint64(str2double(ipRAW));
% some arithmetic operation
ip2Num = ip1Num + 1;
csvwrite('output.csv',ip2Num) 

B. PASS:

ipRAW = '1234567891234';
ip1Num = str2num(ipRAW); % PASS
ip1Num = uint64(str2num(ipRAW)); % PASS
ip1Num = uint64(str2double(ipRAW)); % PASS
% some arithmetic operation
ip2Num = ip1Num + 1;
xlswrite('output.xlsx',ip2Num); % PASS