Wrong Date format with Readtable for CSV

Hello,

I am trying to import multiple csv files into matlab with Date/Time and other data to plot. READTABLE (including date/time format specified) works fine for data files which have dates greater than 13 but not for anything below 13 as it converts to different format irrespective of me defining the format upfront.

Can anybody help me to solve this issue.

[dpb]

Not without seeing

1. The sample data file that shows the problem and
2. The code you used.

Attach a short sample file and paste or attach the code, do not use picture images or screenshots...

[Logesh Velusamy]

Apologies for not posting the code in first place.

I am running following code to read files from a directory and looping through to store imported data in structure.

Once this is done, I am going through another loop to convert structure to array and apply date format and plot data with the below code.

I have attached two sample *.csv files for reference.

Thanks.

% 
files = dir('C:\Test\*.csv');
fullpaths = fullfile({files.folder}, {files.name});
for i=1:length(files)
    Data(i).name = readtable(files(i).name);   
end
% 
for i = 1:length(files)
      
    New_Data = struct2array(Data(i));
    M = datetime(New_Data.Date,'InputFormat','dd-MM-yyyy');
    t = datetime(New_Data.Time,'InputFormat','HH:mm:ss.SSS');
    t.Format= 'HH:mm:ss.SSS';
    New_Data.Datetime = M + timeofday(t);
    New_Data.Datetime.Format = 'MM.dd.uuuu HH:mm:ss.SSS';
        
    if New_Data(i).Fuel_SP > 420 & New_Data(i).Fuel_SP <560
        
        figure(1)
        subplot(2,1), scatter(New_Data(i).Datetime,New_Data(i).FuelPump_rpm);
        xlabel('Date');
        ylabel('Fuel Pump Speed, rpm');
        title('Fuel Pump Speed vs Date');
        grid('on');
        grid('minor');
        hold on  
        
        subplot(2,2), scatter(New_Data(i).Datetime,New_Data(i).FuelPump_Volts);
        xlabel('Date');
        ylabel('Fuel Pump Volts, V');
        title('Fuel Pump Volts vs Date');
        grid('on');
        grid('minor');
        hold on 
    
    else
    end     
end

[dpb]

What is a time of 31:05.9 to be interpreted as?

If it is intended as HH:mm:ss.SSS it's improperly formatted--the hour field and separating colon is missing. You lied to the input interpreter.

'HH' is a format descriptor requiring two digits for 24-hr clock, 'H' is for one/two digit(s), but there is no descriptor for 0|1|2 digits.

Best solution would be to fix whatever it is that created the file to properly format the time column.

Failing that, if there aren't two colons in the input field for the Time variable, prefix the '00:' or '0:' required and make the time descriptor format match the actual formatting used before converting.

[Logesh Velusamy]

That is actually minutes (00:31:05:9), If you double click the cell in CSV file you can see this.

I am not looking into time, as I have lot of csv files that have been populated over several days/months. I want to plot the y values over date/month which is the first column in CSV file.

[Stephen23]

"That is actually minutes (00:31:05:9), If you double click the cell in CSV file you can see this."

When I open your CSV file in a text editor this is what it contains (e.g. the first line of data):

21/08/2019,31:05.9,311.27423,0.27268511,400

Where are the hours defined? Or does the time simply count minutes from the start of the day?

[Logesh Velusamy]

When I am looking at the data in CSV, I am seeing something like this, I am not sure why it is showing like that in text editor.

[Logesh Velusamy]

@dpb

"Best solution would be to fix whatever it is that created the file to properly format the time column"

I will go back to original software and will reconfirm the time setting.

Will that impact the date formatting even though I am not using time column?

[Stephen23]

"When I am looking at the data in CSV, I am seeing something like this"

Because you are using Microsoft Excel to view the file, which performs lots of magic on the file data before it is displayed. Just to make that clear: Excel does NOT display CSV (or even XLS/X data, for that matter) exactly as it is saved in the file: it performs many conversions based on the locale settings and the number settings and the date settings and no doubt other magic... in short, do NOT rely on Excel if you really want to know what is saved in a file.

I strongly recommend that you view text files (which is what CSV files are) using a text editor.

"I am not sure why it is showing like that in text editor."

Becaue that is what your CSV file actually contains. There is no way to avoid the fact that your CVS file is missing the hour data.

"Will that impact the date formatting even though I am not using time column?"

It should not affect the date, but it will affect the time of day.

dpb is correct: you should fix the file writing, not try to fix this data-bug in MATLAB.

[Logesh Velusamy]

Stephen Cobeldick, dpb

Thanks for your help guys, I will try fixing this in the export software.

[dpb]

Will that impact the date formatting even though I am not using time column?

You can, of course, simply ignore the time column, but if you do all the data for the given day will be plotted on top of itself as a blob of points. If that's the intent, ok; if not, "not so much".