readtimetable does not read the entire Excel file

I have several Excel files with date and time and then some ten columns. The number of lines is 9000.

When I read them with readtimetable, I get them correct in six cases. The other four get cut at line 2881, 4345, 745 and 745 respectively. The first problematic one had dashes instead of the values in a column from the point that it did not work. I put 0 there and still it does not work.

What more can I try?

[Mathieu NOE]

hi

can you share an extract of the excel files that cause the issue ?

[Per Westerlund]

Yes, in principle, since I downloaded them. They are quite large, so I will make an extract.

[Per Westerlund]

Here it comes.

[Cris LaPierre]

I don't see dashes or zeros in the last 4 columns of this file. You can share a larger file than this. Perhaps one with at least the first 3000 rows? At least something that will demonstrate the same errors you are getting. That allows us to test it out. Consider sharing the code you are using to read in the files as well.

data = readtable('Svk_2017_del.xlsx','NumHeaderLines',6)

data = 31×11 table
            Var1             Var2      Var3      Var4      Var5      Var6       Var7      Var8        Var9      Var10     Var11 
    ____________________    ______    ______    ______    ______    _______    ______    _______    ________    _____    _______

    01-Jan-2017 01:00:00    -13047    3429.5    2381.2    9074.7    0.85076    1076.5     1.1083    0.035288    15964    -2920.3
    01-Jan-2017 02:00:00    -12824      3290    2355.1    9074.9    0.84465    1095.5     1.0244    0.026189    15817    -2996.5
    01-Jan-2017 03:00:00    -12668    3053.2      2268    9077.4    0.82665    1142.3     0.9999    0.019635    15543    -2878.1
    01-Jan-2017 04:00:00    -12629    2932.2      2311      9078    0.80068    1158.5    0.87338    0.018089    15481    -2855.7
    01-Jan-2017 05:00:00    -12708    2787.2    2327.5    9079.9    0.67075    1169.1    0.69828    0.017403    15365      -2660
    01-Jan-2017 06:00:00    -12924    2661.7    2555.7    9082.6    0.63896    1195.5    0.00409    0.019086    15496    -2573.9
    01-Jan-2017 07:00:00    -13227      2611    2727.8    9082.7    0.57101    1184.2    0.67683    0.023495    15607    -2382.6
    01-Jan-2017 08:00:00    -13588    2553.5      2860    9082.7    0.55226    1165.9    0.61035    0.025813    15663    -2077.1
    01-Jan-2017 09:00:00    -13897      2392    3300.6    9084.7    0.62331    1195.9    0.10089      0.1066    15974    -2078.9
    30-Apr-2017 13:00:00    -13971    1602.6    3829.9    8126.6    0.56972    940.81    0.20466      62.945    14564    -592.63
    30-Apr-2017 14:00:00    -13776    1679.7    3695.7    8125.3     3.1852    957.29    0.19749      53.797    14515    -739.39
    30-Apr-2017 15:00:00    -13759    1822.6    3645.2    8126.3    0.52958    948.42    0.22002       40.43    14584    -824.86
    30-Apr-2017 16:00:00    -13826    1818.1      3855    8126.5    0.57314    941.25    0.22412      24.477    14766    -940.32
    30-Apr-2017 17:00:00    -13578    1739.5    4080.5    8127.2    0.53474    926.32    0.23595      10.796    14885    -1306.9
    30-Apr-2017 18:00:00    -13366    1557.4    4161.5    8128.7    0.61035    914.91    0.21257      2.4397    14766    -1399.8
    30-Apr-2017 19:00:00    -13408    1394.8    4597.6    8127.4    0.60448    928.66    0.21304     0.24832    15050    -1641.4

[Per Westerlund]

Here is the command and the file with some 3000 rows:

f2017t = readtimetable("n_fot2017-01-12 mod del.xls", "VariableNamingRule", "preserve")

