Convert .txt to .xls
2 views (last 30 days)
Show older comments
I have a code that I need to convert multiple .txt files to .xls files and save with the same filename.
The first 12 lines of the file are descriptions of settings only. I needed these to be separated as Delimited, Comma, Colon, and Other: \
The text files from line 13 and below are separated by comma. I need these to sorted as columns.
% Path to the folder containing .txt files
txtFolder = '/path/to/txt/files/';
% Get a list of .txt files in the folder
txtFiles = dir(fullfile(txtFolder, '*.txt'));
% Loop through each .txt file
for i = 1:numel(txtFiles)
% Read the content of the current .txt file
txtPath = fullfile(txtFolder, txtFiles(i).name);
fileID = fopen(txtPath, 'r');
txtContent = textscan(fileID, '%s', 'Delimiter', '\n');
fclose(fileID);
% Process data (lines 13 and beyond)
dataLines = txtContent{1}(13:end);
numColumns = 6; % Number of columns for each line
processedData = cell(numel(dataLines), numColumns);
% Split and store data into columns
for j = 1:numel(dataLines)
dataValues = strsplit(dataLines{j}, ',');
numValues = min(numColumns, numel(dataValues));
processedData(j, 1:numValues) = dataValues(1:numValues);
end
% Create an Excel file
excelFilename = [erase(txtFiles(i).name, '.txt'), '.xlsx'];
excelPath = fullfile(txtFolder, excelFilename);
% Write processed data to Excel using xlsxWrite
xlsxWrite(excelPath, processedData);
end
3 Comments
Walter Roberson
on 2 Sep 2023
How about if you use readcell() with 'HeaderLines', 12, and then writecell() ?
Answers (1)
Gyan Vaibhav
on 16 Nov 2023
Hi Michael,
I understand that your goal is to process data from “TXT” files and convert them into “XLS” files using MATLAB.
The code you've shared appears to be mostly correct, but there seems to be a small mistake. The correct function name should be “xlswrite”, not “xlsxWrite”.
However, it's worth noting that there are more efficient and compatible methods for writing to spreadsheets. For instance, the “writetable” function is generally recommended over “xlswrite” due to better compatibility. Before using “writetable”, you'll need to convert your cell array to a table.
Additionally, the “strsplit” function may not handle quoted strings containing commas as expected. A more reliable approach would be to use “textscan" with a comma as the delimiter. This will ensure accurate data parsing, even when your data includes quoted strings with commas.
for j = 1:numel(dataLines)
dataValues = textscan(dataLines{j}, '%s', 'Delimiter', ',');
dataValues = dataValues{1}';
numValues = min(numColumns, numel(dataValues));
processedData(j, 1:numValues) = dataValues(1:numValues);
end
% Create an Excel file
excelFilename = [erase(txtFiles(i).name, '.txt'), '.xls'];
excelPath = fullfile(txtFolder, excelFilename);
% Convert cell array to table
T = cell2table(processedData);
% Write processed data to Excel using writetable
writetable(T, excelPath);
The above code can be included in the existing code, and it should give the expected results.
For more details about the “writetable” function refer to the following documentation:
Hope this helps.
Thanks
Gyan
0 Comments
See Also
Categories
Find more on Spreadsheets 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!