subdivide numbers inside a file .xlsx

1 次查看（过去 30 天）
Alberto Acri
Alberto Acri 2024-8-20
回答： Walter Roberson 2024-8-20
Ran in:
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'}

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

采纳的回答

Binaya
Binaya 2024-8-20
编辑：Binaya 2024-8-20
Ran in:
Hi Alberto
You can follow the below steps to divide the numbers into different columns:
  1. 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:
  1. readcell: https://www.mathworks.com/help/matlab/ref/readcell.html
  2. strsplit: https://www.mathworks.com/help/matlab/ref/strsplit.html
  3. writetable: https://www.mathworks.com/help/matlab/ref/writetable.html
I hope this answers your query.

更多回答（2 个）

Stephen23
Stephen23 2024-8-20
编辑：Stephen23 2024-8-20
Ran in:
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
Walter Roberson
Walter Roberson 2024-8-20
Ran in:
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>

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2021b

Community Treasure Hunt

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

Start Hunting!

Translated by