Can not Read CSV file with readtable function

I have CSV file with several line of headers. I tried to read the data simply as:

data = readtable('D96035_2019010300.xls');

However, I got error.

Is there any option to read it?

Thank you for help.

[Walter Roberson]

Xls files are not csv?

[Marzuki Marzuki]

Sorry walter, I receive the data from my friend and he said CSV, sorry the file is xls format

[Cris LaPierre]

The file you have shared is not an xls file. It's a csv file that has had its extension changed to xls.

Answer 1

[Cris LaPierre]

The error I encountered is because the extendion of your csv file was changed xls. Change it back to csv, and readtable should work fine.

data = readtable('D96035_2019010300.csv')

data = 4794x41 table
    ObsTime     DCnt    ST    RE      SondeN      FCnt    AGC    rcvFREQ    WM      WD       WS     Height    Xdistanc    Ydistanc    GF    HDP    PDP    GeodetLat    GeodetLon    V    Press0    Temp0    Humi0    FE     FRT      FTI     FVH       FVL      FSP1    FSP2     FSP3     FSP4    N     N1    N2    N3    N4    N5    N6    N7    N8
    ________    ____    __    __    __________    ____    ___    _______    __    ______    ____    ______    ________    ________    __    ___    ___    _________    _________    _    ______    _____    _____    __    ______    ___    ______    ______    ____    ____    ______    ____    __    __    __    __    __    __    __    __    __

    06:29:57      0     7     0     7.0074e+06    3356     0      400.7     1          0       0        7         0            0      2     0.8    1.4     3.5956       98.862      0    1011.3      25     85.1     0     364.62     0     327.77    355.91     0      2.21    193.03     0      12    2      6    12    13    15    19    20    25
    06:29:58      2     7     0     7.0074e+06    3358     0      400.7     1          0       0      7.1       2.7           -3      2     0.8    1.4     3.5956       98.862      0    1011.3    24.8       86     0     364.62     0      327.7    355.91     0         0         0     0      12    2      6    12    13    15    19    20    25
    06:29:59      4     7     0     7.0074e+06    3360     0      400.7     1          0       0      7.1       2.7           -3      2     0.8    1.4     3.5956       98.862      0    1011.3    24.7     86.2     0     364.62     0      327.7    355.91     0      2.21    193.05     0      12    2      6    12    13    15    19    20    25
    06:30:00      6     7     0     7.0074e+06    3362     0      400.7     1          0       0      7.3       2.7           -3      2     0.8    1.4     3.5956       98.862      0    1011.3    24.6     86.1     0     364.62     0     327.73    355.91     0         0         0     0      12    2      6    12    13    15    19    20    25
    06:30:01      8     7     0     7.0074e+06    3364     0      400.7     1          0       0      7.4       2.7           -3      2     0.8    1.6     3.5956       98.862      0    1011.3    24.6       86     0     364.62     0     327.73    355.91     0      2.21    193.15     0      12    2      6    12    13    15    19    24    25
    06:30:02     10     7     0     7.0074e+06    3366     0      400.7     1          0       0      7.5       2.7         -2.6      2     0.8    1.6     3.5956       98.862      0    1011.2    24.6       86     0     364.62     0     327.74    355.91     0         0         0     0      12    2      6    12    13    15    19    24    25
    06:30:03     12     7     0     7.0074e+06    3368     0      400.7     1          0       0      7.7       2.7         -2.5      2     0.8    1.4     3.5956       98.862      0    1011.2    24.6       86     0     364.62     0     327.74    355.91     0      2.21    193.21     0      12    2      6    12    13    15    19    20    25
    06:30:04     14     7     0     7.0074e+06    3370     0      400.7     1          0       0      7.9       2.7         -2.3      2     0.8    1.6     3.5956       98.862      0    1011.2    24.6       86     0     364.62     0     327.75    355.91     0         0         0     0      12    2      6    12    13    15    19    24    25
    06:30:05     16     7     0     7.0074e+06    3372     0      400.7     1      295.2    0.07        9       2.7         -2.3      2     0.8    1.5     3.5956       98.862      0    1011.1    24.6     85.9     0     364.62     0     327.75    355.91     0      2.21    193.22     0      12    2     12    13    15    19    20    24    25
    06:30:06     18     7     0     7.0074e+06    3374     0      400.7     1     260.73    0.36     11.2       2.7         -2.3      2     0.8    1.5     3.5956       98.862      0    1010.8    24.6       86     0     364.62     0     327.74    355.91     0         0         0     0      12    2     12    13    15    19    20    24    25
    06:30:07     20     7     0     7.0074e+06    3376     0      400.7     1      241.3    0.68       14       2.7         -2.3      2     0.7    1.4     3.5956       98.862      0    1010.5    24.6     86.1     0     364.62     0     327.74    355.91     0      2.21    193.22     0      12    2      5    12    13    15    19    20    24
    06:30:08     22     7     0     7.0074e+06    3378     0      400.7     1     251.05    0.97     17.3       2.7         -2.3      2     0.8    1.6     3.5956       98.862      0    1010.1    24.6     86.1     0     364.62     0     327.74    355.91     0         0         0     0      12    2      5    12    13    15    19    24    25
    06:30:09     24     7     0     7.0074e+06    3380     0      400.7     1     267.31    1.13     20.8       2.7         -2.3      2     0.8    1.6     3.5956       98.862      0    1009.7    24.6     85.9     0     364.62     0     327.74    355.91     0      2.21    193.26     0      12    2      5    12    13    15    19    24    25
    06:30:10     26     7     0     7.0074e+06    3382     0      400.7     1     266.07    1.46     24.4       2.7         -1.9      2     0.8    1.6     3.5956       98.862      0    1009.3    24.6     85.8     0     364.62     0     327.74     355.9     0         0         0     0      12    2      5    12    13    15    19    24    25
    06:30:11     28     7     0     7.0074e+06    3384     0      400.7     1     254.38    1.56     28.2       4.2         -1.5      2     0.8    1.6     3.5956       98.862      0    1008.9    24.6     85.7     0     364.62     0     327.74     355.9     0      2.21    193.26     0      12    2      5    12    13    15    19    24    25
    06:30:12     30     7     0     7.0074e+06    3386     0      400.7     1     249.98    1.68     32.2       4.2         -0.8      2     0.8    1.4     3.5956       98.862      0    1008.4    24.7     85.6     0     364.62     0     327.73    355.88     0         0         0     0      12    2      5     6    12    13    15    20    25

