readtimetable does not read the entire Excel file

17 Nov 2023
1 Answer
Answer Accepted
Updated 17 Nov 2023
22 Views (30 days)

0 votes

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?

7 Comments
Show 5 older comments Hide 5 older comments

Mathieu NOE
Mathieu NOE on 17 Nov 2023
hi
can you share an extract of the excel files that cause the issue ?
Per Westerlund
Per Westerlund on 17 Nov 2023
Yes, in principle, since I downloaded them. They are quite large, so I will make an extract.
Cris LaPierre
Cris LaPierre on 17 Nov 2023
Edited: Cris LaPierre on 17 Nov 2023
Ran in:
Ran in:
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
Per Westerlund on 17 Nov 2023
Edited: Cris LaPierre on 17 Nov 2023
Ran in:
Ran in:
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
Cris LaPierre on 17 Nov 2023
I don't see any issues importing this file either. Do you?
Per Westerlund
Per Westerlund on 17 Nov 2023
The file has 2992 rows of data and I get only 2881 rows when I import it.

Sign in to comment.

Sign in to answer this question.

 Accepted Answer

Drew
Drew on 17 Nov 2023
Edited: Drew on 17 Nov 2023
Ran in:

0 votes

Ran in:
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.

More Answers (0)

Categories

Find more on Spreadsheets in Help Center and File Exchange

Products

Release

R2022a

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!