How import data and change data and time format?

formatspec='%f%f%f%f%f%f%{MM-dd-yyyy HH:mm:ss}D%{MM-dd-yyyy HH:mm:ss.SSSSSS}D'
formatspec=variable
%f- format for MATLAB, floating decimal, not sure why so many 
{}-desired format for code 
D%- no idea
D- no idea

If someone could please help me to understand what this string means, I could appreciate it. I received help on this, but cannot remember why we developed the string in this way.

Answer 1

[Walter Roberson]

%{....}D is a date specification. %{MM-dd-yyyy HH:mm:ss}D is equivalent to asking to

datetime(TheInputString, 'Format', 'MM-dd-yyyy HH:mm:ss')

which is numeric month, numeric day of month, four digit year, 24-hour type hour, two digit minute, two digits seconds.

and %{MM-dd-yyyy HH:mm:ss.SSSSSS}D is equivalent to asking to

datetime(TheInputString, 'Format', 'MM-dd-yyyy HH:mm:ss.SSSSSS')

which is numeric month, numeric day of month, four digit year, 24-hour type hour, two digit minute, two digits seconds, 6 digit fractions of a second.

Note: if you are using textscan then the %{}D formats given that way can have difficulty if the Whitespace parameter includes space: MATLAB would generally see the space as indicating the end of the field before trying to parse it as a date. readtable() is better at handling that.

[Wafa'a Shanti]

Thank you for this. The program worked for this first bit of information I loaded into the program, and now I am using new information that is in a different form. "TheInputString", is that the format of the information being brought into the program? For instance, the date is in the form of yyyyMMdd- would I put that where "TheInputString" is and then format to how I want? (My thoughts below)

 formatspec=datetime(yyyyMMdd HH:mm:ss, 'Format', 'MM-dd-yyyy HH:mm:ss)

And if they are in different columns in the array being created, would I then create two different datetime sequences?

Thank you!

[Walter Roberson]

Oops, I made a mistake above. Where I said that datetime() would be called with those particular strings as the Format parameter, I should have said the InputFormat command. So where I said

datetime(TheInputString, 'Format', 'MM-dd-yyyy HH:mm:ss')

I should have written

datetime(TheInputString, 'InputFormat', 'MM-dd-yyyy HH:mm:ss')

The %{...}D string you are using is a format specification that can be used only with textscan() or readtable(), but the part inside the %{...}D must be a valid 'InputFormat' for datetime() calls. That is, textscan() and readtable() will take the input text field and pass it to datetime using the part inside the {} as the 'InputFormat'. But there can be circumstances under which you call datetime() yourself.

If you have input that is in the form yyyyMMdd and you are using textscan() or readtable() then use '%{yyyyMMdd}D' for that field.

datetime() does have a Format parameter, but it is for controlling how the datatime displays. So it would be valid to call,

datetime(TheInputString, 'InputFormat', 'yyyyMMdd', 'Format', 'MM-dd-yyyy HH:mm:ss')

to tell it that TheInputString is a string to be interpreted as yyyyMMdd but to be displayed as MM-dd-yyyy HH:mm:ss

Example:

TheInputString = '20171225';
datetime(TheInputString, 'InputFormat', 'yyyyMMdd', 'Format', 'MM-dd-yyyy HH:mm:ss')

or

TheInputString = '20171225';
temp = textscan(TheInputString, '%{yyyyMMdd}D');
temp{1}.Format = 'MM-dd-yyyy HH:mm:ss';

[Wafa'a Shanti]

Thank you for your help- I really appreciate it! Now that I have the format figured out, I am getting a error in regards I think to the data itself:

Error using readtable (line 198)

Unable to read the DATETIME data with the format 'MM-dd-yyyy HH:mm:ss'. If the data is not a time, use
%q to get text data.

Note: readtable detected the following parameters:
'ReadVariableNames', true

Error in OSU (line 3)
NumericValueTable=readtable(datapath,'Delimiter',',','HeaderLines',0,'Format',formatspec);

I looked up what 'ReadVariableNames' is, and from my understanding it is the same as the 'delimiter' in the sense it disregards the first line of input information. So, naturally, I attempted to add both, and did not make it very far. I looked at the input file to see what is wrong with line 198, and the data is exactly like the data before and after, with no letters or anything. Is there something I am not understanding?

[Walter Roberson]

The "line 198" refers to line 198 of readtable.m not to line 198 of your data.

What are the first two lines of your file? What is currently assigned to formatspec ?

[Wafa'a Shanti]

datapath='C:\Users\Faye\Documents\MATLAB\OSUr1.csv';
formatspec='%f%f%f%f%f%f%{MM-dd-yyyy HH:mm:ss}D'; 
NumericValueTable=readtable(datapath,'Delimiter',',','HeaderLines',0,'Format',formatspec)

I thought the problem was in the second line with formatting the code, but it resides in the third line. These are the only three lines in the program, so the line 198 of readtable.m is something I am confused about.

[Walter Roberson]

readtable.m is the Mathworks-provided code that implements readtable(). line 198 is the line that sent out the error "Unable to read the DATETIME data with the format" etc.; it tells you which code was complaining, but has nothing to do with which line of your file has a problem.

What are the first two lines of your OSUr1.csv ?

[Wafa'a Shanti]

• datapath='C:\Users\Faye\Documents\MATLAB\OSUr1.csv';
• formatspec='%f%f%f%f%f%f%{MM-dd-yyyy HH:mm:ss}D';

When I enter only these two lines into my code, I receive no error. It is only when I add the third line (NumericValueTable=readtable(datapath,'Delimiter',',','HeaderLines',0,'Format',formatspec);) the program comes up with an error.

[Walter Roberson]

I understand. But you need to attach the file OSUr1.csv for me to look at to tell you what formatspec you need to use instead of the one you have.

Answer 2

[Usama Khalid]

Facing the same error, any help?

ds.SelectedVariableNames = {'Date_Time','Usage','AC_DR','UPS','LR','Kitchen','AC_Dr','AC_BR'};

ds.SelectedFormats = {'%{MM/dd/yyyy HH:mm:ss}D','%f','%f','%f','%f','%f','%f','%f'};

PreRaw = readall(ds);

[Usama Khalid]

the error: