Script Converting txt to CSV

Trying to create a scrip that converts txt to csv.

I need to skip the first line.

All rows thereafter (variable), have a timestamp in format DD-MM-YYYY HH:MM:SS AM/PM followed by five more columns of data with character delimaters W,C,A,B,R.

Any ideas how to get this running?

Codestart:

Sample data lines

06-14-2019 12:56:24 PM

06-14-2019 12:56:37 PM W-00.3C2.20e-12A6.32B3.34R029

06-14-2019 12:56:42 PM W-00.4C5.15e-13A6.31B3.32R062

06-14-2019 12:56:47 PM W-00.7C4.06e-13A6.29B3.31R118

Answer 1

[ag]

Hi Patrick,

To convert the text file to csv, you can use the "fgetl" MATLAB function to read and process each line.

Below is a self explanatory code snippet, that demonstrates how you can do that:

% Define the format for the data lines
formatSpec = '%s %f %f %f %f %f';

% Read and process each line from the input file
% Ignore the first line
fgetl(fileIdTxt);  
while ~feof(fileIdTxt)
    % Read the next line
    line = fgetl(fileIdTxt);
    
    % Split the line using the delimiters
    parts = split(line, {' ', 'W', 'C', 'A', 'B', 'R'});
    
    % Check if the line was split into the expected number of parts
    if length(parts) == 7
        % Extract the timestamp and data
        timestamp = parts{1};
        data = str2double(parts(2:end));
        
        % Write the data to the output CSV file
        fprintf(fileIdCsv, '%s,%.2f,%.2f,%.2f,%.2f,%.2f\n', timestamp, data);
    end
end

For more details, please refer to the following MathWorks documentations:

• fgetl - https://www.mathworks.com/help/matlab/ref/fgetl.html
• feof - https://www.mathworks.com/help/matlab/ref/feof.html
• split - https://www.mathworks.com/help/matlab/ref/string.split.html

Hope this helps!

[Voss]

Including ' ' (space) in the set of delimiters gives 9 parts with this example data, not 7, with the timestamp split into three parts

line = '06-14-2019 12:56:37 PM W-00.3C2.20e-12A6.32B3.34R029';

parts = split(line, {' ', 'W', 'C', 'A', 'B', 'R'})

parts = 9x1 cell array
    {'06-14-2019'}
    {'12:56:37'  }
    {'PM'        }
    {0x0 char    }
    {'-00.3'     }
    {'2.20e-12'  }
    {'6.32'      }
    {'3.34'      }
    {'029'       }

Better to exclude the space because then you have 6 nicely delimited parts, with the timestamp all together.

parts = split(line, {'W', 'C', 'A', 'B', 'R'})

parts = 6x1 cell array
    {'06-14-2019 12:56:37 PM '}
    {'-00.3'                  }
    {'2.20e-12'               }
    {'6.32'                   }
    {'3.34'                   }
    {'029'                    }

Also, your formatSpec variable is unused.

And when you write to csv, you can use %s format for all parts, since that's consistent with they already are (no need to convert to double unless you need that validation step).

fileIdTxt = fopen('test.txt','r');
fileIdCsv = fopen('test.csv','w');

% Read and process each line from the input file
% Ignore the first line
fgetl(fileIdTxt);  
while ~feof(fileIdTxt)
    % Read the next line
    line = fgetl(fileIdTxt);
    
    % Split the line using the delimiters
    parts = split(line, {'W', 'C', 'A', 'B', 'R'});
    
    % Write the data to the output CSV file
    fprintf(fileIdCsv, '%s,', parts{:});
    fprintf(fileIdCsv, '\n');
end

type test.csv

06-14-2019 12:56:37 PM ,-00.3,2.20e-12,6.32,3.34,029,
06-14-2019 12:56:42 PM ,-00.4,5.15e-13,6.31,3.32,062,
06-14-2019 12:56:47 PM ,-00.7,4.06e-13,6.29,3.31,118,

[ag]

Hey Voss, thanks for pointing out the mistakes and providing the suggestions.

Answer 2

[Voss]

str = readlines('test.txt')

str = 4x1 string array
    "06-14-2019 12:56:24 PM "
    "06-14-2019 12:56:37 PM W-00.3C2.20e-12A6.32B3.34R029"
    "06-14-2019 12:56:42 PM W-00.4C5.15e-13A6.31B3.32R062"
    "06-14-2019 12:56:47 PM W-00.7C4.06e-13A6.29B3.31R118"

str(1) = [];

C = regexp(str,'[WCABR]','split');
out = vertcat(C{:});
writematrix(out,'test.csv')

% check the csv file's contents
type test.csv

06-14-2019 12:56:37 PM ,-00.3,2.20e-12,6.32,3.34,029
06-14-2019 12:56:42 PM ,-00.4,5.15e-13,6.31,3.32,062
06-14-2019 12:56:47 PM ,-00.7,4.06e-13,6.29,3.31,118