How to convert 'DDMMYYYYhhmm' into 'DD-MM-YYYY hh:mm'

Hi there,

I have some data (Please see attached the screenshot) in an Excel sheet. I need to convert it into timeseries with two coloumns. Basically, the first coloumn should the date and the second coloumn should be the values. I was wondering if anyone could help me with this.

The data is formatted in two columns separated by a single space:

The first column is the datetime in the following format: 'DDMMYYYYhhmm'. 'DD' is the day of the month as a two digit integer 'MM' is the month of the year as a two digit integer 'YYYY' is the year as a four digit integer 'hh' is the hour of the day (using 24 hour time) as a two digit integer 'mm' is the minute of the hour as a two digit integer.

The second column is the water level reading in metres in the following format: 'si.fff'. 's' is a prefix indicating if the reading is positive or negative positive readings are denoted by a space ' ' negative readings are denoted by a dash '-'. 'i.fff' is the height of the recording in metres

For example, the line '190120380314 3.142' would refer to a height of 3.142 metres recorded at 03:14am on the 19th of January 2038.

Thank you.

Kind regards,

Ali

[Stephen23]

@Ali Saremi: please upload the original data file (not a screenshot) by clicking the paperclip button.

[Ali Saremi]

@Stephen Cobeldick: Thank you for your reply. I have already uploaded the orginal data file. Look forward to hearing from you.

Answer 1

[Bjorn Gustavsson]

You can do something like this to convert a date-string to another date-string:

datestr(datenum(char(['190120211029';'140219671940']),'ddmmyyyyHHMM'),'yyyy-mm-dd HH:MM')

You can of course replace '190120211029' with the string/char-array with your time instances.

HTH

[Ali Saremi]

Hi @Bjorn Gustavsson. Thank you for your reply and sharing the code with me. If I want to convert the whole coloumn in the excel data file, would it possible to convert it into table first and then to use table function in matlab in the above code to convert the coloumn into the desirable format? Thank you.

[Bjorn Gustavsson]

You dont need to go through some novel data-type in order to do that. Just this will be enough:

fid = fopen('H011008A_2001to2010.csv','r');
C = textscan(fid, '%s %f');
C_1 = cell2mat(C{1});  % Extract the first column    
% whos %% will then return:
% Name               Size               Bytes  Class     Attributes
%
%  C                  1x2             75700880  cell                
%  C_1           525699x12            12616776  char                
%  fid                1x1                    8  double              

% Convert a couple of the dates into the prefered date-format: 
Dstr = datestr(datenum(C_1([1:4 17,32],:),'ddmmyyyyHHMM'),'yyyy-mm-dd HH:MM');                   
% Convert all:
DstrAll = datestr(datenum(C_1,'ddmmyyyyHHMM'),'yyyy-mm-dd HH:MM');

That last conversion was surprisingly slow. Perhaps it's faster to do it directly:

nrows = size(C_1,1);
qwe = [C_1(:,5:8),repmat('-',nrows,1),C_1(:,3:4),repmat('-',nrows,1),C_1(:,1:2),repmat(' ',nrows,1),C_1(:,9:10),repmat(':',nrows,1),C_1(:,11:12)];

Less flexible, slightly more prone to maintenance anoyance.

[Ali Saremi]

Hi @Bjorn Gustavsson. Thank you so much. It worked. One more favour to ask. I have been trying to convert the file to a timetable:

The first coloumn: yyyy-mm-dd HH:MM and second coloumn:the values.

With the code which you have shared with me, the date is converted corrcetly, but I haven't been able to insert the values and date all together, so eventually i could use it as a timetable. If you could help me with that, I would much appreciat it. Thank you.

[Bjorn Gustavsson]

From the help and documentation for timetable you find this:

       MeasurementTime = datetime({'2015-12-18 08:03:05';'2015-12-18 10:03:17';'2015-12-18 12:03:13'});
       Temp = [37.3;39.1;42.3];
       Pressure = [30.1;30.03;29.9];
       WindSpeed = [13.4;6.5;7.3];
       WindDirection = categorical({'NW';'N';'NW'});
       TT = timetable(MeasurementTime,Temp,Pressure,WindSpeed,WindDirection);

Adapted to your case this would turn to something like this:

fid = fopen('H011008A_2001to2010.csv','r');
C = textscan(fid, '%s %f');
C_1 = cell2mat(C{1});  % Extract the first column
% Convert a couple of the dates into the prefered date-num:
MeasurementTime = datetime(C_1,'inputformat','ddmmyyyyHHMM');
TT = timetable(MeasurementTime,C{2});

Disclaimer: I've never had to bother with these datatypes.

[Ali Saremi]

Thank you @Bjorn Gustavsson for your reply and sharing the codes with me. Unfortunatley, the dates are not converted correctly. I think the problem should be in line 7 (MeasurementTime = datetime(C_1,'inputformat','ddmmyyyyHHMM');). I would appreicate it, if you could let me know what you think. Thanks.

[Bjorn Gustavsson]

Reading the help and documentation I learned that you can do something like this:

MeasurementTime = datetime(str2num(C_1(:,5:8)),... % Extract year
                           str2num(C_1(:,3:4)),... % month
                           str2num(C_1(:,1:2)),... % etc
                           str2num(C_1(:,9:10)),...
                           str2num(C_1(:,11:12)),...
                           zeros(size(C_1(:,11))));

[Ali Saremi]

Thank you for your help @Bjorn Gustavsson. Much appreciate it.