[Marzuki Marzuki]

Thank you Cris, I simply change the the file to csv, I got also 4794x41 table. Thank you. But, suppose that I want to read the data without changing it to CSV, is not possible. Because I have many data, it will take time to change it manually to CSV

[Cris LaPierre]

This is just a warning. Columns in tables are separate variables, and are subject to MATLAB's variable naming rules. When they don't, MATLAB will automatically change them so they do and present this warning message.

Depending on your version of MATLAB, there is an option to preserve the original variable names. See the VariableNamingRule section here.

[Walter Roberson]

data = readtable('D96035_2019010300.xls', 'filetype', 'text');

[Marzuki Marzuki]

Thank you walter, I got following error:

Error using readtable (line 216)

Unable to open file 'D96035_2019010300.xls'.

[Cris LaPierre]

You probably have the file open outside of MATLAB. Close that file.

[Marzuki Marzuki]

Hi @Cris LaPierre, I tried to use following code:

data = readtable('D96035_2016010100.CSV', 'filetype', 'text');

or

data = readtable('D96035_2016010100.CSV');

to read the attached file, I got only 1 x 24 table. It sould be 6000s x41 table, I don't know why these two codes does not work for this data, because the data looks the same as previously attachied one. Hope your help. Thank you.

[Walter Roberson]

data = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/629360/D96035_2016010100.CSV', 'filetype', 'text', 'headerlines', 6);
summary(data)