f2017t = 2881×11 timetable
    Time series product instance (Beskrivning)    Kärnkraft prod. per CO//SWE///    Gasturbin/diesel prod. per CO//SWE///    Vindkraft prod. per CO//SWE///    Netto Exp/imp. per CO//SWE///    Ospec. prod. per CO//SWE///    Total prod. per CO//SWE///    Solkraft prod. per CO//SWE///    Vågkraft prod. per CO//SWE///    Summa förbr. per CO//SWE///    Övr.värmekraft prod. per CO//SWE///    Vattenkraft prod. per CO//SWE///
    __________________________________________    ______________________________    _____________________________________    ______________________________    _____________________________    ___________________________    __________________________    _____________________________    _____________________________    ___________________________    ___________________________________    ________________________________

    NaT                                                          NaN                                   NaN                                  NaN                               NaN                             NaN                          NaN                              NaN                            NaN                              NaN                                NaN                                   NaN             
    01-Jan-2017 00:00:00                                      9074.4                               0.85744                               3637.8                           -2919.6                         0.92037                        16256                         0.035938                              0                           -13339                             1125.7                                2415.8             
    01-Jan-2017 01:00:00                                      9074.7                               0.85076                               3429.5                           -2920.3                          1.1083                        15964                         0.035288                              0                           -13047                             1076.5                                2381.2             
    01-Jan-2017 02:00:00                                      9074.9                               0.84465                                 3290                           -2996.5                          1.0244                        15817                         0.026189                              0                           -12824                             1095.5                                2355.1             
    01-Jan-2017 03:00:00                                      9077.4                               0.82665                               3053.2                           -2878.1                          0.9999                        15543                         0.019635                              0                           -12668                             1142.3                                  2268             
    01-Jan-2017 04:00:00                                        9078                               0.80068                               2932.2                           -2855.7                         0.87338                        15481                         0.018089                              0                           -12629                             1158.5                                  2311             
    01-Jan-2017 05:00:00                                      9079.9                               0.67075                               2787.2                             -2660                         0.69828                        15365                         0.017403                              0                           -12708                             1169.1                                2327.5             
    01-Jan-2017 06:00:00                                      9082.6                               0.63896                               2661.7                           -2573.9                         0.00409                        15496                         0.019086                              0                           -12924                             1195.5                                2555.7             
    01-Jan-2017 07:00:00                                      9082.7                               0.57101                                 2611                           -2382.6                         0.67683                        15607                         0.023495                              0                           -13227                             1184.2                                2727.8             
    01-Jan-2017 08:00:00                                      9082.7                               0.55226                               2553.5                           -2077.1                         0.61035                        15663                         0.025813                              0                           -13588                             1165.9                                  2860             
    01-Jan-2017 09:00:00                                      9084.7                               0.62331                                 2392                           -2078.9                         0.10089                        15974                           0.1066                              0                           -13897                             1195.9                                3300.6             
    01-Jan-2017 10:00:00                                      9085.5                                1.0216                                 2243                           -1612.9                         0.00508                        16105                          0.90292                              0                           -14494                             1212.7                                3561.6             
    01-Jan-2017 11:00:00                                      9082.8                               0.96721                               2080.4                             -1197                         0.00387                        16146                           2.4625                              0                           -14951                               1221                                3758.2             
    01-Jan-2017 12:00:00                                      9085.3                                1.0963                               1968.2                           -1153.4                         0.00167                        16371                           2.5767                              0                           -15218                             1217.6                                4095.8             
    01-Jan-2017 13:00:00                                      9085.1                                1.0842                               2011.4                             -1079                          0.0002                        16513                           1.7942                              0                           -15435                             1232.6                                4181.4             
    01-Jan-2017 14:00:00                                      9084.7                               0.97908                               2076.3                           -1023.3                           9e-05                        16764                          0.43023                              0                           -15741                               1246                                4355.6             

[Cris LaPierre]

I don't see any issues importing this file either. Do you?

[Per Westerlund]

The file has 2992 rows of data and I get only 2881 rows when I import it.

Answer 1

[Drew]

Your .xls file has a hidden worksheet before the visible worksheet. The hidden worksheet has fewer rows. By default, readtable is reading the first worksheet, which happens to be hidden. You can specify the worksheet name to read the visible worksheet you are looking for, and the result has 2992 rows as expected.

f2017t = readtimetable("n_fot2017-01-12 mod del.xls","Sheet","Förb + prod i Sverige", "VariableNamingRule", "preserve")

f2017t = 2992×10 timetable
            Tid             Total förbrukning    Vindkraft    Vattenkraft    Kärnkraft    Gasturbin/diesel    Övr.värmekraft    Ospec. prod.    Solkraft    Total produktion    Import/export
    ____________________    _________________    _________    ___________    _________    ________________    ______________    ____________    ________    ________________    _____________

    01-Jan-2017 00:00:00         -13339           3637.8        2415.8        9074.4          0.85744             1125.7          0.92037       0.035938         16256             -2919.6   
    01-Jan-2017 01:00:00         -13047           3429.5        2381.2        9074.7          0.85076             1076.5           1.1083       0.035288         15964             -2920.3   
    01-Jan-2017 02:00:00         -12824             3290        2355.1        9074.9          0.84465             1095.5           1.0244       0.026189         15817             -2996.5   
    01-Jan-2017 03:00:00         -12668           3053.2          2268        9077.4          0.82665             1142.3           0.9999       0.019635         15543             -2878.1   
    01-Jan-2017 04:00:00         -12629           2932.2          2311          9078          0.80068             1158.5          0.87338       0.018089         15481             -2855.7   
    01-Jan-2017 05:00:00         -12708           2787.2        2327.5        9079.9          0.67075             1169.1          0.69828       0.017403         15365               -2660   
    01-Jan-2017 06:00:00         -12924           2661.7        2555.7        9082.6          0.63896             1195.5          0.00409       0.019086         15496             -2573.9   
    01-Jan-2017 07:00:00         -13227             2611        2727.8        9082.7          0.57101             1184.2          0.67683       0.023495         15607             -2382.6   
    01-Jan-2017 08:00:00         -13588           2553.5          2860        9082.7          0.55226             1165.9          0.61035       0.025813         15663             -2077.1   
    01-Jan-2017 09:00:00         -13897             2392        3300.6        9084.7          0.62331             1195.9          0.10089         0.1066         15974             -2078.9   
    01-Jan-2017 10:00:00         -14494             2243        3561.6        9085.5           1.0216             1212.7          0.00508        0.90292         16105             -1612.9   
    01-Jan-2017 11:00:00         -14951           2080.4        3758.2        9082.8          0.96721               1221          0.00387         2.4625         16146               -1197   
    01-Jan-2017 12:00:00         -15218           1968.2        4095.8        9085.3           1.0963             1217.6          0.00167         2.5767         16371             -1153.4   
    01-Jan-2017 13:00:00         -15435           2011.4        4181.4        9085.1           1.0842             1232.6           0.0002         1.7942         16513               -1079   
    01-Jan-2017 14:00:00         -15741           2076.3        4355.6        9084.7          0.97908               1246            9e-05        0.43023         16764             -1023.3   
    01-Jan-2017 15:00:00         -16477           2106.2          4841        9084.4          0.94576             1242.9          0.00044       0.047412         17276             -799.64   

If this answer helps you, please remember to accept the answer.

[Cris LaPierre]

That reminded me of this Answer: https://www.mathworks.com/matlabcentral/answers/2042557-error-while-reading-table#answer_1346387

[Per Westerlund]

Thank you! Now, I could import the file.