Multiple date and time formats in .txt files

13 views (last 30 days)
Lu
Lu on 17 May 2011
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

Answers (2)

Fangjun Jiang
Fangjun Jiang on 17 May 2011
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','-','/');
  5 Comments
Fangjun Jiang
Fangjun Jiang on 18 May 2011
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!

Sign in to comment.


Jarrod Rivituso
Jarrod Rivituso on 17 May 2011
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!
  3 Comments
Lu
Lu on 18 May 2011
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

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!