Importing from Openoffice

1 次查看（过去 30 天）
CapaB
CapaB 2012-6-7
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.

请先登录，再回答此问题。

回答（5 个）

per isakson
per isakson 2012-6-7
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 个评论
CapaB
CapaB 2012-6-8
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.

请先登录，再进行评论。

CapaB
CapaB 2012-6-8
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 个评论
per isakson
per isakson 2012-6-8
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
per isakson 2012-6-8
I doubt that ""214" and 214" causes problems.

请先登录，再进行评论。

CapaB
CapaB 2012-6-9
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 个评论
CapaB
CapaB 2012-6-9
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
CapaB 2012-6-9
That the decimals are commas and not dots mitght also be a problem....

请先登录，再进行评论。

per isakson
per isakson 2012-6-9
.
"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 个评论
CapaB
CapaB 2012-6-9
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.

请先登录，再进行评论。

CapaB
CapaB 2012-6-10
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 个评论
Walter Roberson
Walter Roberson 2012-6-10
I would suggest this should be a new Question as it has nothing to do with importing CVS

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Low-Level File I/O 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by