Change one line in Excel file
2 views (last 30 days)
Show older comments
Hello!
I want to change one line (the header) in many Excel files for consistency (so I can merge them using readtable() ). Individuals created the Excel files themselves, and have minor variations in the header that muck up readtable(). I have a loop, below, to fix the Excel files, but have tried xlsread/xlswrite, cellread/cellwrite, and have issues with each. Suggestions?
addpath(fileDir);
fileNames = dir(fileDir);
fileNames = {fileNames.name};
fileNames = fileNames(cellfun(@(f)contains(f,'.xls'),fileNames));
for f = 1:numel(fileNames)
flist = xlsread(fileNames{f});
% flist_orig = flist;
flist{1,1} = 'Topic Number';
flist{1,2} = 'First Name';
flist{1,3} = 'Last Name';
flist{1,4} = 'Mentoring Topic';
flist{1,5} = 'Years Experience';
flist{1,6} = 'Location';
flist{1,7} = 'Email Address';
xlswrite(filenames{f},flist); %cell(flist,'junkofix.xlsx'); %fileNames{f});
end
I set "fileDir" before this runs, of course!
Thanks.
Doug Anderson
0 Comments
Answers (2)
dpb
on 10 Jun 2021
I'd avoid xlswrite and use writecell instead. Use the 'Range' parameter to put in the right place and not disturb anything else, of course.
NB: I have a bunch of these must deal with all the time and rather than trying to fight the losing battle of the users not continuing to create new versions from same old versions as had before even after fixing them, I instead just rename the variables in the table to match the ones I want instead.
0 Comments
Image Analyst
on 10 Jun 2021
Do you have Windows? If so, I'd use ActiveX. The advantage of that over writecell() is that you won't destroy any formatting (like bolding, cell colors, borders, etc.) like writecell() does.
Alternatively you can just reassign the headers after they've been inported into MATLAB.
1 Comment
dpb
on 10 Jun 2021
Edited: dpb
on 11 Jun 2021
I'm 100% in agreement to the latter alternative as I noted as well; don't even bother to try to fix the originals; the hired help will just create new ones with the same problem in their place, anyways...and, yeah, go ahead and ask me how I know that! :)
writecell(...,'UseExcel',true,'AutoFitWidth',false,'PreserveFormat',true);
handles(*) the above without the grief of having to do the connection and low-level stuff directly.
If one is simply writing one or two records, the overhead isn't bad; I agree if need to update a bunch of different ranges separately, then the overhead of opening/closing the connection every write is a killer.
There's a FileExchange submission xlswriteEx that creates a persistent connection object to get around this that works a champ; I highly recommend it for a little higher-level interface but that will not grind to a snail's pace or hang when writing many rows in one-at-a-time fashion where it's difficult to build the full sheet content at one time.
(*) I'm not sure which release introduced these; I believe it was in R2019b I first became aware of them.
See Also
Categories
Find more on Switches and Breakers in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!