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

조회 수: 5 (최근 30일)
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일
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
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 CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

제품


릴리스

R2023b

Community Treasure Hunt

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

Start Hunting!

Translated by