Converting a CSV into a XLS through MatLab

Hello

I have a large set of data that is formatted in .csv and I was wondering if there was a way through MatLab that could convert it to an .xls.

Thanks,

-Frank

source_dir = 'C:\Users\xuf\Desktop\Raw Data (CSV)'
dest_dir = 'C:\Users\xuf\Desktop\Converted Raw Data (Excel)'
source_files = dir(fullfile(source_dir, '*.csv'));

for i = 1:length(source_files)
    data= csvread(fullfile(source_dir,source_files(i).name));
    xlswrite(fullfile(dest_dir,source_files(i).name), data);
end

Answer 1

[Andy]

You could import it (in various ways: csvread, import, etc.) and then write out to Excel via xlswrite.

[Frank]

Hey

I'm trying to take this on, just like the loop problem before. However I'm getting the errors :

??? Error using ==> dlmread at 145

Mismatch between file and format string.

Trouble reading number from file (row 1, field 1) ==> Times

Error in ==> csvread at 50

m=dlmread(filename, ',', r, c);

Error in ==> CSVconversion at 6

data= csvread(fullfile(source_dir,source_files(i).name));

[Walter Roberson]

Notice the last sentence here:

http://www.mathworks.com/help/techdoc/ref/csvread.html

M = csvread(filename) reads a comma-separated value formatted file, filename. The filename input is a string enclosed in single quotes. The result is returned in M. The file can only contain numeric values.

[Andy]

I didn't realize you had non-numeric data. In this case, your best bet is probably to read and write with xlsread and xlswrite (xlsread can read csv):

[dummy, dummy, raw] = xlsread('filename.csv')

xlswrite('filename.xls')

[Walter Roberson]

You probably want to tell xlswrite() what you want written ;-)

[Andy]

Good catch, sorry:

[dummy, dummy, raw] = xlsread('filename.csv')

xlswrite('filename.xls',raw)

Answer 2

[Fangjun Jiang]

Why bother? Open the .csv file in Excel and save it as .xls.

[Walter Roberson]

Or in OpenOffice if Excel isn't handy.

[Frank]

Hi

Well, I have 20 .csv files and probably more in the future. I would like to compile a script that would quickly execute the conversion.

[Fangjun Jiang]

If you don't need to do any data processing and just need to do the conversion for multiple files, I suggest you use the actxserver('excel.application'). Go through a loop, open the .csv file and save it as .xls file.

[Andy]

That's what xlsread does anyway. Why reinvent the wheel? See my comment on my answer above.

[Fangjun Jiang]

It certainly will be much faster. You can open xlsread.m and xlswrite.m to see how much stuff is wasted for this task.

[Walter Roberson]

How much time spent coding and debugging the actxserver() version? How much time spent executing xlsread() and xlswrite() for 20 files?

[Fangjun Jiang]

I don't want to get into an argument here. But for using actxserver(), it needs one command to open the server at the beginning and one command at the end to delete it. For each loop, one command to open the csv file and one command to save as .xls file.

The time save would be huge if the Excel file is large.

In terms of debugging, that will depends on your experience, right?

At issue is taking the right approach to tackle the task.

Answer 3

[Harish TV]

clear;
clear all;
fileip='filename.csv';
g=char(fileip);
g=g(1:end-4)
fileop=horzcat(g,'.xlsx')
g=fileip(1:end-4);
[~,~,F]=xlsread(fileip);
F=cellstr(F);
%delete the header rows (4 rows)
F([1:4],:)=[];
for a=1:size(F,1);
b=F(a,1);
c=char(b);
D(a,:)=strsplit(c,',');
end  
xlswrite(fileop,D);
disp(['--------------Process complete---------------------']);