Variables:

    ObsTime: 6307×1 duration

        Values:

            Min       06:22:56
            Median    07:15:45
            Max       08:08:52

    DCnt: 6307×1 double

        Values:

            Min            0  
            Median      5490  
            Max        11866  

    ST: 6307×1 double

        Values:

            Min          6    
            Median       7    
            Max          7    

    RE: 6307×1 double

        Values:

            Min          0    
            Median       0    
            Max          0    

    SondeN: 6307×1 double

        Values:

            Min             7.4349e+06  
            Median          7.4349e+06  
            Max             1e+07       
            NumMissing      66          

    FCnt: 6307×1 double

        Values:

            Min             2      
            Median          6211   
            Max             12618  
            NumMissing      65     

    AGC: 6307×1 double

        Values:

            Min          63   
            Median      115   
            Max         134   

    rcvFREQ: 6307×1 double

        Values:

            Min        402.89 
            Median      402.9 
            Max         402.9 

    WM: 6307×1 double

        Values:

            Min          1    
            Median       1    
            Max          1    

    WD: 6307×1 double

        Values:

            Min             0 
            Median     235.21 
            Max        359.89 

    WS: 6307×1 double

        Values:

            Min            0  
            Median      7.06  
            Max        31.53  

    Height: 6307×1 double

        Values:

            Min         10.5  
            Median     13490  
            Max        32665  

    Xdistanc: 6307×1 double

        Values:

            Min             -1950.9  
            Median          6328.9   
            Max             19582    
            NumMissing      147      

    Ydistanc: 6307×1 double

        Values:

            Min             -2737.9  
            Median          4408.2   
            Max             10374    
            NumMissing      147      

    GF: 6307×1 double

        Values:

            Min              0    
            Median           2    
            Max              4    
            NumMissing       89   

    HDP: 6307×1 double

        Values:

            Min             0     
            Median          0.8   
            Max             99.9  
            NumMissing      58    

    PDP: 6307×1 double

        Values:

            Min             0     
            Median          0     
            Max             99.9  
            NumMissing      58    

    GeodetLat: 6307×1 double

        Values:

            Min             3.5709  
            Median          3.6355  
            Max             3.6894  
            NumMissing      147     

    GeodetLon: 6307×1 double

        Values:

            Min             98.845  
            Median          98.919  
            Max             99.039  
            NumMissing      147     

    Var20: 6307×1 double

        Values:

            Min          0    
            Median       0    
            Max          7    

    Var21: 6307×1 double

        Values:

            Min             7.5    
            Median          169.4  
            Max             1014   
            NumMissing      12     

    Press0: 6307×1 double

        Values:

            Min             -83.9  
            Median          -48.8  
            Max             26     
            NumMissing      100    

    Temp0: 6307×1 double

        Values:

            Min             3.7   
            Median          48    
            Max             98.9  
            NumMissing      164   

    Humi0: 6307×1 double

        Values:

            Min             0     
            Median          0     
            Max             8661  
            NumMissing      65    

    FE: 6307×1 double

        Values:

            Min             0       
            Median          454.85  
            Max             455.05  
            NumMissing      65      

    FRT: 6307×1 double

        Values:

            Min             403    
            Median          500.3  
            Max             561.5  
            NumMissing      65     

    FTI: 6307×1 double

        Values:

            Min        327.87 
            Median     327.95 
            Max        327.99 

    FVH: 6307×1 double

        Values:

            Min             2.8   
            Median          2.8   
            Max             2.8   
            NumMissing      90    

    FVL: 6307×1 double

        Values:

            Min              0    
            Median           0    
            Max              0    
            NumMissing       73   

    FSP1: 6307×1 double

        Values:

            Min             0       
            Median          0       
            Max             655.35  
            NumMissing      79      

    FSP2: 6307×1 double

        Values:

            Min             655.32  
            Median          655.35  
            Max             655.35  
            NumMissing      14      

    FSP3: 6307×1 double

        Values:

            Min             0       
            Median          0       
            Max             655.35  
            NumMissing      65      

    FSP4: 6307×1 double

        Values:

            Min          0    
            Median       8    
            Max          8    

    N: 6307×1 double

        Values:

            Min              1    
            Median           2    
            Max              12   
            NumMissing       60   

    N1: 6307×1 double

        Values:

            Min              2    
            Median           5    
            Max              13   
            NumMissing       60   

    N2: 6307×1 double

        Values:

            Min              3    
            Median           6    
            Max              17   
            NumMissing       60   

    N3: 6307×1 double

        Values:

            Min              4    
            Median           12   
            Max              20   
            NumMissing       60   

    N4: 6307×1 double

        Values:

            Min              5    
            Median           13   
            Max              25   
            NumMissing       60   

    N5: 6307×1 double

        Values:

            Min              6    
            Median           17   
            Max              28   
            NumMissing       66   

    N6: 6307×1 double

        Values:

            Min              7    
            Median           19   
            Max              28   
            NumMissing       84   

    N7: 6307×1 double

        Values:

            Min             8     
            Median          20    
            Max             28    
            NumMissing      690   

    N8: 6307×1 double

        Values:

            Min             NaN   
            Median          NaN   
            Max             NaN   
            NumMissing      6307  

[Marzuki Marzuki]

Thank you @Walter Roberson Does it work only for R2021 a version ?, I am working with 2020 version and found same result as before (1 x 24 table.) when I used this code: readtable('D96035_2016010100.CSV', 'filetype', 'text', 'headerlines', 6);

[Walter Roberson]

I just tried in R2020a on Mac using

data = readtable('D96035_2016010100.CSV', 'filetype', 'text', 'headerlines', 6);

and got back a table that is 6307 rows and 42 columns.

[Marzuki Marzuki]

Sorry @Walter Roberson, after cheking my mathworks account, it seems my Matlab 2019b not 2020a. Does not it work for R2019 b version ?

[Walter Roberson]

filename = 'D96035_2016010100.CSV';
opt = detectImportOptions(filename, 'headerlines', 6);
data = readtable(filename, opt);