You are now following this question
- You will see updates in your followed content feed.
- You may receive emails, depending on your communication preferences.
Wrong date while importing .csv
3 views (last 30 days)
Show older comments
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?
5 Comments
madhan ravi
on 24 Aug 2018
Upload your file by clicking the paper clip button.
madhan ravi
on 24 Aug 2018
can you upload your code?
madhan ravi
on 24 Aug 2018
Just lookup xticklabels.
Ancalagon8
on 24 Aug 2018
You are right. The date was not in the data but in the name of the csv
Accepted Answer
jonas
on 24 Aug 2018
Edited: jonas
on 24 Aug 2018
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.
18 Comments
Ancalagon8
on 24 Aug 2018
>> 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
on 24 Aug 2018
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
on 24 Aug 2018
It says: "Subscript indices must either be real positive integers or logicals."
Ancalagon8
on 24 Aug 2018
ans =
'cell' 'double' 'double'
Ancalagon8
on 24 Aug 2018
Edited: Ancalagon8
on 24 Aug 2018
also when i run the whole loop it says "Undefined function or variable 'detectImportOptions'."
jonas
on 24 Aug 2018
Edited: jonas
on 24 Aug 2018
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
on 24 Aug 2018
Edited: Ancalagon8
on 24 Aug 2018
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
on 24 Aug 2018
Edited: Ancalagon8
on 24 Aug 2018
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
on 24 Aug 2018
Edited: jonas
on 24 Aug 2018
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
on 24 Aug 2018
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.
Walter Roberson
on 24 Aug 2018
In this situation, let it be imported as datetime, but then do
t{i} = data.Var1 - dateshift(data.Var1, 'start', 'day') + datetime(day);
jonas
on 24 Aug 2018
Edited: jonas
on 24 Aug 2018
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
on 24 Aug 2018
Yes it worked! Then i used
datetime = [t{:}];
in order to convert cell to datetime
Ancalagon8
on 24 Aug 2018
Thank you, much appreciate!
jonas
on 24 Aug 2018
Edited: jonas
on 24 Aug 2018
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
on 3 Sep 2018
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
More Answers (0)
See Also
Categories
Find more on Data Type Conversion in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!An Error Occurred
Unable to complete the action because of changes made to the page. Reload the page to see its updated state.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)