Method of extracting data from CSV

2013 4 12
1 个回答
回答已采纳
3 次查看（30 天）

0 个投票

Dear All, I have a very large CSV file with 5 columns which contains some historical financial prices.
Column 1 is our date starting from 1/3/2007 all the way till till mid 2011 in the form of mm/dd/yyyy.
Column 2 is our time of out trades in the form of :
35:45.0 35:45.0 35:46.0 35:46.0 35:46.0 (as seen in excel)
However when I change the data type of this column in excel from CUSTOM to TIME I get something better which I need:
13:07:23 13:07:24 13:07:24 13:07:25 13:07:25
Column 3 is our respective prices
Column 4 is our symbol (stays the same)
Column 5 is the number of trades.
So my question is I have all of this information...
I want to put the all the columns in one matrix in MATLAB (apart from 4). But I DONT want it to be a string matrix as I want to do some operations with the data.
Hint: May need to convert column 1 and 2 in to number format?

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

 采纳的回答

Walter Roberson
Walter Roberson 2013-4-12

0 个投票

fid = fopen('YourFile.csv', 'rt');
datacell = textscan(fid, '%s%s%f%*s%f', 'Delimiter', ',');
fclose(fid);
dates = datenum(datacell{1}, 'mm/dd/yyyy');
outtrades = datenum(datacell{2}, 'MM:SS:FFF');
prices = datacell{3};
ntrades = datacell{5};
datamatrix = horzcat(dates, outtrades, prices, ntrades);
Question: is there no hour in the data?

6 个评论
显示 4更早的评论 隐藏 4更早的评论

Mate 2u
Mate 2u 2013-4-12
编辑：Mate 2u 2013-4-12
Error using datenum (line 179)
DATENUM failed.
Caused by:
Error using dtstr2dtnummx
Failed on converting date string to date number.
This is the error I get, here is a sample of the column 2 open on excel:
35:45.0
35:45.0
35:46.0
35:46.0
35:46.0
BUT ...when I change format from Custom to TIME.....we get the right format:
13:07:23
13:07:24
13:07:24
13:07:25
13:07:25
does anybody know how I can fix this so I can achieve what I said in the above question but keep the time in the format as shown?
Walter Roberson
Walter Roberson 2013-4-12
Your question indicated that the second field was in the format mm:ss:ms which does not match the sample data. If your field is instead in the format of mm:ss.ms then change from 'MM:SS:FFF' to 'MM:SS.FFF'
Mate 2u
Mate 2u 2013-4-12
Hi Walter, I apologise, please see the updated question and thank you.
per isakson
per isakson 2013-4-12
Proposal:
  • open your csv-file in an editor, e.g. Matlabs editor or Notepad
  • copy&paste a few lines to the question
Mate 2u
Mate 2u 2013-4-12
CSV does not fully open on matlabs editor due to the limit of 1 million rows.
Mate 2u
Mate 2u 2013-4-12
Actually this suffices.

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by