Convert .txt to .xls

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

[John D'Errico]

And your question is what? How is this a question about MATLAB? Especially, since you could probably have done the same using an Excel macro? But even if you choose to use MATLAB, you have code. So what is the problem?

[Michael Angeles]

Hi John, I'm still on the debugging phase of the script since I get errors.

[Walter Roberson]

How about if you use readcell() with 'HeaderLines', 12, and then writecell() ?

Answer 1

[Gyan Vaibhav]

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:

"writetable" documentation: https://www.mathworks.com/help/matlab/ref/writetable.html

Hope this helps.

Thanks

Gyan