Multiple date and time formats in .txt files

Hi everybody!

I have one question regarding reformatting data and time from one format to another. I have many files as .txt format in a folder, but the date and time format are different. For example, some files have the date and time format as 'dd.mm.yy HH:MM:SS', some others as 'dd-mm-yyyy HH:MM:SS', and some others as 'dd/mm/yy HH:MM:SS'.

In my script I use textscan to load all the files contained in a folder and then try to convert the date and time to numeric values using this:

numDate=datenum([dateandtime{:}],'dd-mm-yyyy HH:MM:SS');

But of course, since I have three different formats, matlab throws an error :??? Error using ==> datestr at 180 Cannot convert input into specified date string. DATENUM failed..

Is there a way that I could include in my script something that would allow to reformat all the dates and times into one format? So that all of date and time would be in format 'dd-mm-yy HH:MM:SS'??

UPDATE: so I checked random files in my folder and i found that the problem is not only about changing '.' or '/' or '-', but also that the date strings are in different formats. For example they can be 'dd.mm.yy HH:MM:SS' or 'dd.mm.yyyy HH:MM:SS' or 'mm.dd.yy HH:MM:SS'. How can I reformat all these formats to a single one like 'dd.mm.yyyy HH:MM:SS'??

Thank you so much and have a nice day!

Lourdes

Answer 1

[Fangjun Jiang]

As long as you can read the date string correctly, you can write your code to choose one format and convert the other two.

Once you have the string, run the following two lines:

MyDataString=strrep(MyDataString,'.','/');
MyDataString=strrep(MyDataString,'-','/');

Then write your code according to the single format: 'dd/mm/yy HH:MM:SS'. For example:

strrep('dd.mm.yy HH:MM:SS','.','/');
strrep('dd-mm-yyyy HH:MM:SS','-','/');

[Lu]

Dear Fangjun Jiang,

Thank you for the tip! The first two code lines work perfectly to change the '.' or '-' but now I found that my .txt files have all different formats. I updated my question to explain myself better. Do you have any ideas of how to do this in the case that the date format starts with the month instead of the date?

Thank you :)

[Jarrod Rivituso]

Good idea, Fangjun :)

[Fangjun Jiang]

Regarding your update, that is going to be tough! You know that for certain date like '01.05.11', it could be regarded as Jan. 5th, or May 1st. There is no way you can tell without extra information. You could read a bunch of data and be able to determine the field (if the number is greater than 12, it must be dd, instead of mm). But there is no guarantee your data will always have that variety.

What I suggest is that you seem to need to pre-process your text file manually. Go through your text file, add a row above your data to mark the data string format, then you can read in the data string format and use datenum(DataString, DataStringFormat). You don't need to worry about conversion any more.

[Lu]

Dear Fangjun Jiang,

Thank you for the reply! I have over 2160 text files! I guess it will take a while then :S

[Fangjun Jiang]

Well, then try to read in all the data and determine the date string format. Although no guarantee, in reality your data should provide enough variety to be able to do that (as you've already done that through your eyes). But certainly, it will need more code adding to your already challenging task. Good luck!

Answer 2

[Jarrod Rivituso]

I'm going to assume you have a cell array of strings of different formats.

Could you define some criterion of being able to determine which format is which? For instance, given your example, I could search for a '-' and determine that it is one format, and search for a '.' and determine it is a different format.

If you can do that, you can do some fancy logical indexing and get what you need. See example below.

%Create fake data  
diffFormats = {'03.08.99 14:03:36'; '03.10.99 14:03:36'; '03-11-99 14:03:36'; '03-12-99 14:03:36'}

%Create logical arrays for where each specific format is in the data
onesWithDashes = cellfun(@(str) ~isempty(strfind(str,'-')),diffFormats)
onesWithDots = cellfun(@(str) ~isempty(strfind(str,'.')),diffFormats)

%Create array where final datenums will be placed
numericDates = zeros(size(diffFormats))

%Convert each format using it's own call to datenum
numericDates(onesWithDashes) = datenum(diffFormats(onesWithDashes),'dd-mm-yy HH:MM:SS')
numericDates(onesWithDots) = datenum(diffFormats(onesWithDots),'dd.mm.yy HH:MM:SS')

Note that your search criterion is more complicated to determine if you have a certain format, then you may want to use regexp instead of strfind.

Hope this helps!

[Lu]

Dear Jarrod,

Thank you for the answer! but I just realized that the date formats are also in three forms: 'dd.mm.yy HH:MM:SS' , 'dd.mm.yyyy HH:MM:SS' and 'mm.dd.yy HH:MM:SS' . do you have any idea of how to do this to set the date format to 'dd.mm.yyyy HH:MM:SS' ?

thank you :)

[Jarrod Rivituso]

How can you tell what the true meaning of something like this is then...?

03.04.81 00:00:00

If you want to do it algorithmically, you'll need to first clarify how you would manually differentiate that between March 4, 1981 and April 3, 1981.

[Lu]

Dear Jarrod,

I don´t really know much matlab, and the way I realized that 03.04.81 was march 4, 1981 was comparing it to the file name which is

data20091019.txt.

Just by looking at the date included in the text file i can see the real date. Do you have any ideas of how to do it? :S

Thank you so much