CODEWITHSUNDEEP
perlexcelmatlab
siegel
siegel

Reputation: 829

Changing format of many files in Excel

I have a folder filled with thousands of csv files. When I open one file, the data looks like:

20110503    01:46.0  1527.8  1  E
20110503    01:46.0  1537.8  1  E
20110504    37:40.0  1536.6  1  E
20110504    37:40.0  1533.6  1  E
20110504    36:17.0  1531.1  1  E

The second column(time) has minutes and seconds before the decimal point. If I select the second column, right click and click format cells, select time, and change to 13:30:55 mode, the same data looks like:

20110503    19:01:46 1527.8 1   E
20110503    19:01:46 1537.8 1   E
20110504    0:37:40  1536.6 1   E
20110504    0:37:40  1533.6 1   E
20110504    8:36:17  1531.1 1   E

Now I can see hours, minutes and seconds. I have written a matlab function that reads these files, but needs to be able to read the hours. The function can only be used after I change the format to display the hours. Now I have to apply the function to all the files in the folder.

I'm wondering, is there a way to change the default time display so hours are included? If not, is there a way of writing a script to change the format of these files? Thanks!

Note: the part of my matlab function that reads the file looks like:

fid = fopen('E:\Tick Data\Data Output\NGU13.csv','rt'); 
c = fscanf(fid, '%d,%d:%d:%d,%f,%d,%*c');

datamat = reshape(c,6,length(c)/6)'; % reshape into matrix

yyyymmdd = datamat(:,1);    
hr = datamat(:,2);             
mn = datamat(:,3);            
sec = datamat(:,4);
pp = datamat(:,5); % price
vv = datamat(:,6); % volume

In Excel:

enter image description here

In Notepad, you can see hours, minutes, seconds, and milliseconds:

20111206,09:50:56.411,4.320,1,E
20111206,10:02:10.167,4.300,1,E
20111206,11:24:09.052,4.313,1,E
20111206,11:46:09.359,4.307,1,E
20111206,11:50:22.785,4.320,1,E

Upvotes: 0

Views: 100

Answers (1)

Oleg
Oleg

Reputation: 10676

For a record of the type

20010402, 09:30:24.456, 4.235, 1, E

you should use this fmt:

fmt  = '%f%f:%f:%f.%f%f%*s';
data = textscan(fid, fmt, 'Delimiter',',','CollectOutput',true);

Upvotes: 2

Related Questions