Multiple import/edit of xls files

Dear All, I have around 1400 xls files with 2 sheets in each file.(with the filenamelike 50005100.xls but without a pattern) Here is what I want to do: 1) delete the first sheet; 2) insert a column with header(ID) and then fill in the column with the file name in the second sheet. then export the file to csv(I know how to do that) I need to do this to all of them one by one. Can anyone help me with this? Million thanks. Az

Answer 1

[Chirag Gupta]

This should work! You might need to tweak it slightly!

hExcel = actxserver('Excel.Application');
hExcel.visible = 1; % If you want Excel visible.
hExcel.DisplayAlerts = false; % Avoid excel warning popups

for i:length(filenames) % Assuming filenames contains the 1400 xls files
   Wkbk = hExcel.Workbooks.Open(fullfile(pathToFile, filenames{i})); % Opens Excel file
   Sheets = Wkbk.Sheets; % Get the Sheets
   Sheets.Item(1).Delete; % Deletes the first sheet
   Wkbk.ActiveSheet.Cells.Item(1).EntireRow.Insert; Assuming you want to insert a row and not a column
   Wkbk.ActiveSheet.Cells.Range('A1').Value = filenames{i}; % Insert Filename
   Wkbk.Save; Save xls file
   Wkbk.SaveAs('C:\MatlabWork\Answers\Test.csv', 6); Save as .csv (file format for csv enum is 6)
   Wkbk.Close;

end

hExcel.Quit;
hExcel.delete;

[Az]

First, many thanks to you,Chirag Gupta

This is like the second time I work with Matlab.

Apologies if my questions are dumb.

Is this code for Matlab?

1)How can I deal with the filenames? they are like 43423232.xls,5485747.xls with NO pattern.

2) in addtion, I want to delete the 1,2,5,7 columns in the second sheet as well how do I do that?

Thank you so much!!!!!!

[Az]

one more, I want to save the converted file with the same filename as of the xls file How should I tweak that?

Thanks!!!

[Chirag Gupta]

This is MATLAB code!

I am assuming that you have a list of filenames in a cell array. The code above is quite complex for a beginner especially if you are not familiar with VBA.

filenames = {'79802.xls','682038.xls',.... and so on};

Also to delete particular Columns, you will need to look on the MSDN website.

But it is typically:

Wkbk.ActiveSheet.Cells.Range('C1').EntireColumn.Delete;

Remember after you delete column C, column D will automatically become column C. Take that into account when deleting columns successively

[Chirag Gupta]

To save it with the same name:

You need to change the SaveAs command and replace it filename with the name you want

[Az]

Thanks Chirag,

So basically, I need to type in all the filenames manually?

Many thanks!

[Walter Roberson]

Do you want to work with all of the .xls files in the directory? If so then you can use dir('*.xls') to get file information.

[Az]

yes，thanks Walter. I'll try that.

Answer 2

[ricco]

I have a question which is similar to this. I have a code which loads data from several spreadsheets into excel:

files = dir('*.xls');
%read data from excel into matlab
for i=1:length(files);
    File_Name{i}=files(i,1).name;%Removes the file names from 'files'
    [num{i},txt{i},raw{i}] = xlsread(File_Name{i},'Ble min');
end

Where the loop imports the workbook entitled 'Ble min' from different spreadsheets. How could I use actxserver to do the same i.e. to speed the process up.

thanks