I have an Excel sheet with headers of different depths and the numbers are not being read (ex.: d=-12.82m is being read as x_12_82M),

Question

janas 2023-11-20

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2049522-i-have-an-excel-sheet-with-headers-of-different-depths-and-the-numbers-are-not-being-read-ex-d

评论： janas 2023-11-20

Sample.xlsx

I am trying to convert from Excel to a different format, for different depths but the headers are not being read properly.

I even tried changing it manually in Excel but if I do, they will be read as part of the data and not as headers

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Stephen23 2023-11-20

Please upload a sample spreadsheet by clicking the paperclip button.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Chunru 2023-11-20

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2049522-i-have-an-excel-sheet-with-headers-of-different-depths-and-the-numbers-are-not-being-read-ex-d#answer_1356317

在 MATLAB Online 中打开

% The first row is treated as var names.  Use preserve to keep the original
% format
a = readtable("https://www.mathworks.com/matlabcentral/answers/uploaded_files/1544212/Sample.xlsx", "VariableNamingRule", "preserve")
a = 4×41 table
           Datum            -12.82 m    -12.47 m    -12.12 m    -11.77 m    -11.42 m    -11.07 m    -10.72 m    -10.37 m    -10.02 m    -9.67 m    -9.32 m    -8.97 m    -8.62 m    -8.27 m    -7.92 m    -7.57 m    -7.22 m    -6.87 m    -6.52 m    -6.17 m    -5.82 m    -5.47 m    -5.12 m    -4.77 m    -4.42 m    -4.07 m    -3.72 m    -3.37 m    -3.02 m    -2.67 m    -2.32 m    -1.97 m    -1.63 m    -1.28 m    -0.93 m    -0.58 m    -0.23 m    0.12 m    0.47 m    0.82 m
    ____________________    ________    ________    ________    ________    ________    ________    ________    ________    ________    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    ______    ______    ______

    27-Jun-2019 12:00:00      0.62        0.64        0.65        0.66        0.66        0.72        0.74        0.76        0.73       0.75       0.77       0.78       0.79       0.86       0.83       0.82       0.88       0.87       0.91       0.91       0.96       0.99       0.99       0.98       0.99          1       1.01       1.08       1.05       0.87       0.55        NaN        NaN        NaN        NaN        NaN        NaN       NaN       NaN       NaN  
    27-Jun-2019 12:01:00      0.55        0.62        0.67        0.68        0.66        0.71        0.72        0.75        0.75       0.79       0.82       0.82       0.82       0.81       0.81       0.84       0.86        0.9       0.91       0.92       0.91       0.93       0.95       0.98       0.99       0.99          1       0.98          1       0.98       0.86        NaN        NaN        NaN        NaN        NaN        NaN       NaN       NaN       NaN  
    27-Jun-2019 12:02:00      0.62        0.62        0.65        0.66        0.67        0.73        0.72        0.76        0.75       0.76       0.77       0.79       0.78       0.79        0.8       0.85       0.89       0.87       0.91       0.91       0.93       0.95       0.97          1       1.01       1.05       1.06       1.06       1.04       1.03       0.89        NaN        NaN        NaN        NaN        NaN        NaN       NaN       NaN       NaN  
    27-Jun-2019 12:03:00      0.67        0.71        0.76        0.78         0.8        0.79        0.78         0.8        0.75       0.76       0.75       0.75        0.8       0.82       0.82       0.84       0.88       0.89       0.89       0.93       0.97       0.99       1.02       1.02       1.02       1.05       1.03       1.07       1.02       0.92       0.93        NaN        NaN        NaN        NaN        NaN        NaN       NaN       NaN       NaN  
s = a.Properties.VariableNames;  % get the variable names
whos
  Name        Size            Bytes  Class    Attributes

  a           4x41            11189  table              
  cmdout      1x33               66  char               
  s           1x41             4846  cell               
d = cellfun(@(x) sscanf(x, "%f"), s(2:end) )  % convert to depth
d = 1×40
  -12.8200  -12.4700  -12.1200  -11.7700  -11.4200  -11.0700  -10.7200  -10.3700  -10.0200   -9.6700   -9.3200   -8.9700   -8.6200   -8.2700   -7.9200   -7.5700   -7.2200   -6.8700   -6.5200   -6.1700   -5.8200   -5.4700   -5.1200   -4.7700   -4.4200   -4.0700   -3.7200   -3.3700   -3.0200   -2.6700