Method of extracting data from CSV

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?

Answer 1

[Walter Roberson]

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?

[Mate 2u]

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]

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]

Hi Walter, I apologise, please see the updated question and thank you.

[per isakson]

Proposal:

• open your csv-file in an editor, e.g. Matlabs editor or Notepad
• copy&paste a few lines to the question

[Mate 2u]

CSV does not fully open on matlabs editor due to the limit of 1 million rows.

[Mate 2u]

Actually this suffices.