Extracting 2 columns from a excel file and send them to an own matrix file

4 次查看（过去 30 天）
Sergio
Sergio 2024-2-16
评论： Star Strider 2024-2-17
Hello, I have an XLS file that I need to extract two columns from, and send them in the Workspace to an own file.
I do on the xlsx file:
D=readtable('tempDataTrollhFlygpl.xlsx', 'NumHeaderLines', 10) % anger 10 rubrikrader slik at jeg kan ekstrahere kolonne entries mer effektivt
timetemp = D;({:,3}{:,4})
timetempdouble = str2double(D{:,3},{:,4});
In order to get the file called timetempdouble as an own file, with readable double-format. But this does not work. How can I extract those two columns, and what is the general command to say extract "n" columns from a xlsx file and convert it to doubles?
Thanks!

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

采纳的回答

Star Strider
Star Strider 2024-2-16
编辑：Star Strider 2024-2-16
Ran in:
Perhaps something like this —
opts = detectImportOptions('tempDataTrollhFlygpl.xlsx', 'NumHeaderLines', 9, 'VariableNamingRule','preserve');
opts = setvartype(opts, 4, 'double');
D=readtable('tempDataTrollhFlygpl.xlsx', opts) % anger 10 rubrikrader slik at jeg kan ekstrahere kolonne entries mer effektivt
D = 6786×7 table
Från Datum Tid (UTC) Till Datum Tid (UTC) Representativt dygn Lufttemperatur Kvalitet Var6 Tidsutsnitt: ____________________ ____________________ ___________________ ______________ ________ __________ _____________________________________________________________________________________________________________________________________________ 01-Jan-1961 00:00:01 02-Jan-1961 01-Jan-1961 0.1 {'Y'} {0×0 char} {'Kvalitetskontrollerade historiska data (utom de senaste 3 mån)' } 02-Jan-1961 00:00:01 03-Jan-1961 02-Jan-1961 0.7 {'Y'} {0×0 char} {'Tidsperiod (fr.o.m.) = 1961-01-01 00:00:00 (UTC)' } 03-Jan-1961 00:00:01 04-Jan-1961 03-Jan-1961 1 {'Y'} {0×0 char} {'Tidsperiod (t.o.m.) = 1979-08-01 23:59:59 (UTC)' } 04-Jan-1961 00:00:01 05-Jan-1961 04-Jan-1961 0.4 {'Y'} {0×0 char} {'Medelvärdet är beräknat för = 24 timmar' } 05-Jan-1961 00:00:01 06-Jan-1961 05-Jan-1961 0 {'Y'} {0×0 char} {0×0 char } 06-Jan-1961 00:00:01 07-Jan-1961 06-Jan-1961 0.2 {'Y'} {0×0 char} {'Kvalitetskoderna:' } 07-Jan-1961 00:00:01 08-Jan-1961 07-Jan-1961 0 {'Y'} {0×0 char} {'Grön (G) = Kontrollerade och godkända värden.' } 08-Jan-1961 00:00:01 09-Jan-1961 08-Jan-1961 -1.1 {'Y'} {0×0 char} {'Gul (Y) = Misstänkta eller aggregerade värden. Grovt kontrollerade arkivdata och okontrollerade realtidsdata (senaste 2 tim).' } 09-Jan-1961 00:00:01 10-Jan-1961 09-Jan-1961 -1.1 {'Y'} {0×0 char} {0×0 char } 10-Jan-1961 00:00:01 11-Jan-1961 10-Jan-1961 -0.8 {'Y'} {0×0 char} {'Orsaker till saknade data:' } 11-Jan-1961 00:00:01 12-Jan-1961 11-Jan-1961 -5.4 {'Y'} {0×0 char} {'stationen eller givaren har varit ur funktion.' } 12-Jan-1961 00:00:01 13-Jan-1961 12-Jan-1961 -6.8 {'Y'} {0×0 char} {'kvalitetskontrollerna har felmarkerat data (röd kvalitetskod). Efterföljande manuell granskning godkänner, rättar eller underkänner data.'} 13-Jan-1961 00:00:01 14-Jan-1961 13-Jan-1961 2.6 {'Y'} {0×0 char} {0×0 char } 14-Jan-1961 00:00:01 15-Jan-1961 14-Jan-1961 0.3 {'Y'} {0×0 char} {0×0 char } 15-Jan-1961 00:00:01 16-Jan-1961 15-Jan-1961 2.5 {'Y'} {0×0 char} {0×0 char } 16-Jan-1961 00:00:01 17-Jan-1961 16-Jan-1961 -2.7 {'Y'} {0×0 char} {0×0 char }
ismt6 = all(cellfun(@(x)isempty(x),D{:,6})); % Check 6 (Seems To Be Empty)
if ismt6
D = removevars(D, 6); % If 'Var6' Is Empty, Remove It (Optional)
end
D % Edited 'D'
D = 6786×6 table
Från Datum Tid (UTC) Till Datum Tid (UTC) Representativt dygn Lufttemperatur Kvalitet Tidsutsnitt: ____________________ ____________________ ___________________ ______________ ________ _____________________________________________________________________________________________________________________________________________ 01-Jan-1961 00:00:01 02-Jan-1961 01-Jan-1961 0.1 {'Y'} {'Kvalitetskontrollerade historiska data (utom de senaste 3 mån)' } 02-Jan-1961 00:00:01 03-Jan-1961 02-Jan-1961 0.7 {'Y'} {'Tidsperiod (fr.o.m.) = 1961-01-01 00:00:00 (UTC)' } 03-Jan-1961 00:00:01 04-Jan-1961 03-Jan-1961 1 {'Y'} {'Tidsperiod (t.o.m.) = 1979-08-01 23:59:59 (UTC)' } 04-Jan-1961 00:00:01 05-Jan-1961 04-Jan-1961 0.4 {'Y'} {'Medelvärdet är beräknat för = 24 timmar' } 05-Jan-1961 00:00:01 06-Jan-1961 05-Jan-1961 0 {'Y'} {0×0 char } 06-Jan-1961 00:00:01 07-Jan-1961 06-Jan-1961 0.2 {'Y'} {'Kvalitetskoderna:' } 07-Jan-1961 00:00:01 08-Jan-1961 07-Jan-1961 0 {'Y'} {'Grön (G) = Kontrollerade och godkända värden.' } 08-Jan-1961 00:00:01 09-Jan-1961 08-Jan-1961 -1.1 {'Y'} {'Gul (Y) = Misstänkta eller aggregerade värden. Grovt kontrollerade arkivdata och okontrollerade realtidsdata (senaste 2 tim).' } 09-Jan-1961 00:00:01 10-Jan-1961 09-Jan-1961 -1.1 {'Y'} {0×0 char } 10-Jan-1961 00:00:01 11-Jan-1961 10-Jan-1961 -0.8 {'Y'} {'Orsaker till saknade data:' } 11-Jan-1961 00:00:01 12-Jan-1961 11-Jan-1961 -5.4 {'Y'} {'stationen eller givaren har varit ur funktion.' } 12-Jan-1961 00:00:01 13-Jan-1961 12-Jan-1961 -6.8 {'Y'} {'kvalitetskontrollerna har felmarkerat data (röd kvalitetskod). Efterföljande manuell granskning godkänner, rättar eller underkänner data.'} 13-Jan-1961 00:00:01 14-Jan-1961 13-Jan-1961 2.6 {'Y'} {0×0 char } 14-Jan-1961 00:00:01 15-Jan-1961 14-Jan-1961 0.3 {'Y'} {0×0 char } 15-Jan-1961 00:00:01 16-Jan-1961 15-Jan-1961 2.5 {'Y'} {0×0 char } 16-Jan-1961 00:00:01 17-Jan-1961 16-Jan-1961 -2.7 {'Y'} {0×0 char }
timetemp = D(:,[3 4]) % Create 'timetemp' From Variables 3 & 4
timetemp = 6786×2 table
Representativt dygn Lufttemperatur ___________________ ______________ 01-Jan-1961 0.1 02-Jan-1961 0.7 03-Jan-1961 1 04-Jan-1961 0.4 05-Jan-1961 0 06-Jan-1961 0.2 07-Jan-1961 0 08-Jan-1961 -1.1 09-Jan-1961 -1.1 10-Jan-1961 -0.8 11-Jan-1961 -5.4 12-Jan-1961 -6.8 13-Jan-1961 2.6 14-Jan-1961 0.3 15-Jan-1961 2.5 16-Jan-1961 -2.7
EDIT — (16 Feb 2024 at 19:13)
I forgot about saving it to a file. Use writetable for that.
.
  4 个评论
显示 2更早的评论
Sergio
Sergio 2024-2-17
Thanks Starstrider, the last part solved the problem,
timetemp = D(:,[3 4])
gave the file in the Workspace..called timetemp

请先登录，再进行评论。

更多回答（1 个）

Askic V
Askic V 2024-2-16
编辑：Askic V 2024-2-16
Hello,
I would do something like this to extract the data, and then I would used writematrix like VBBV suggested to write into new file.
A = readtable('tempDataTrollhFlygpl.xlsx');
Values = str2double(A{4:end, 4});
Dates = datetime(A{4:end, 3}, 'InputFormat','dd-MMM-yyyy');
plot(Dates, Values)

类别

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

标签

产品


版本

R2023b

Community Treasure Hunt

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

Start Hunting!

Translated by