subdivide numbers inside a file .xlsx

Question

Alberto Acri 2024-8-20，18:04

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2146719-subdivide-numbers-inside-a-file-xlsx

回答： Walter Roberson 2024-8-20，18:47

采纳的回答： Binaya

file.xlsx

在 MATLAB Online 中打开

How can I divide the numbers into different columns in this attached .xlsx file?

filename = 'file.xlsx';
t = readtable(filename);
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(1:6,:)
ans = 6x1 table
                                          x_Name_                                       
    ____________________________________________________________________________________

    {'File 1'                                                                          }
    {0x0 char                                                                          }
    {'[Data]'                                                                          }
    {'X [ m ], Y [ m ], Z [ m ], Number on File 1, Velocity [ m s^-1 ]'                }
    {'-3.55882719e-02, -1.09321419e-02, 8.20557680e-03, 0.00000000e+00, 1.00000292e-01'}
    {'-3.59198786e-02, -1.09023182e-02, 8.16907175e-03, 1.00000000e+00, 1.00000285e-01'}

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Binaya 2024-8-20，18:27

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2146719-subdivide-numbers-inside-a-file-xlsx#answer_1501969

编辑：Binaya 2024-8-20，18:31

在 MATLAB Online 中打开

file.xlsx

Hi Alberto

You can follow the below steps to divide the numbers into different columns:

Read the data using "readcell" function.

data = readcell('file.xlsx');

2. Select the range of data where you have the numbers present.

data = data(6:13)
data = 8x1 cell array
    {'-3.55882719e-02, -1.09321419e-02, 8.20557680e-03, 0.00000000e+00, 1.00000292e-01'}
    {'-3.59198786e-02, -1.09023182e-02, 8.16907175e-03, 1.00000000e+00, 1.00000285e-01'}
    {'-3.57956178e-02, -1.06404135e-02, 8.25155806e-03, 2.00000000e+00, 1.00000285e-01'}
    {'-3.54897231e-02, -1.05726253e-02, 8.30925070e-03, 3.00000000e+00, 1.00000285e-01'}
    {'-3.52230370e-02, -1.06995245e-02, 8.31269473e-03, 4.00000000e+00, 1.00000285e-01'}
    {'-3.53128649e-02, -1.10662561e-02, 8.20836797e-03, 5.00000000e+00, 1.00000277e-01'}
    {'-3.56706455e-02, -1.12167289e-02, 8.12293869e-03, 6.00000000e+00, 1.00000277e-01'}
    {'-3.59513536e-02, -1.11927493e-02, 8.09170678e-03, 7.00000000e+00, 1.00000285e-01'}

3. Split the string in each row at the comma and convert the strings to numbers. Iterate this over all the rows and store each row in an array.

numRows = length(data);
for i = 1:numRows    
    newData(i, :) = str2double(strsplit(data{i}, ','));
end
newData
newData = 8x5
   -0.0356   -0.0109    0.0082         0    0.1000
   -0.0359   -0.0109    0.0082    1.0000    0.1000
   -0.0358   -0.0106    0.0083    2.0000    0.1000
   -0.0355   -0.0106    0.0083    3.0000    0.1000
   -0.0352   -0.0107    0.0083    4.0000    0.1000
   -0.0353   -0.0111    0.0082    5.0000    0.1000
   -0.0357   -0.0112    0.0081    6.0000    0.1000
   -0.0360   -0.0112    0.0081    7.0000    0.1000
<mw-icon class=""></mw-icon>
<mw-icon class=""></mw-icon>

4. You can now use the newData in your code or save it in a new XLSX file using the "writetable" function.

