Import, sort and extract multiple excel files into 1 table/vector

2018 8 19
1 个回答
回答已采纳
更新时间：2018 8 21
20 次查看（30 天）

0 个投票

Hi I have 12 .csv files (1 for each month) with a fair amount of data in each, although they are presented the same way. I want to bring in all 12 files into MATLAB, and store the same 3 columns of data (columnns 2-4, rows 6-36(or end)) from each file sequentially in 3 vectors date, min, max. I have been researching and struggling all morning, and the below code is the best I have
% tell MATLAB to look for all .csv files in currently directory
files15 = dir('*.csv');
% Loop the same amount of times as number of files
for i = 1:numel(files15)
% store the file name in a column cell
x(:,i) = double(files15(i).name)
% Set a placeholder inside the file name to remove string
fNum = find((x),'TEST')
% Extract the number from the file name
out(:,i)=(fNum(fNum+5:end-4))
% sort files numerically 1-12
neworder = sort(out)
% import numbers and text as separate components for each file
[import15,import15t] = xlsread (files15);
% Store all dates in one column
d6 = import5t(7:end,2);
% Store all min and max temperatures in two columns
Tmin6 = import5(1:end,1);
Tmax6 = import5(1:end,2);
end
I think I am making it more complicated than it needs to be, any suggestions on the simplest way to achieve this?
Thanks in advance Anthony

4 个评论
显示 2更早的评论 隐藏 2更早的评论

Image Analyst
Image Analyst 2018-8-19
Can you attach 2 or 3 of the files so people can try to help you?
Anthony Cook
Anthony Cook 2018-8-20
编辑：Anthony Cook 2018-8-20
Sorry, please see attached. It is for an assignment so I don't want a copy paste code, just some pointers in the right direction or which functions to use/try. Thanks in advance
dpb
dpb 2018-8-20
You'd have to modify the sscanf line a little in my posting; didn't mention about the YYYY- string before. Easy enough to do use
fmt='TEST %d-%d.csv';
and can return both values.
As a way to avoid all this grief about order, any time you create files sequentially such as this, use a format when creating the file names that includes the preceding '0's in the number fields so that the files are sorted numerically as well as lexically automagically --
fn=sprintf('TEST %d-%02d.csv',year,mo);
will produce
>> fn=sprintf('TEST %d-%02d.csv',2017,8)
fn =
'TEST 2017-08.csv'
>>
etc., and then dir will return a list of files sorted in the order desired already.
Anthony Cook
Anthony Cook 2018-8-20
Thank you for your help. I agree with just renaming the files to be in order, but not allowed in this case :) Thanks again

请先登录，再进行评论。

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

 采纳的回答

dpb
dpb 2018-8-20

0 个投票

Good start but have made it a little more complicated than need...to get the files in numerical instead of lexical order
d=dir('*.csv');
[~,ix]=sort(cellfun(@(s) sscanf(s,'TEST%d.csv'),cellstr({d.name})));
d=d(ix);
Now, as IA suggests, it would be good to know what the format of the files actually is; I'm guessing there are 6 header lines from the indexing expression for the dates so something like
dates=[];
data=[];
for i=1:length(d)
% import numbers and text as separate components for each file
[v,t] = xlsread(d(i).name);
dates=[dates;t(7:end,2)];
data=[data;v];
end
Probably this would be a good application for a table and readtable, but need the details of the data structure.

5 个评论
显示 3更早的评论 隐藏 3更早的评论

Anthony Cook
Anthony Cook 2018-8-20
Thank you, I have managed to get all 365 days of information into 3 variables. For sorting the data, I can see what your first code is doing but I still can't seem to sort the data. There is 12 files from July to July. So MATLAB reads October first (10) over August (8). e.g.
TEST 2017-8
TEST 2017-9
TEST 2017-10
I have also tried using regexp but that doesn't seem to be working either
[~, index] = sort( str2double( regexp( {d.name}, '\d+', 'match', 'once' )))
d = d(index) ;
I see there is a FEX submission 'natsortfiles' but I would like to solve using in house MATLAB functions.
dpb
dpb 2018-8-20
编辑：dpb 2018-8-20
See the comment above -- with both the year/month in the file name you've got to do some more work...the above format doesn't match the filename string. If the year were always constant you could just encode it into the format string and the rest would still work--assume you're looking for a known year to process, then enter the year as input at the beginning and then
infmt=num2str(yr,'TEST %d-*.csv'); % wild card string for given year
rdfmt=num2str(yr,'TEST %d-%%d.csv'); % fmt for given year read month
d=dir(infmt);
[~,ix]=sort(cellfun(@(s) sscanf(s,rdfmt),cellstr({d.name})));
d=d(ix);
If you were to want to do multiple years, then it gets a little more complicated in that you have to return both year and month and sort both.
I'm sure regexp can do it; I'm just not practiced enough in writing the parsing expressions to even give it a go; just not an area I've made any attempt to learn the syntax of, sorry. :)
dpb
dpb 2018-8-21
ADDENDUM The line
d=d(idx);
does the sort for you by rearranging the dir() structure in numeric order of the files. Thus, when you iterate through the reordered structure, you'll get them in order.
Why it didn't work before is that I thought the filenames were of the form 'TEST-NN.csv' not 'TEST YYYY-NN.csv' so the format used to read the month number was wrong and thus the sort would fail as would get empty vector for n. Unfortunately, that's a silent failure so no error was generated as I didn't include any error handling code to test for the case.
dpb
dpb 2018-8-21
Glad to help...just happened to notice the comment and dawned on me you may have not understood the underlying cause of the failure.

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

产品

标签

Community Treasure Hunt

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

Start Hunting!

Translated by