How to open multiple .dat files in Matlab and save them in the given name as .xlsx file per sheet

I attached 10 .dat files. Each of the files contain 40 years hourly data. Kindly help me with the matlab codes to open and save the in .xlsx formats using the file names individually in a file sheet by sheet. I will also use the codes for the other 30 files. Thanks.

Ojo O. S

Answer 1

[Image Analyst]

For some reason, I'm having trouble unzipping your data. Window10 won't allow it. In the meantime, try this:

% Specify the folder where the files live.
myFolder = 'D:\data files';
% Check to make sure that folder actually exists.  Warn user if it doesn't.
if ~isfolder(myFolder)
	errorMessage = sprintf('Error: The following folder does not exist:\n%s\nPlease specify a new folder.', myFolder);
	uiwait(warndlg(errorMessage));
	myFolder = uigetdir(); % Ask for a new one.
	if myFolder == 0
		% User clicked Cancel
		return;
	end
end
% Get a list of all files in the folder with the desired file name pattern.
filePattern = fullfile(myFolder, '*.dat'); % Change to whatever pattern you need.
theFiles = dir(filePattern);
numFiles = length(theFiles)
Result = zeros(numFiles, 1);
for k = 1 : numFiles
	baseFileName = theFiles(k).name;
	[~, baseFileNameNoExt, ext] = fileparts(baseFileName);
	inputFullFileName = fullfile(theFiles(k).folder, baseFileName);
	fprintf(1, 'Now reading %s\n', inputFullFileName);
	% Now do whatever you want with this file name,
	% such as reading it in as an image array with imread()
	data = readmatrix(inputFullFileName, 'x.);
	outputFullFileName = fullfile(myFolder, baseFileNameNoExt, '.xlsx');
	fprintf(1, 'Now writing %s\n', outputFullFileName);
	xlswrite(outputFullFileName, data);
end

[Ojo Olusola]

Thank you for the helpful response. Note that Each of the .dat files has some columns without line of demarcation. But I received this error message in the course of executing the code.

numFiles =

40

Now reading C:\Users\HP\Desktop\RHpaper\Rehum\Rehum\Abeokuta.dat

Error using readmatrix (line 158)

All parameters must have an associated value.

Error in extract1 (line 29)

data = readmatrix(inputFullFileName, 'x.');

Kindly help. Thanks.

[Image Analyst]

I don't know how that got in there. Must have been a typo. Should be

data = readmatrix(inputFullFileName);

[Ojo Olusola]

Thank you. I got this error message again:

Now reading C:\Users\HP\Desktop\RHpaper\Rehum\Rehum\Abeokuta.dat

Now writing C:\Users\HP\Desktop\RHpaper\Rehum\Rehum\Abeokuta\.xlsx

Error using xlswrite (line 224)

Invoke Error, Dispatch Exception:

Source: Microsoft Excel

Description: SaveAs method of Workbook class failed

Help File: xlmain11.chm

Help Context ID: 0

Error in extract1 (line 30)

xlswrite(outputFullFileName, data);

[Image Analyst]

I thought I saw people using 3 things in fullfile() before. But anyway, this works:

outputFullFileName = fullfile(myFolder, [baseFileNameNoExt, '.xlsx']);

I combined the last two with brackets to form one argument.

[Ojo Olusola]

Thank you very much. This work perfectly.

i want the excel files to be on different sheets in one data file.

Thanks.

[Image Analyst]

Change the sheet name

sheetName = sprintf('Results %d', k);  % Whatever....
xlswrite(outputFullFileName, data, sheetName, 'A1');

[Ojo Olusola]

Thank you for your response. The excell files are still listing one by one. Please, I want all the excell files to be in sheet by sheet under one file. e.g sheet 1 (Abeokuta) sheet 2 (Ado-Ekiti) Sheet 3 (Akure) ... under one file name 'mydata'. Thanks.

[Ojo Olusola]

I mean under one file name 'mydata.xlsx'. Thanks

[Image Analyst]

Then simply set up your outputFullFileName, BEFORE the for loop. Then it will save into the same workbook all the time. Of course to precent overwriting you're going to have to change the cell reference, like

outputFullFileName = 'whatever.xlsx'
nextRow = 1;
for k = 1 : numFiles
	baseFileName = theFiles(k).name;
	[~, baseFileNameNoExt, ext] = fileparts(baseFileName);
	inputFullFileName = fullfile(theFiles(k).folder, baseFileName);
	fprintf(1, 'Now reading %s\n', inputFullFileName);
	% Now do whatever you want with this file name,
	% such as reading it in as an image array with imread()
	data = readmatrix(inputFullFileName, 'x.);
	cellReference = sprintf('A%d', nextRow);
   	fprintf(1, 'Now writing %s\n', outputFullFileName);
	xlswrite(outputFullFileName, data);
        % Increment the next row to be just below this data
        nextRow = nextRow + size(data, 1);
end

[Ojo Olusola]

Thank you. This code work efficiently.