MATLAB: Error using textscan to read a csv file with number and text and date in one file

textscan

Error using textscan Unable to read the DATETIME data with the format 'HH:mm:ss'. If the data is not a time, use %q to get string data.

One example data file I used is attached.

I tried several things, but no success. Please advise a good way of reading the data.

I try to write a code to read such a file. However, importdata can't handle the mixing types, and readtable don't skip the first 4 lines. Another try of using textscan as below gave me errors:

formatSpec = '%{HH:mm:ss}D,%C,%f,%f,%f,%f,%f,%f,%f,%{HH:mm:ss}D,%f,%f,%f,%f'; 
fid=fopen(cpc_name);
        for j1=1:4
        tmp1=fgetl(fid);
            if j1==2
                dstr=tmp1(6:14);
            end
        end
        C=textscan(fid,formatSpec);
        fclose(fid);

Best Answer

Fan, leave the commas out fo the format and use readtable:

>> t = readtable('test1_V6.csv','Format','%{HH:mm:ss}D%f%C%f%f%f%f%f%f%f%{HH:mm:ss}D%f%f%f%f','Header',3)
Warning: Variable names were modified to make them valid MATLAB identifiers. The original names are saved in the
VariableDescriptions property. 
t =
      Time      Comment    ModuleID    Alt_m_    P_mbar_    T_degC_    x_RH    Wind_degrees_    Wind_m_s_    Supply_V_    UTCTime     Latitude_deg_    Longitude_deg_    Course_deg_    Speed_m_s_
    ________    _______    ________    ______    _______    _______    ____    _____________    _________    _________    ________    _____________    ______________    ___________    __________
    23:09:10    NaN        F667D9      54.2      999.9      16.3       39.2    236              4.1          11.8         23:09:00    70.495           -149.89           78             0.02      
    23:09:11    NaN        F667D9      54.2      999.9      16.3         39    237              3.9          11.8         23:09:05    70.495           -149.89           78             0.01      
    23:09:15    NaN        F667D9      54.2      999.8      16.3       39.1    236              4.1          11.8         23:09:10    70.495           -149.89           78             0.01      
    23:09:20    NaN        F667D9      54.2      999.9      16.3       38.9    236              4.1          11.8         23:09:15    70.495           -149.89           78             0.01      
    23:09:25    NaN        F667D9      54.2      999.8      16.3       38.8    236              3.9          11.8         23:09:20    70.495           -149.89           78             0.02      
    23:09:30    NaN        F667D9      54.2      999.8      16.3       38.7    236              4.1          11.8         23:09:25    70.495           -149.89           78             0.02      
    23:09:32    NaN        F667D9      54.2      999.7      16.3       38.8    236              3.9          11.8         23:09:30    70.495           -149.89           78             0.02      
    23:09:36    NaN        F667D9      54.2      999.8      16.3       39.4    235              4.1          11.8         23:09:35    70.495           -149.89           78             0.01      
    23:09:41    NaN        F667D9      54.2      999.7      16.3       38.8    236              3.9          11.8         23:09:40    70.495           -149.89           78             0.01      
    23:09:46    NaN        F667D9      54.2      999.8      16.3         39    236              4.1          11.8         23:09:45    70.495           -149.89           78             0.01      
    23:09:51    NaN        F667D9      54.2      999.9      16.3       38.8    235              3.9          11.8         23:09:50    70.495           -149.89           78                0      
    23:09:56    NaN        F667D9      54.2      999.8      16.3       39.1    235              4.1          11.8         23:09:55    70.495           -149.89           78             0.01

The times had no date portion, so they were read as "today", but the file header indocates that they're from 6/11/2016. Modify the two time variables accordingly:

>> t.Time = datetime(2016,6,11) + timeofday(t.Time);
>> t.UTCTime = datetime(2016,6,11) + timeofday(t.UTCTime)
t =
            Time            Comment    ModuleID    Alt_m_    P_mbar_    T_degC_    x_RH    Wind_degrees_    Wind_m_s_    Supply_V_          UTCTime           Latitude_deg_    Longitude_deg_    Course_deg_    Speed_m_s_
    ____________________    _______    ________    ______    _______    _______    ____    _____________    _________    _________    ____________________    _____________    ______________    ___________    __________
    11-Jun-2016 23:09:10    NaN        F667D9      54.2      999.9      16.3       39.2    236              4.1          11.8         11-Jun-2016 23:09:00    70.495           -149.89           78             0.02      
    11-Jun-2016 23:09:11    NaN        F667D9      54.2      999.9      16.3         39    237              3.9          11.8         11-Jun-2016 23:09:05    70.495           -149.89           78             0.01      
    11-Jun-2016 23:09:15    NaN        F667D9      54.2      999.8      16.3       39.1    236              4.1          11.8         11-Jun-2016 23:09:10    70.495           -149.89           78             0.01      
    11-Jun-2016 23:09:20    NaN        F667D9      54.2      999.9      16.3       38.9    236              4.1          11.8         11-Jun-2016 23:09:15    70.495           -149.89           78             0.01      
    11-Jun-2016 23:09:25    NaN        F667D9      54.2      999.8      16.3       38.8    236              3.9          11.8         11-Jun-2016 23:09:20    70.495           -149.89           78             0.02      
    11-Jun-2016 23:09:30    NaN        F667D9      54.2      999.8      16.3       38.7    236              4.1          11.8         11-Jun-2016 23:09:25    70.495           -149.89           78             0.02      
    11-Jun-2016 23:09:32    NaN        F667D9      54.2      999.7      16.3       38.8    236              3.9          11.8         11-Jun-2016 23:09:30    70.495           -149.89           78             0.02      
    11-Jun-2016 23:09:36    NaN        F667D9      54.2      999.8      16.3       39.4    235              4.1          11.8         11-Jun-2016 23:09:35    70.495           -149.89           78             0.01      
    11-Jun-2016 23:09:41    NaN        F667D9      54.2      999.7      16.3       38.8    236              3.9          11.8         11-Jun-2016 23:09:40    70.495           -149.89           78             0.01      
    11-Jun-2016 23:09:46    NaN        F667D9      54.2      999.8      16.3         39    236              4.1          11.8         11-Jun-2016 23:09:45    70.495           -149.89           78             0.01      
    11-Jun-2016 23:09:51    NaN        F667D9      54.2      999.9      16.3       38.8    235              3.9          11.8         11-Jun-2016 23:09:50    70.495           -149.89           78                0      
    11-Jun-2016 23:09:56    NaN        F667D9      54.2      999.8      16.3       39.1    235              4.1          11.8         11-Jun-2016 23:09:55    70.495           -149.89           78             0.01

