MATLAB Answers

Importing from Openoffice

10 views (last 30 days)
CapaB
CapaB on 7 Jun 2012
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.

Answers (5)

per isakson
per isakson on 7 Jun 2012
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
  1 Comment
CapaB
CapaB on 8 Jun 2012
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.

Sign in to comment.


CapaB
CapaB on 8 Jun 2012
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}
  2 Comments
per isakson
per isakson on 8 Jun 2012
I doubt that ""214" and 214" causes problems.

Sign in to comment.


CapaB
CapaB on 9 Jun 2012
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.
  2 Comments
CapaB
CapaB on 9 Jun 2012
That the decimals are commas and not dots mitght also be a problem....

Sign in to comment.


per isakson
per isakson on 9 Jun 2012
.
"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] )
  1 Comment
CapaB
CapaB on 9 Jun 2012
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.

Sign in to comment.


CapaB
CapaB on 10 Jun 2012
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.
  1 Comment
Walter Roberson
Walter Roberson on 10 Jun 2012
I would suggest this should be a new Question as it has nothing to do with importing CVS

Sign in to comment.

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!