table = array2table(newData)
table = 8x5 table
    newData1     newData2     newData3     newData4    newData5
    _________    _________    _________    ________    ________

    -0.035588    -0.010932    0.0082056       0          0.1   
     -0.03592    -0.010902    0.0081691       1          0.1   
    -0.035796     -0.01064    0.0082516       2          0.1   
     -0.03549    -0.010573    0.0083093       3          0.1   
    -0.035223      -0.0107    0.0083127       4          0.1   
    -0.035313    -0.011066    0.0082084       5          0.1   
    -0.035671    -0.011217    0.0081229       6          0.1   
    -0.035951    -0.011193    0.0080917       7          0.1   
writetable(table,'newFile.xlsx')

Here are the documentation links to the functions used above:

readcell: https://www.mathworks.com/help/matlab/ref/readcell.html
strsplit: https://www.mathworks.com/help/matlab/ref/strsplit.html
writetable: https://www.mathworks.com/help/matlab/ref/writetable.html

I hope this answers your query.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 2

Stephen23 2024-8-20，18:39

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2146719-subdivide-numbers-inside-a-file-xlsx#answer_1501984

编辑：Stephen23 2024-8-20，18:40

在 MATLAB Online 中打开

file.xlsx

Simpler:

C = readcell('file.xlsx');
M = str2double(split(C(6:end),', '))
M = 8x5
   -0.0356   -0.0109    0.0082         0    0.1000
   -0.0359   -0.0109    0.0082    1.0000    0.1000
   -0.0358   -0.0106    0.0083    2.0000    0.1000
   -0.0355   -0.0106    0.0083    3.0000    0.1000
   -0.0352   -0.0107    0.0083    4.0000    0.1000
   -0.0353   -0.0111    0.0082    5.0000    0.1000
   -0.0357   -0.0112    0.0081    6.0000    0.1000
   -0.0360   -0.0112    0.0081    7.0000    0.1000
<mw-icon class=""></mw-icon>
<mw-icon class=""></mw-icon>
T = array2table(M, 'VariableNames',split(C(5),', '))
T = 8x5 table
     X [ m ]      Y [ m ]      Z [ m ]     Number on File 1    Velocity [ m s^-1 ]
    _________    _________    _________    ________________    ___________________

    -0.035588    -0.010932    0.0082056           0                    0.1        
     -0.03592    -0.010902    0.0081691           1                    0.1        
    -0.035796     -0.01064    0.0082516           2                    0.1        
     -0.03549    -0.010573    0.0083093           3                    0.1        
    -0.035223      -0.0107    0.0083127           4                    0.1        
    -0.035313    -0.011066    0.0082084           5                    0.1        
    -0.035671    -0.011217    0.0081229           6                    0.1        
    -0.035951    -0.011193    0.0080917           7                    0.1        

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 3

Walter Roberson 2024-8-20，18:47

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2146719-subdivide-numbers-inside-a-file-xlsx#answer_1501989

在 MATLAB Online 中打开

file.xlsx

Your data is stored in single cells of the xlsx. For example

-3.55882719e-02, -1.09321419e-02, 8.20557680e-03, 0.00000000e+00, 1.00000292e-01

is stored all as a single cell, not as seperate cells.

format long g
filename = 'file.xlsx';
temp = readcell(filename, 'headerlines', 5);
t = cell2mat(cellfun(@str2num, temp, 'uniform', 0))
t = 8x5
             -0.0355882719             -0.0109321419              0.0082055768                         0               0.100000292
             -0.0359198786             -0.0109023182             0.00816907175                         1               0.100000285
             -0.0357956178             -0.0106404135             0.00825155806                         2               0.100000285
             -0.0354897231             -0.0105726253              0.0083092507                         3               0.100000285
              -0.035223037             -0.0106995245             0.00831269473                         4               0.100000285
             -0.0353128649             -0.0110662561             0.00820836797                         5               0.100000277
             -0.0356706455             -0.0112167289             0.00812293869                         6               0.100000277
             -0.0359513536             -0.0111927493             0.00809170678                         7               0.100000285
<mw-icon class=""></mw-icon>
<mw-icon class=""></mw-icon>