Wrong date while importing .csv

Having a problem when i import daily .csv files. More specifically, when i plot my xy data(x axis contains date and time stamp), in every different daily file, MATLAB shows todays date. Any ideas?

[madhan ravi]

Upload your file by clicking the paper clip button.

[madhan ravi]

can you upload your code?

[madhan ravi]

Just lookup xticklabels.

[Stephen23]

@Sirius8: this is what one line of the file looks like:

00:00:0;0.0;0.00540500736377

Where is the date? Please show us which parts of that line contain date data.

[Ancalagon8]

You are right. The date was not in the data but in the name of the csv

Answer 1

[jonas]

The date is not automatically assigned because it's not in the data but in the name of the file. You could do something like this:

files=dir('folderpath~\*.csv');
for i=1:length({files.name})
    date=files(i).name;
    date=regexprep(date,'.csv','')
    data=readtable(files(i).name);
    t{i}=data{:,1}+datetime(date);
    xy{i}=data{:,2:3}
end

Now you have two cell arrays, t and xy, where the former has all time data and the latter all other data.

[Ancalagon8]

>> files=dir('folderpath~\*.csv');

files =

       name: '2018-08-17.csv'
       date: '23-Aug-2018 14:43:02'
      bytes: 2642784
      isdir: 0
    datenum: 7.3730e+05

>>for i=1:length({files.name})

    date=files(i).name;
    date=regexprep(date,'.csv','')
    data=readtable(files(i).name);
    t{i}=data{:,1}+datetime(date);
    xy{i}=data{:,2:3}
end

date =

2018-08-17

Addition is not defined between datetime arrays.

[jonas]

It seems your local settings are different from mine, so readtable interprets the first column as datetime instead of duration. Add these two lines at the beginning of the loop.

    opts=detectImportOptions(files(i).name)
    opts=setvartype(opts,'Var1','duration')

[Ancalagon8]

It says: "Subscript indices must either be real positive integers or logicals."

[jonas]

I'm having trouble reproducing the issue. At what line does it say that?

type this and paste the output in comment here:

varfun(@class,data,'OutputFormat','cell')

[Ancalagon8]

ans =

    'cell'    'double'    'double'

[Ancalagon8]

also when i run the whole loop it says "Undefined function or variable 'detectImportOptions'."

[jonas]

Alright, what release are you running? We may have to convert the cell to duration subsequent to loading the table. Copy a few rows from the first column here so I can figure out how to convert it. In the meantime, you can try:

TT=timetable(data)

as timetable is sometimes smart enough to figure out the format of the input.

[Ancalagon8]

R2016a. A "dummy" solution is to revert windows date from 24/08 to 17/08. It worked. Is there any way to define current date into matlab as "17-08-2018" before i import the .csv file?

[Ancalagon8]

00:00:0;0.0;0.00540500736377
00:00:1;0.0;0.0043870014771
00:00:2;0.0;0.00462333825702
00:00:3;0.0;0.00423929098966
00:00:4;0.0;0.00442477876106
00:00:5;0.0;0.00477104874446
00:00:6;0.0;0.00409158050222
00:00:7;0.0;0.0053982300885
00:00:8;0.0;0.00479289940828
00:00:9;0.0;0.00451327433628
00:00:10;0.0;0.00418020679468
00:00:11;0.0;0.00476401179941
00:00:12;0.0;0.00406804733728
00:00:13;0.0;0.00498525073746
00:00:14;0.0;0.00429837518464
00:00:15;0.0;0.00418879056047
00:00:16;0.0;0.00508875739645
00:00:17;0.0;0.00386430678466
00:00:18;0.0;0.00460856720827
00:00:19;0.0;0.00468335787923
00:00:20;0.0;0.00478581979321
00:00:21;0.0;0.00470501474926
00:00:22;0.0;0.00459379615953
00:00:23;0.0;0.00494082840237
00:00:24;0.0;0.00353982300885
00:00:25;0.0;0.00511834319527
00:00:26;0.0;0.00460972017673
00:00:27;0.0;0.00411504424779
00:00:28;0.0;0.00472673559823
00:00:29;0.0;0.00530280649926
00:00:30;0.0;0.00441654357459

[jonas]

Let's skip readtable altogether and try another import option:

files=dir('folderpath~\*.csv');

for i=1:length({files.name})
    date=files(i).name;
    date=regexprep(date,'.csv','')
    din=importdata(files(i).name,';')
    t{i}=duration(din.textdata,'format','hh:mm:ss')+datetime(date);
    xy{i}=din.data
end

I wouldn't recommend your other solution. Albeit it could work, it is the opposite of robust.

[Ancalagon8]

date =

2018-08-17

din =

          data: [86401x2 double]
      textdata: {86401x1 cell}
    rowheaders: {86401x1 cell}

Error using duration (line 237) Input data must be a numeric matrix with three columns, or three separate numeric arrays.

[jonas]

Seems to have been more updates to DURATION than I was aware of but alright, give me a few minutes to fix

[Walter Roberson]

In this situation, let it be imported as datetime, but then do

t{i} = data.Var1 - dateshift(data.Var1, 'start', 'day') + datetime(day);

[jonas]

Thanks Walter, that should do the trick.

If it, for some reason, still does not work. Then this solution should. It's however much slower due to the cellfun

files=dir('folderpath~\*.csv');

for i=1:length({files.name})
    date=files(i).name;
    date=regexprep(date,'.csv','')
    din=importdata(files(i).name,';')
    tday=cellfun(@(x) str2double(strsplit(x,':')),din.textdata,'uniformoutput',false)
    tday=cell2mat(tday);
    t{i}=hours(tday(:,1))+minutes(tday(:,2))+seconds(tday(:,3))+datetime(date);
    xy{i}=din.data
end

The absolute best solution would be to update MATLAB :)

[Ancalagon8]

Yes it worked! Then i used

datetime = [t{:}];

in order to convert cell to datetime

[Ancalagon8]

Thank you, much appreciate!

[jonas]

You're welcome! Note that you can just remove the curly braces from the t{i} if you want to achieve that. I put the results in a cell array as I figured you have several data sets that you want to load and store.

Also, don't use datetime as a variable name!!

[Ancalagon8]

In general it works. At some daily files i get this error in the line "tday=cell2mat(tday);"

"Error in cell2mat (line 83) m{n} = cat(1,c{:,n});"

    files=dir('*2018-08-25.csv');
    for i=1:length({files.name})
       date=files(i).name;
       date=regexprep(date,'.csv','');
       din=importdata(files(i).name,';');
       tday=cellfun(@(x) str2double(strsplit(x,':')),din.textdata,'uniformoutput',false);
       tday=cell2mat(tday);
       ti=hours(tday(:,1))+minutes(tday(:,2))+seconds(tday(:,3))+datetime(date);
       xyi=din.data;
    end