Clear Filters
Clear Filters

Error while reading table

8 views (last 30 days)
Indrani
Indrani on 3 Nov 2023
Commented: Indrani on 4 Nov 2023
Hello,
I am trying to read the data of the attached excel file. However, when I use readtable, only values upto row 2881 are loaded in a table in MATLAB. Can you tell me how to resolve this issue?
Thanks!
  2 Comments
Voss
Voss on 3 Nov 2023
Moved: Voss on 3 Nov 2023
Ran in:
I can't open that file in Excel to see how many rows it has, but readcell reads 2886 rows, the first 5 or 6 of which look like the header.
C = readcell('n_fot2017-01-12.xls')
C = 2886×12 cell array
Columns 1 through 7 {'Report template (Ra…'} {[01-Jan-2017 00:00:00]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {'Report template (Ra…'} {[01-May-2017 00:00:00]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {'Report template (Pr…'} {[22-Sep-2017 19:47:43]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {'Time series product…'} {'Kärnkraft prod. per…'} {'Gasturbin/diesel…'} {'Vindkraft prod. …'} {'Netto Exp/imp. p…'} {'Ospec. prod. per…'} {'Total prod. per …'} {'Numeric time series…'} {'M273_x_SWE_x_x_x_1' } {'M275_x_SWE_x_x_x…'} {'M272_x_SWE_x_x_x…'} {'M645_x_SWE_x_x_x…'} {'M270_x_SWE_x_x_x…'} {'M500_x_SWE_x_x_x…'} {'Numeric time series…'} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[01-Jan-2017 00:00:00]} {[ 9.0744e+03]} {[ 0.8574]} {[ 3.6378e+03]} {[ -2.9196e+03]} {[ 0.9204]} {[ 1.6256e+04]} {[01-Jan-2017 01:00:00]} {[ 9.0747e+03]} {[ 0.8508]} {[ 3.4295e+03]} {[ -2.9203e+03]} {[ 1.1083]} {[ 1.5964e+04]} {[01-Jan-2017 02:00:00]} {[ 9.0749e+03]} {[ 0.8447]} {[ 3.2900e+03]} {[ -2.9965e+03]} {[ 1.0244]} {[ 1.5817e+04]} {[01-Jan-2017 03:00:00]} {[ 9.0774e+03]} {[ 0.8266]} {[ 3.0532e+03]} {[ -2.8781e+03]} {[ 0.9999]} {[ 1.5543e+04]} {[01-Jan-2017 04:00:00]} {[ 9.0780e+03]} {[ 0.8007]} {[ 2.9322e+03]} {[ -2.8557e+03]} {[ 0.8734]} {[ 1.5481e+04]} {[01-Jan-2017 05:00:00]} {[ 9.0799e+03]} {[ 0.6707]} {[ 2.7872e+03]} {[ -2.6600e+03]} {[ 0.6983]} {[ 1.5365e+04]} {[01-Jan-2017 06:00:00]} {[ 9.0826e+03]} {[ 0.6390]} {[ 2.6617e+03]} {[ -2.5739e+03]} {[ 0.0041]} {[ 1.5496e+04]} {[01-Jan-2017 07:00:00]} {[ 9.0827e+03]} {[ 0.5710]} {[ 2.6110e+03]} {[ -2.3826e+03]} {[ 0.6768]} {[ 1.5607e+04]} {[01-Jan-2017 08:00:00]} {[ 9.0827e+03]} {[ 0.5523]} {[ 2.5535e+03]} {[ -2.0771e+03]} {[ 0.6103]} {[ 1.5663e+04]} {[01-Jan-2017 09:00:00]} {[ 9.0847e+03]} {[ 0.6233]} {[ 2.3920e+03]} {[ -2.0789e+03]} {[ 0.1009]} {[ 1.5974e+04]} Columns 8 through 12 {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {'Solkraft prod. p…'} {'Vågkraft prod. p…'} {'Summa förbr. per…'} {'Övr.värmekraft p…'} {'Vattenkraft prod…'} {'M301_x_SWE_x_x_x…'} {'M302_x_SWE_x_x_x…'} {'M501_x_SWE_x_x_x…'} {'M274_x_SWE_x_x_x…'} {'M271_x_SWE_x_x_x…'} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[ 0.0359]} {[ 0]} {[ -1.3339e+04]} {[ 1.1257e+03]} {[ 2.4158e+03]} {[ 0.0353]} {[ 0]} {[ -1.3047e+04]} {[ 1.0765e+03]} {[ 2.3812e+03]} {[ 0.0262]} {[ 0]} {[ -1.2824e+04]} {[ 1.0955e+03]} {[ 2.3551e+03]} {[ 0.0196]} {[ 0]} {[ -1.2668e+04]} {[ 1.1423e+03]} {[ 2.2680e+03]} {[ 0.0181]} {[ 0]} {[ -1.2629e+04]} {[ 1.1585e+03]} {[ 2.3110e+03]} {[ 0.0174]} {[ 0]} {[ -1.2708e+04]} {[ 1.1691e+03]} {[ 2.3275e+03]} {[ 0.0191]} {[ 0]} {[ -1.2924e+04]} {[ 1.1955e+03]} {[ 2.5557e+03]} {[ 0.0235]} {[ 0]} {[ -1.3227e+04]} {[ 1.1842e+03]} {[ 2.7278e+03]} {[ 0.0258]} {[ 0]} {[ -1.3588e+04]} {[ 1.1659e+03]} {[ 2.8600e+03]} {[ 0.1066]} {[ 0]} {[ -1.3897e+04]} {[ 1.1959e+03]} {[ 3.3006e+03]}
Evidently, readtable is interpreting 5 header rows and putting the remaining rows into a table with 2886-5 = 2881 rows.
T = readtable('n_fot2017-01-12.xls')
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
T = 2881×12 table
TimeSeriesProductInstance_Beskrivning_ K_rnkraftProd_PerCO__SWE___ Gasturbin_dieselProd_PerCO__SWE___ VindkraftProd_PerCO__SWE___ NettoExp_imp_PerCO__SWE___ Ospec_Prod_PerCO__SWE___ TotalProd_PerCO__SWE___ SolkraftProd_PerCO__SWE___ V_gkraftProd_PerCO__SWE___ SummaF_rbr_PerCO__SWE___ x_vr_v_rmekraftProd_PerCO__SWE___ VattenkraftProd_PerCO__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
If you want to tell readtable that there are 6 header rows, you can do that, and then you'll get a table with 2886-6 = 2880 rows.
T = readtable('n_fot2017-01-12.xls','NumHeaderLines',6)
T = 2880×12 table
Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 Var9 Var10 Var11 Var12 ____________________ ______ _______ ______ _______ _______ _____ ________ ____ ______ ______ ______ 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 01-Jan-2017 15:00:00 9084.4 0.94576 2106.2 -799.64 0.00044 17276 0.047412 0 -16477 1242.9 4841
Indrani
Indrani on 3 Nov 2023
Moved: Voss on 3 Nov 2023
Can you please check the attached screenshot? Data from row 2882 is not being read

Sign in to comment.

Sign in to answer this question.

Accepted Answer

Cris LaPierre
Cris LaPierre on 3 Nov 2023
Edited: Cris LaPierre on 3 Nov 2023
Ran in:
I noticed that the variable names in the imported table are not matching the names I see in the file. Upon further investigation, I discovered that is because you have a hidden sheet named Blad1 in your spreadsheet. It is reading that instead of the sheet you can see when you open the file. .
Specify the sheetname to load the data you are seeing.
T = readtable('n_fot2017-01-12.xls',"Sheet","Förb + prod i Sverige",'VariableNamingRule','preserve')
T = 8760×11 table
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

More Answers (1)

Sulaymon Eshkabilov
Sulaymon Eshkabilov on 3 Nov 2023
Ran in:
So far, the orignal file has some VB issues. Therefore, none of the suggest solutions can read the whole data even if the imported data size is correct. The solution is to save the file *.xls as *.csv (UTF-8) and then import the data.
D1 = readtable('n_fot2017-01-12.csv', 'Range', 'A2:K8761');
size(D1)
ans = 1×2
8760 11
D1(end,:) % Whole data is read correctly. It does not stop at row 2886
ans = 1×11 table
Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 Var9 Var10 Var11 ____________________ ______ ______ ______ ______ ____ ____ _____ ____ _____ _______ 31-Dec-2017 23:00:00 -15473 1909.2 6542.8 8534.5 NaN 1113 24.92 0.06 18125 -2493.6
% Compare
D2 = readtable('n_fot2017-01-12.xls', 'Range', 'A2:K8761');
size(D2) % Size is correct
ans = 1×2
8760 11
D2(end,:) % BUT Not correctly read
ans = 1×11 table
Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 Var9 Var10 Var11 ____ ____ ____ ____ ____ ____ ____ ____ ____ _____ _____ NaT NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
C = readcell('n_fot2017-01-12.xls', 'Range', 'A2:K8761');
size(C) % Size is correct
ans = 1×2
8760 11
C(end,1) % BUT Not correctly read
ans = 1×1 cell array
{[<missing>]}
  1 Comment
Sulaymon Eshkabilov
Sulaymon Eshkabilov on 3 Nov 2023
Ran in:
An alternative way to import the whole data is as Chris mentioned (because of the hidden sheet = Blad1), by specifying the sheet name, e.g.:
D1 = readtable('n_fot2017-01-12.xls', 'Sheet', 'Förb + prod i Sverige');
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
size(D1)
ans = 1×2
8760 11
D1(end,:)
ans = 1×11 table
Tid TotalF_rbrukning Vindkraft Vattenkraft K_rnkraft Gasturbin_diesel x_vr_v_rmekraft Ospec_Prod_ Solkraft TotalProduktion Import_export ____________________ ________________ _________ ___________ _________ ________________ _______________ ___________ ________ _______________ _____________ 31-Dec-2017 23:00:00 -15473 1909.2 6542.8 8534.5 NaN 1113 24.925 0.059666 18125 -2493.6
% OR Note dates are not read in the right format
D2 = readmatrix('n_fot2017-01-12.xls', 'Range', 'A2:K8761', 'Sheet', 2);
size(D2)
ans = 1×2
8760 11
D2(end,:)
ans = 1×11
1.0e+04 * 4.3101 -1.5473 0.1909 0.6543 0.8535 NaN 0.1113 0.0025 0.0000 1.8125 -0.2494

Sign in to comment.

Categories

Find more on Tables in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!