Importing from Openoffice

The csv (or xls) file is a list of numbers but when i import it shows a list of numbers in quotes "234" "432" etc

and im not able to import them as numbers. Only way is to import as a cell array but then i cant use them as numbers in matlab and i cant convert them to numbers.

Answer 1

[per isakson]

CSV comes in different flavors and Microsoft make their own rules. Google "CSV file format" and see for example Common Format and MIME Type for Comma-Separated Values (CSV) Files. I guess OpenOffice tries to honor the "standard".

It seems Matlab has no obvious way to read proper CSV files.

A brute approach is to

1. read the file as characters
2. remove all '"'
3. parse the character string with textscan

Something like

function    M = Answer( )    
    fid  = fopen( 'cssm.txt', 'r' );
    str  = fread( fid, '*char' );
    sts  = fclose( fid );
    str( str == '"' ) = [];
    cac  = textscan( str, format );
    peel off the braces   
end

This will certainly not work in all cases

[CapaB]

Thx. I havent got it to work yet tho.

Im not sure how to use the function but when i copy and paste this into the command window

fid = fopen( 'AppHist2.csv', 'r' );

str = fread( fid, '*char' );

sts = fclose( fid );

str( str == '"' ) = [];

cac = textscan( str, '%f' );

I get the data into a file cac which is a 1x1 cell.

Then when i click cac it says [4655x1 double] in cell 1,1.

And when i click in this cell i get all the 4655 numbers i wanted to import.

But because its a 1x1 cell with "a vector inside it" i dont know how to

extract the numbers im looking for.

Answer 2

[CapaB]

I solved it, ty.

Now i have another similar problem.

I have imported a list of numbers to a 4630x1 cell called HMhist.

Some of these numbers are in quotes "214" and some are not in quotes 214.

Your way of removing the "" doesnt seem to work now.

(Error using fread Invalid file identifier.)

and the cell2mat doesnt work either

HmH = cell2mat(HMhist)

Error using cat CAT arguments dimensions are not consistent.

