Import CSV file using readtable gives wrong date time format.

Hi,

I am having a little problem setting the format for a variable when I use read table, it is a similar problem to others posted before, but I couldn't understand the responses posted fully.

here is my csv format (only one line, the date is 5th april! not the 4th of may)

05/04/2019 11:43,-712.6206,-204.0489,22.9026,22.3949,22.3029,22.5494,22.6193

I set the first column and wrote it to a csv file previously using

fprintf(fid, string(datetime('now')));

How can I set the import to be in european format not american, (its displaying wrong when I use the data to plot graphs.

Current code is

Table=readtable(fullfile(path,file))
DATAdate=table2array(Table(:,1));

Not really sure how to use the format arguements.

Thanks!

Steve

Answer 1

[Peter Perkins]

Steven, I'm guessing that you are in the UK, and your system is set to US. Just a guess. You only give one line of your file, so it's a little hard to know for sure the bigger picture, and also not sure what release of MATLAB you have.

Using the most recent version, R2019a, consider these two files:

>> t = table({'04/05/2019'; '04/06/2019';'04/12/2019';'04/13/2019'},[1;2;3;4])
t =
  4×2 table
         Var1         Var2
    ______________    ____
    {'04/05/2019'}     1  
    {'04/06/2019'}     2  
    {'04/12/2019'}     3  
    {'04/13/2019'}     4  
>> writetable(t,'test1.csv')
>> writetable(t(1:3,:),'test2.csv')

Assuming all the dates are in the same format, the first file has dates that could ONLY be in April, because there's no 13th month. So readtable gives me this.

>> t1 = readtable('test1.csv');
>> t1.Var1.Format = 'dd-MMM-yyyy'
t1 =
  4×2 table
       Var1        Var2
    ___________    ____
    05-Apr-2019     1  
    06-Apr-2019     2  
    12-Apr-2019     3  
    13-Apr-2019     4  

But the second file is ambiguous - it contains no day number larger than 12. Because I'm in the US, it assumes I want MM/dd, but warns me and suggests a way to avoid the issue.

>> t2 = readtable('test2.csv');
Warning: The DATETIME data was created using format 'MM/dd/uuuu' but also matched 'dd/MM/uuuu'.
To avoid ambiguity, use a format character vector. e.g. '%{MM/dd/uuuu}D' 
> In matlab.io.internal.readTextFile>textscanReadData (line 559)
  In matlab.io.internal.readTextFile (line 238)
  In matlab.io.internal.legacyReadtable (line 42)
  In readtable (line 225) 
>> t2.Var1.Format = 'dd-MMM-yyyy'
t2 =
  3×2 table
       Var1        Var2
    ___________    ____
    05-Apr-2019     1  
    06-Apr-2019     2  
    12-Apr-2019     3      

If my earlier guess was right, perhaps you can change your system settings to match your expectations. Barring that, the simplest way around this is to specify a format:

>> t2 = readtable('test2.csv','Format','%{dd/MM/yyyy}D%f');
>> t2.Var1.Format = 'dd-MMM-yyyy'
t2 =
  3×2 table
       Var1        Var2
    ___________    ____
    04-May-2019     1  
    04-Jun-2019     2  
    04-Dec-2019     3  

If your file is complicated, I'd recommend using detectimportoptions, and tweak the output form that to read the file exactly as you intend.

Depending on what release you are using, the above may not behave exactly as shown. But this should get you started.

[Steven Brace]

I'll take a go at this, I have attachend a sample file if it helps to explain.

I got close with this:

Table=readtable(fullfile('sample.csv'),'Format','%{dd/mm/yyyy HH:MM}D %f %f %f %f %f %f %f %f %f');

about half of the dates imported correctly i have attached the table.

Any help appreciated!

[Steven Brace]

Table=readtable(fullfile('sampledata.csv'),'Format','%{dd/mm/yyyy HH:MM}D %f %f %f %f %f %f %f %f %f');
datearray=table2array(Table(:,1))
centroidarray=table2array(Table(:,2:5));
temperaturearray=table2array(Table(:,6:10));

scatter(datearray(:),centroidarray(:,1))

fclose('all')

Not quite right!

[Steven Brace]

Got it to work with this just about!

Should have read the answers more carefully!

Table=readtable(fullfile('sampledata.csv'),'Format','%{dd/MM/uuuu HH:mm}D%f %f %f %f %f %f %f %f %f');

[Peter Perkins]

Yes, dd/mm/uuuu HH:MM has the minute and month fields backwards. Under some conditions, you will get a warning if you mix them up, not sure if you did or not.

In the older functions, datenum and datestr, m and M were month and minute. datetime (introduced in R2014b) uses a standard called something like Unicode Locale Data Markup Language, which defines m and M as minute and Month.

Answer 2

[Bob Thompson]

In 'newer' versions of Matlab (sorry, I don't know when this started) it is possible to define a datetime format as a named card within the readtable command. I haven't tested it, but according to the documentation it should look something like this:

Table=readtable(fullfile(path,file),'DatetimeType','dd/mm/yyyy hh:mm');

[Steven Brace]

Cheers, gave this a go and got:

Error using readtable (line 216)
Expected input to match one of these values:

'datetime', 'text'

The input, 'dd/mm/yyyy hh:mm', did not match any of the valid values.

Error in GUIDEredo>BTNload_Callback (line 543)
    Table=readtable(fullfile(path,file),'DatetimeType','dd/mm/yyyy hh:mm');

[Bob Thompson]

I suspect the issue is with all of the other bits of data, rather than the first column. Without testing this though I cannot be sure.

Because you have the mixed format, you may be better off conducting a textscan so that you can set the format of each of the different elements you are retrieving. This will also put the results directly into an array as well.

nc = 7; % Number of columns of data (not including time)
fmt = ['%s,',repmat('%7.4f,',1,nc)]; % Create format string
data = textscan(fullfile(path,file),fmt); % Might need to add 'delimiter',',' to this command
data(:,1) = datetime(data(:,1),'dd/mm/yyyy hh:mm');