converting multiple textfiles to csv and xlsx format.

I have the code for the one file. how can I loop it through?

Here is the code.

filename = 'myFile.txt';
format = '%c';
data=readlines(filename);
data = replace(data, ",", " ");
% data=strrep(data,char(9),',');
%mat = char(data1);
%int = str2double(data1);
writelines(data, 'myFile.csv');
opts = delimitedTextImportOptions("NumVariables", 522);
% Specify range and delimiter
otps.row = [3, 33];
otps.Delimiter = " ";
% Specify column names and types
opts.VNames = ["bin_depth", "depth", "wt", "salinity", "stimf"]
otps.VTypes = ["double", "double", "double", "double", "double"]
table = readtable( "myFile.csv", opts);
writetable(table, 'myFile.xlsx', 'sheet',1,'Range','A1')

I also want to retain the respective names of the text files.

[Walter Roberson]

Why would you bother reading the file, changing the delimiter to space, writing the file, reading the file, writing the file?

Why not just readtable() specifying the VariableNames and writetable that? If you need to skip lines you can do that when reading from the original file.

You create options for 522 columns but you only set variable names for 4, which will be a problem.

[Arnab Paul]

Well I have attached a sample file. I am reading it because I wanted to see how it looks like, and I wanted both csv and excel data. also, well a newbie approach. It has mixed delimters and 522 variables/columns but I did not want to put all of them together. That's why.

Thank you.

Answer 1

[Walter Roberson]

format long g

filename = 'https://www.mathworks.com/matlabcentral/answers/uploaded_files/1128290/f64d5aab60_IOP_20130909_2155.txt';
Lines = readlines(filename);

%variable names are on the first line
variablenames = regexp(Lines(1), ',', 'split');
variablenames(1:5) = ["bin_depth", "depth", "wt", "salinity", "stimf"];

%get rid of lines that do not start with a number
Lines( cellfun(@isempty, regexp(Lines, '^\d')) ) = [];

%we are going to use textscan(), which needs the text as a continuous
%string, not an array
as_text = strjoin(Lines, newline);

%textscan can figure out how many columns there are if you use empty format
%textscan returns cell array; we convert that to array and array to table
fmt = '';
data = array2table( cell2mat( textscan(as_text, fmt)), 'VariableNames', variablenames);

writetable(data, 'myFile.xlsx', 'sheet',1,'Range','A1')

[Arnab Paul]

Hello walter,

Thank you. This is a very crisp code. I will try to convert it in a loop. Any suggestion is appreciated.

[Walter Roberson]

dinfo = dir('*IOP*.txt');
filenames = fullfile({dinfo.folder}, {dinfo.name});
num_files = length(filenames);
for K = 1 : num_files
    filename = filenames{K};
    
    Lines = readlines(filename);
    and so on
end

But be careful -- you are not going to be overwriting the same column of the same sheet of the same file. Perhaps use 'sheet', K instead of 'sheet', 1

[Arnab Paul]

myFolder = '/Users/gulfcarbon2/Downloads/Modis/absorption file';
filePattern = fullfile(myFolder, '*.txt'); % Files with .txt extension
theFiles = dir(filePattern);
for k = 1:length(theFiles)
    baseFileName = theFiles(k).name;
    fullFileName = fullfile(theFiles(k).folder, baseFileName);
    fprintf(1, 'Now reading %s\n', fullFileName);
    Lines = readlines(fullFileName);
    %variable names are on the first line
    variablenames = regexp(Lines(1), ',', 'split');
    variablenames(1:5) = ["bin_depth", "depth", "wt", "salinity", "stimf"]
    %get rid of lines that do not start with a number
    Lines( cellfun(@isempty, regexp(Lines, '^\d')) ) = [];
    %we are going to use textscan(), which needs the text as a continuous
    %string, not an array
    as_text = strjoin(Lines, newline);
    %textscan can figure out how many columns there are if you use empty format
    %textscan returns cell array; we convert that to array and array to table
    fmt = '';
    data = array2table( cell2mat( textscan(as_text, fmt)), 'VariableNames', variablenames);
    writetable(data, [baseFileName, '.xlsx'], 'sheet',1,'Range','A1')
end

I used this one. It worked pretty good. Thank you again