[scilab-Users] Reading an Excel file with mixed strings and numbers

[Samuel Gougeon]

Hello Petter,

Le 18/11/2011 11:05, Petter Wingren a écrit :
> .../...
> What I inserted in the first mail is a cut'n'paste sample, Down to the
> "------------------------------" its all text in one column.
>    
Yes, but to test your case, the formating of data (dates) in the excel 
file is needed.
Dates are numbers + special format.
> Below that, it's two columns (timestamp in the first and the number to
> the right in the second).
>
> I attached the sample as an .xls file here too if that helps.
>
> /Petter
>    
For me, it works:

-->[fd,SST,Sheetnames,Sheetpos] = xls_open('file.xls');
-->format(12)
-->[Value,trash] = xls_read(fd,Sheetpos);
-->Value
  Value  =

     Nan            Nan      Nan
     Nan            Nan      Nan
     Nan            Nan      Nan
     40503.82153    0.       Nan
     40503.82164    0.       Nan
     40503.82176    0.       Nan
     40503.82188    0.       Nan
     40503.82199    0.       Nan
     40503.82211    0.       Nan
     40503.82222    0.       Nan
     40503.82234    0.       Nan
     40503.82245    0.       Nan
     40503.82257    518.     Nan
     40503.82269    1139.    Nan
     40503.8228     1035.    Nan
     40503.82292    501.     Nan
     40503.82303    449.     Nan
     40503.82315    901.     Nan
     40503.82326    545.     Nan
     40503.82338    113.     Nan
     40503.8235     1.       Nan
     40503.82361    37.      Nan
     40503.82373    17.      Nan
     40503.82384    71.      Nan
     40503.82396    15.      Nan
     40503.82407    9.       Nan
     40503.82419    256.     Nan
     40503.82431    352.     Nan
     40503.82442    336.     Nan
     40503.82454    318.     Nan
     40503.82465    337.     Nan
     40503.82477    94.      Nan
     40503.82488    236.     Nan
     40503.825      432.     Nan
     40503.82512    225.     Nan
     40503.82523    637.     Nan
     40503.82535    427.     Nan
     40503.82546    388.     Nan
     40503.82558    291.     Nan
     40503.82569    168.     Nan
     40503.82581    272.     Nan
     40503.82593    264.     Nan
     40503.82604    318.     Nan
     40503.82616    297.     Nan
     40503.82627    263.     Nan
     40503.82639    261.     Nan
     40503.8265     73.      Nan
     40503.82662    45.      Nan
     40503.82674    2.       Nan
     40503.82685    8.       Nan
     40503.82697    27.      Nan
     40503.82708    92.      Nan
     40503.8272     160.     Nan
     40503.82731    291.     Nan
     40503.82743    116.     Nan
     40503.82755    87.      Nan
     40503.82766    157.     Nan
     40503.82778    548.     Nan
     40503.82789    2428.    Nan
     40503.82801    818.     Nan

Then, you just have to convert dates according to the format you need,
either raw numeric as it is "hard-coded", or in a human readable way.
For example:

d = getdate((Value(4:$,1)-25569)*24*3600);
d(:,9) = d(:,9)+d(:,10)/1000;
d = d(:,[6 2 1 7 8 9]);
msprintf("%02i/%02i/%02i %02i:%02i:%02i\n",d(:,:))

(Amusingly, there is just a shift by 1 hour, likely due to the
European convention between summer/winter :-)

Samuel