How to convert a column in matrix from string to numeric

I am having a trouble of converting a string column in a table to a numeric type. I have a table imported from .csv file containing 6 columns with headers and 4 of them should be numeric and one is date in format yyyyMMddhhmm, delimitator is ','.

When I load it using readtable, it assumes that everything is a string and I can't convert the 4 columns to a numeric type. I have tried to convert it through many something2somethingelse converters, did not work.

I tried even:

data = readtable(['data' cell2mat(indexVars(i))], 'Format','%s%f%{yyyyMMddhhmm}D%f%f%f');

It returned:

Error using readtable (line 216)

Unable to read the entire file. You may need to specify a different format, delimiter, or number of header

lines.

Note: readtable detected the following parameters:

'Delimiter', ',', 'HeaderLines', 0, 'ReadVariableNames', true

Any ideas?

[Walter Roberson]

Can you attach part of the csv file for testing?

[Stepan Subik]

Here is a part of the file

[dpb]

As suspectected, the hours are 0-23 hours; the last two records of the file time field are

 194905011800 
 194905011900 

which are 18 and 19 hours in 24-hr time, not 12-hr time so must use 'HH' instead of 'hh'

>> datetime('194905011800','InputFormat','yyyyMMddhhmm')
Error using datetime (line 636)
Unable to convert '194905011800' to datetime using the format 'yyyyMMddhhmm'. 
>>
>> datetime('194905011800','InputFormat','yyyyMMddHHmm')
ans = 
  datetime
   01-May-1949 18:00:00
>> 

See the doc for datetime format strings for all the details.

Answer 1

[dpb]

data = readtable(['data' cell2mat(indexVars(i))], 'Format','%s%f%{yyyyMMddhhmm}D%f%f%f');

'hh' is for 12-hr day; 'HH' is for 24-hr day. If is 12-hour, need the AM/PM suffix is probably the problem I'd guess.

But, you didn't show us a sample of the input time string to be able to confirm just what is the actual input format being used; but from the verbal description would seem to have to have used 24-hr days. So, use 'HH' instead.

BTW, if you have R2016(b?) or later, detectImportOptions often will do better as does more thorough probing of the file than does readtable on its own.

ADDENDUM:

As suspected, use 'HH' instead of 'hh' to match the input file date format (24hr clock)

 >> doc datetime
>> datetime('194905011800','InputFormat','yyyyMMddhhmm')
Error using datetime (line 636)
Unable to convert '194905011800' to datetime using the format 'yyyyMMddhhmm'. 
>> datetime('194905011800','InputFormat','yyyyMMddHHmm')
ans = 
  datetime
   01-May-1949 18:00:00
>> 

I'll grant seems like the error message could be far more informative than is and in this case tell you precisely what was wrong. In more general case at least could identify which field it failed on.

[Stepan Subik]

Hi, the file is now in the comments and I have R2018b. How can it help?

[dpb]

Did you just fix the format string as I suggested and retry?

If by "it" you mean detectImportOptions as described it probes the file more deeply than does readtable and has better chance thereby of determing the data/time format automagically. You pass the returned object.

See the doc on both...

[Stepan Subik]

Thank you, the detectImportOptions did the job ;-)