Adding filename inside table excel and merge

8 views (last 30 days)
I don't know it is possible, but I don't remember.
I have several excel (.xslx) almost 3000 file. I would like to insert filename inside table adding one column.
I put one example, these are file excel:
'filename1.xlsx'
'filename2.xlsx'
'filename3.xlsx'
'filename4.xlsx'
'filename5.xlsx'
'filename6.xlsx'
'.....xlsx'
Each excel file has two variables (data and rainfall). for example 'filename1.xlsx':
filename1.data
filename1.rainfall
I would like to put another variable (column) with the filename, in this case is filename1
filename1.data
filename1.rainfall
filename1.filename1
help me!
Maybe using "for"? I don't know
And then it is possible to merge all excel file creating one master excel file?
  1 Comment
Sudharsana Iyengar
Sudharsana Iyengar on 5 Nov 2021
See your question is not clear to me. check this.
%-------------------------------------------------------------------------------------------------
myFolder = 'destination file'; % Define your working folder (have all your xlsx files here)
%-------------------------------------------------------------------------------------------------
if ~isdir(myFolder)
errorMessage = sprintf('Error: The following folder does not exist:\n%s', myFolder);
uiwait(warndlg(errorMessage));
return;
end
%this is if you input a wrong folder
%--------------------------------------------------------------------------------------------------
filePattern = fullfile(myFolder, '*.xlsx');
xlsxFiles = dir(filePattern);
%this will detect all xlsx files in your folder
%--------------------------------------------------------------------------------------------------
for k = 1:length(xlsxFiles)
baseFileName = xlsxFiles(k).name;
fullFileName = fullfile(myFolder, baseFileName);
%baseFileName is the name of your file, which we will use for extracting
%data.
fprintf(1, 'Now reading %s\n', fullFileName);
%this will say which file its reading
xlsxData{k} =readtable(fullFileName);
T=xlsxData{k};
writetable(T(1:height(T),1:width(T)), 'dummy.csv');
opts = detectImportOptions('dummy.csv');
getvaropts(opts,{''}) % your columns names
opts.SelectedVariableNames = {''}; % col name seperated by commas
TT=readtable('dummy.csv',opts);
TT(:,5)= % something you want to save.
destination='your location';
writetable(TT,[destination,baseFileName,'.xlsx']);
Z = table2array(T(1:height(T),:));
B=str2double(Z);
clear TT T
fh = fopen('dummy.csv','w');
if exist('dummy.csv', 'file')==2
delete('dummy.csv');
%we are deleting dummy as dummy is being created in current folder, so it
%will be detected as a csv and the for loop will go on and on.
end
end

Sign in to comment.

Answers (0)

Products


Release

R2021b

Community Treasure Hunt

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

Start Hunting!