How to combine 3 (or more) separate CSV files into one Excel file with multiple sheets

Hi everyone,

I have a problem. I have three separate CSV files, all with different headers, that I want to combine into one Excel file with multiple sheets, so that each sheet, of course, represents each CSV file.

I may need to combine more than three later on, but for now, three will do. I do not have any code for this because I honestly have no idea how to start. I know how to read in the files themselves, but combining them eludes me. To create the CSV files, I am using 'csvwrite_with_headers', a file I dopwnloaded a while ago from Matlab Central. The file names are 'Pressdata5050.csv', 'Tempdata5050.csv', and 'Velocitydata5050.csv'.

I would appreciate any help you could provide.

p.s. I'm using R2018a

Answer 1

[Stephen23]

D = 'path of folder where the CSV files are';
S = dir(fullfile(D,'*.CSV'));
for k = 1:numel(S)
    mat = csvread(fullfile(D,S(k).name)); % or whatever loads your data
    xlswrite('merged.xlsx', mat, fileparts(S(k).name))
end

If the CSV files contain text then use xlsread instead of csvread:

    [~,~,mat] = xlsread(fullfile(D,S(k).name));

[PATRICK WAYNE]

Wow, that was quick. Thank you, I will give this a try and update the status here.

[PATRICK WAYNE]

Ok, here's my code:

D = 'C:\Users\PWayne\Documents\MATLAB';

S = dir(fullfile(D,'*.CSV'));
for k = 1:numel(S)
    [~,~,mat] = xlsread(fullfile(D,S(k).name));
    xlswrite('Results5050.xlsx', mat, fileparts(S(k).name))
end

This works and imports the headers, but it only outputs the last (3rd) file. I tried using mat(k), but that didn't work. I have to admit, I'm not very good at this.

[PATRICK WAYNE]

Ok, I finally got it to save all three into multiple sheets in one excel file. The problem was sheet specification. Here's the working code:

D = 'C:\Users\PWayne\Documents\MATLAB';
S = dir(fullfile(D,'*.CSV'));
for i = 1:numel(S)
    [~,~,mat] = xlsread(fullfile(D,S(i).name));
    xlswrite('Results5050.xlsx', mat, i);
end

I just need to figure out how to rename the sheets, which shouldn't be too hard. Thank you for your help!

[Stephen23]

Ah, my mistake, you will need to use the second output of fileparts:

D = 'C:\Users\PWayne\Documents\MATLAB';
S = dir(fullfile(D,'*.CSV'));
for k = 1:numel(S)
    [~,~,mat] = xlsread(fullfile(D,S(k).name));
    [~,fnm] = fileparts(S(k).name);
    xlswrite('Results5050.xlsx', mat, fnm);
end

[PATRICK WAYNE]

That makes me so very happy! I was opening the activex server to change the sheet names and I did not like it; still don't. Mainly because even though I closed the activex server and deleted the entry, the Excel process remained, making my workbook 'read-only' unless I either ctrl-alt-deleted and killed the process, or I deleted the workbook and compiled again................very annoying. Thank you again for all your help.