Error in cell2mat (line 84) m{n} = cat(1,c{:,n}

[per isakson]

It's hard to guess what's going on! What does str(1:120)' display?

"(Error using fread Invalid file identifier.)" hints that you fail to open the file. However, you say you read "4630x1 cell".

Why use cell2mat?

[per isakson]

I doubt that ""214" and 214" causes problems.

Answer 3

[CapaB]

Im trying to use cell2mat because HMhist is a cell. (I imported the data from HMhist.csv using the import wizard)

HMhist(755)-HMhist(1);

Undefined function 'minus' for input arguments of type 'cell'.

*************************************

If i instead use the code that worked for the other csv-files

    fid  = fopen( 'HMhist.csv', 'r' );

    str  = fread( fid, '*char' );

    sts  = fclose( fid );

    str( str == '"' ) = [];
    cac  = textscan( str, '%f' );

    HMhist = cell2mat(cac);

Then HMhist becomes a single number, 208, instead of a 4630x1 vector.

"What does str(1:120)' display?"

str includes all the right information.

str(1:120)

ans =

2

0

8

,

5

etc for 120 rows.

[CapaB]

Ok, i got rid of the "" by this

load HMhist.mat

for i=1:4630

HMhist{i}( HMhist{i} == '"' ) = [];

end

But the problem seems to be that the cell array "numbers" are of different lengths...

(213,5 215 216,6 etc)

str2double or cell2mat doesnt work.

[CapaB]

That the decimals are commas and not dots mitght also be a problem....

Answer 4

[per isakson]

.

"commas instead of dots" is definately a problem!

    fid  = fopen( 'HMhist.csv', 'r' );
    str  = fread( fid, '*char' );
    sts  = fclose( fid );
    str( str == '"' ) = [];
    str( str == ',' ) = '.'; 
    cac  = textscan( str, '%f' );

Why do you believe that the format, '%f', is appropriate? First you need to get the format right.

What does str(1:120)' display? Don't forget the blip! Or do

    permute( str(1:124), [2,1] )

[CapaB]

Ty again!

I think i figured it out now!

I'll post the entire code tomorrow.

I havent used matlab or math(!) in about 10 years

so there are probably mistakes i havent noticed tho.

If someone have time to read it through i'd be very happy.

Answer 5

[CapaB]

My bank sells this contract:

If Apple, Coca-Cola, Volkswagen or H&M dont fall more than 50% over the next 3 years i get a return of 32.4%. (Its between the start on 15 June 2012 and end on 15 June 2015. The fall between these two dates should have to be no more than 50%)

If any one of them fall more than 50% i get my investment reduced by how much the worst stock has fallen.

I tried to calculate the value of such a contract based on historical prices from 1 Jan 1994.

*****************************'

clear

Tot=0;

n=4655-755; % Number of 3-year periods

T1=0; % Number of these which yields a positive return

T2=0; % Number of these which yields a negative return

 fid  = fopen( 'AppHist2.csv', 'r' );   % Read Apple data
    str  = fread( fid, '*char' );
    sts  = fclose( fid );
    str( str == '"' ) = [];
    cac  = textscan( str, '%f' );

AppH = cell2mat(cac);

for i=1:4655

    AppHO(i)=AppH(4656-i);

end

 fid2  = fopen( 'CoHist.csv', 'r' );    % Read Coca-Cola data
    str2  = fread( fid2, '*char' );
    sts2  = fclose( fid2 );
    str2( str2 == '"' ) = [];
    cac2  = textscan( str2, '%f' );

CoH = cell2mat(cac2);

for i=1:4655

    CoHO(i)=CoH(4656-i);

end

fid3 = fopen( 'VwHist4.csv', 'r' ); % Read Volkswagen data

    str3  = fread( fid3, '*char' );

    sts3  = fclose( fid3 );

    str3( str3 == '"' ) = [];

    str3( str3 == ',' ) = '.'; 

    cac3  = textscan( str3, '%f' );

VwH = cell2mat(cac3);

for i=1:3416

    VwHO(i)=VwH(3417-i);

end

fid4 = fopen( 'HMhist.csv', 'r' ); % Read HM data

    str4  = fread( fid4, '*char' );

    sts4  = fclose( fid4 );

    str4( str4 == '"' ) = [];

    str4( str4 == ',' ) = '.'; 

    cac4  = textscan( str4, '%f' );

HmH = cell2mat(cac4);

for i=1:4628

    HmHO(i)=HmH(4629-i);

end

P2=1; P3=1;

for i=1:n

% run through the data from 1 jan 1994

    K=AppHO(i+755)-AppHO(i);                
    P=K/AppHO(i);
% Apple. Change in % between two points separated by 3 years.

    K1=CoHO(i+755)-CoHO(i);                  
    P1=K1/CoHO(i);
% Coca-Cola. Change in % between two points separated by 3 years.

    if i>1239                                % less data for VW.

        j=i-1239;

        K2=VwHO(j+755)-VwHO(j);             
        P2=K2/VwHO(j);
 % VW. Change in % between two points separated by 3 years.

    end

    if i>27                                                            
% 27 data points less for HM.

        k=i-27;

        K3=HmHO(k+755)-HmHO(k);              
        P3=K3/HmHO(k);
% HM. Change in % between two points separated by 3 years.

    end

if  (P>=-0.5)&(P1>=-0.5)&(P2>=-0.5)&(P3>=-0.5)  
      T=9800*1.35;                               
% 35% return if none of the four stocks have fallen more than 50%
% Contract size is 10000 - 200 courtage
      T1 = T1+1;                                 

    else

        S=[P P1 P2 P3]
        Smin=min(S)

        T=9800+9800*Smin                                
% If any stock have fallen by more than 50% my investment is reduced by how much the worst stock has fallen.
        T2 = T2+1;

    end

    end

    T;

    Tot = Tot + T;

end

OutcomePerContract=Tot/n

Return=(OutcomePerContract-10000)/10000

NumberPosDays=T1

NumberNegDays=T2

*************************************

OutcomePerContract =

   1.1801e+04

Return =

    0.1801

NumberPosDays =

        3311

NumberNegDays =

   589

***********************************************

If anyone has any ideas on how this code can be improved or if there is any errors, I am happy to hear about it.

[Walter Roberson]

I would suggest this should be a new Question as it has nothing to do with importing CVS