How can I extract and then write data in certain order from other excel files to excel file?

1 回表示 (過去 30 日間)
My excel workbook inserts seven worksheets. A,B and C columns in every worksheets are same. Next five columns are respectively values of longitude 25, 30, 35, 40 and 45 related to sheets(Lat30, Lat32.5 and so on). My data is normally 99888x8 table. Small data example orders as Data.xlsx file and I would like to do as Test.xlsx file.
A column: Year (from 1998 to 2017)
B column: Day of the year (from 1 to 365 or 366)
C column: Hour
D,E,F,G & H columns: TEC values
I'd like to write like test.xlsx file for every hour but I have no idea how I write like Test.xlsx file for every hour?

採用された回答

Guillaume
Guillaume 2019 年 3 月 15 日
編集済み: Guillaume 2019 年 3 月 15 日
[~, sheets] = xlsfinfo('Data.xlsx'); %get list of sheets
lattables = cell(size(sheets)); %preallocate cell array of table
for sh = 1:numel(sheets)
latitude = sscanf(strrep(sheets{sh}, '_', '.'), 'Lat%f'); %extract latitude from sheet name (replace _ by . so that Lat is followed by an actual number
lattables{sh} = readtable('Data.xlsx', 'Sheet', sheets{sh}); %read sheet
lattables{sh}.Latitude = repmat(latitude, height(lattables{sh}), 1); %append latitude column (all identical for the current table)
end
fulltable = vertcat(lattables{:}); %once all are loaded, concatenate into a single table
fulltable = stack(fulltable, 4:8, 'ConstantVariables', [1:3, 9], 'NewDataVariableName', 'TEC', 'IndexVariableName', 'Longitude');
fulltable.Longitude = sscanf(strrep(strjoin(cellstr(fulltable.Longitude)), '_', '.'), 'Long%f ')

その他の回答 (1 件)

Suat YAZICI
Suat YAZICI 2019 年 3 月 16 日
Code is working very well but I didn't export or copy result. I got error as follow:
Out of Memory.PNG
I determined Java Heap Size as 2,024 MB by increasing but have still problem.
>> whos fulltable
Name Size Bytes Class Attributes
fulltable 3496080x6 167814665 table
>> memory
Maximum possible array: 23341 MB (2.447e+10 bytes) *
Memory available for all arrays: 23341 MB (2.447e+10 bytes) *
Memory used by MATLAB: 1456 MB (1.527e+09 bytes)
Physical Memory (RAM): 8096 MB (8.489e+09 bytes)
* Limited by System Memory (physical + swap file) available.
  1 件のコメント
Guillaume
Guillaume 2019 年 3 月 17 日
With more than 3 million rows, you wouldn't be able to copy ir into excel as Excel is limited to 1,048,576 rows.
You could write it into a text file, it may take a while:
writetable(fulltable, 'C:\somewhere\somefile.txt');
Otherwise you need to reduce the size of the table. Maybe split it into yearly tables (this can be done easily with splitapply).
Note that if your tables are less than a million row, you can export them into an excel file without copy/pasting, using writetable.

サインインしてコメントする。

カテゴリ

Help Center および File ExchangeSpreadsheets についてさらに検索

Community Treasure Hunt

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

Start Hunting!

Translated by