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

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!

[VBBV]

Use writematrix function with the desired filename and extension

https://in.mathworks.com/help/matlab/ref/writematrix.html

Answer 1

[Star Strider]

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.

.

[Askic V]

@Star Strider How did you manage to read from the file and execute code without using load and websave? Is there any other way we can read from the attached files here on the discussion threads?

Sorry for being off topic.

[Star Strider]

I used the ‘paperclip’ icon on the top toolbar and then checked the appropriate box (only one here, clicking on the top one next to ‘Attachment name’ checks all of them) in the ‘Link from this thread’ tab, then clicked on ‘Submit’. The files then attach automatically to the Comment or Answer window that originates them.

The paperclip icon is also used to upload files from the user’s computer, as it was previously.

[Sergio]

Thanks Starstrider, the last part solved the problem,

timetemp = D(:,[3 4])      

gave the file in the Workspace..called timetemp

[Star Strider]

As always, my pleasure!

Answer 2

[Askic V]

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)