Related Solutions

MATLAB: Copy 1 part of text in complex text file

This copies the exact text from the input file into the output file, starting from "output final hypocenters" until the end of that block of data. The input and output files are attached to this answer:

ofh = '~~~ output final hypocenters';
[fi1,msg] = fopen('output.txt','rt');
[fi2,msg] = fopen('final_hypos.out','wt');
assert(fi1>=3,msg)
assert(fi2>=3,msg)
str = '';
while ~strncmp(str,ofh,numel(ofh))
    str = strtrim(fgetl(fi1));
end
str = '';
while isempty(strtrim(str))
    str = fgetl(fi1);
end
while ~isempty(strtrim(str)) && ~strncmp(strtrim(str),'~~~',3)
    fprintf(fi2,'%s\n',str);
    str = fgetl(fi1);
end
fclose(fi1);
fclose(fi2);

The output file contains this:

      date    origin   latitude longitude  depth  mag  no  rms      x      y      z
   1 960415 0839 55.21 21.5634N 105.7475E  19.88 0.00  12 0.982 -227.88 -35.17  19.88
   2 960622 1839 26.78 21.2159N 103.2645E   8.06 0.00   7 9.627   29.05 -73.64   8.06
   3 960928 1314 14.53 19.4704N 102.8734E  13.75 0.00  11 6.272   69.97-266.92  13.75
   4 961221 1751  4.82 20.4105N 100.2162E   8.16 0.00   316.790  345.75-162.82   8.16
   5 970702 1557  1.99 20.9215N 102.6076E   9.98 0.00   8 5.114   97.20-106.25   9.98
   6 971024 0930 40.25 22.5243N 101.6269E   9.57 0.00   7 8.966  197.79  71.23   9.57
   7 971119 0651 53.92 22.0071N 101.7117E  10.00 0.00   412.446  189.38  13.96  10.00
   8 980210 0818  3.39 21.1240N 104.8061E  15.83 0.00  14 1.284 -130.67 -83.82  15.83
   9 980213 1458  5.06 23.6673N 105.8520E  18.35 0.00  11 3.341 -236.92 197.80  18.35
  10 980213 2315  0.21 23.9868N 102.7740E   8.17 0.00   4 7.048   79.09 233.18   8.17
  11 980514 0334 44.95 20.9545N 103.3424E   9.36 0.00  13 5.413   21.00-102.59   9.36
... lots of lines here
 832 180220 1409 56.30 24.5814N 101.0812E  10.00 0.00   810.252  252.18 299.01  10.00
 833 180228 1723 38.50 23.0752N 102.9323E  18.14 0.00   8 4.553   63.06 132.23  18.14
 834 180312 2008 32.26 19.9176N 105.2585E  15.63 0.00  15 2.393 -178.27-217.41  15.63
 835 180313 0447 46.58 22.5116N 103.9298E   8.69 0.00   8 0.751  -39.68  69.82   8.69
 836 180318 2053 54.36 21.7291N 104.2485E   8.31 0.00  10 0.617  -72.74 -16.82   8.31
 837 180326 0439 56.97 21.5877N 103.9346E  21.37 0.00  11 1.062  -40.30 -32.47  21.37

MATLAB: Contour Plot: DATA COLLECTED vs TIME in date and hours

The first thing you might try is to read your date/time stamps into MATLAB as strings. One way to accomplish this outcome would be to place the date/time data into a plain text file, with one date/time stamp per line of text. Then save the file as data.txt or something similar.

You can then read the date/time stamps into MATLAB from the text file and convert the strings into an array of datenums.

txt = fopen('data.txt','r');
k = 0;
while ~feof(txt)
  k = k + 1;
  aStr = fgetl(txt);
  dtStamp(k) = datenum(aStr);
end
fclose(txt);

Once you have the date/time stamps in datenum form, you can always retrieve the individual parts as follows:

yr = year(dtStamp(k));
mo = month(dtStamp(k));
dy = day(dtStamp(k));

etc.

That should get you started.

HTH.

Rick

Best Answer

Related Solutions

MATLAB: Copy 1 part of text in complex text file

MATLAB: Contour Plot: DATA COLLECTED vs TIME in date and hours

Related Question