Convert .txt to .csv with prespecified format

I have a (.txt) with this format

 2023 JAN  1   00 31 34.1 38.5625 23.6833   14         2.0
 2023 JAN  1   00 38 24.7 38.3304 20.4172   19         1.0
 2023 JAN  1   00 47 15.0 38.3940 21.9118    7         0.9

and i want to make a file with the following format:

 DATETIME;LAT;LONG;DEPTH;MAG
 01-01-2023T00:31:34.1;38.5625;23.6833;14;2.0
 01-01-2023T00:38:24.7;38.3304;20.4172;19;1.0
 01-01-2023T00:47:15.0;38.3940;21.9118;7;0.9

Any help?

[Cris LaPierre]

Please attach an example of the original file to your post using the paperclip icon.

[Ancalagon8]

Here you are:

Answer 1

[Cris LaPierre]

Assuming your input file can be duplicated by copying and pasting the text above into a text file, here is how I would do it.

• Treat the file as a fixed-width file
• Import the date and time as a single variable of type datetime
• Set the display format of the datetime variable to be your desired output format
• Set the variable names to be your desired output names

With the table formatted the way you want, use writetable to create your output file, specifying the desired delimirter.

% set up import options
opts = fixedWidthImportOptions("VariableWidths",[25,8,8,5,12],"NumVariables",5,'DataLines',3);
opts = setvartype(opts,'Var1','datetime');
opts = setvaropts(opts,"Var1",'InputFormat','yyyy MMM d HH mm ss.S','DatetimeFormat','MM-dd-yyyy''T''HH:mm:ss.S');
opts.VariableNames = ["DATETIME" "LAT" "LONG" "DEPTH" "MAG"];

% import original file
data = readtable("originalFile.txt",opts)

data = 3x5 table
          DATETIME               LAT           LONG        DEPTH       MAG  
    _____________________    ___________    ___________    ______    _______

    01-01-2023T00:31:34.1    {'38.5625'}    {'23.6833'}    {'14'}    {'2.0'}
    01-01-2023T00:38:24.7    {'38.3304'}    {'20.4172'}    {'19'}    {'1.0'}
    01-01-2023T00:47:15.0    {'38.3940'}    {'21.9118'}    {'7' }    {'0.9'}

% create output file
writetable(data,'outputFile.txt','Delimiter',';')

Now preview the output file

type('outputFile.txt')

DATETIME;LAT;LONG;DEPTH;MAG
01-01-2023T00:31:34.1;38.5625;23.6833;14;2.0
01-01-2023T00:38:24.7;38.3304;20.4172;19;1.0
01-01-2023T00:47:15.0;38.3940;21.9118;7;0.9

[Cris LaPierre]

Interesting. That function was introduced in R2017a, so it should be available.

For R2018b, you do need to use single quotes for most name-value pairs. Here's the updated code.

% set up import options
opts = fixedWidthImportOptions('VariableWidths',[25,8,8,5,12],'NumVariables',5,'DataLines',3);
opts = setvartype(opts,'Var1','datetime');
opts = setvaropts(opts,'Var1','InputFormat','yyyy MMM d HH mm ss.S');
opts = setvaropts(opts,'Var1','DatetimeFormat','MM-dd-yyyy''T''HH:mm:ss.S');
opts.VariableNames = ["DATETIME" "LAT" "LONG" "DEPTH" "MAG"];

% import original file
data = readtable('originalFile.txt',opts)

% create output file
writetable(data,'outputFile.txt','Delimiter',';')

It ran without error for me in R2018b.

[Cris LaPierre]

What OS are you using? I'm on Windows 11.

[Cris LaPierre]

It should be working. What is the result of running the following commands?

which detectImportOptions
which fixedWidthImportOptions

[Walter Roberson]

R2018b definitely has the fixedWidthImportOptions function.

I wonder whether it is possible that you are using R2018a? The function was introduced in R2018b.

[Cris LaPierre]

The bottom of the fixedWidthImportOptions page indicates it was added in R2017a, but I can reproduce the error in R2018a, so I did some digging. It is indeed available in R2017a, but the calling syntax is different. From the R2018a doc:

Alternatively, for a messy fixed-width text file that is not automatically detected by the detectImportOptions function, create a custom FixedWidthImportOptions object:

opts = matlab.io.text.FixedWidthImportOptions

% set up import options
opts = matlab.io.text.FixedWidthImportOptions;
opts.VariableNames = {'DATETIME' 'LAT' 'LONG' 'DEPTH' 'MAG'};
opts.VariableWidths = [25,8,8,5,12];
opts.DataLines = 3;
opts = setvartype(opts,'DATETIME','datetime');
opts = setvaropts(opts,'DATETIME','InputFormat','yyyy MMM d HH mm ss.S','DatetimeFormat','MM-dd-yyyy''T''HH:mm:ss.S');

% import original file
data = readtable('data.txt',opts)

data = 3x5 table
          DATETIME               LAT           LONG        DEPTH       MAG  
    _____________________    ___________    ___________    ______    _______

    01-01-2023T00:31:34.1    {'38.5625'}    {'23.6833'}    {'14'}    {'2.0'}
    01-01-2023T00:38:24.7    {'38.3304'}    {'20.4172'}    {'19'}    {'1.0'}
    01-01-2023T00:47:15.0    {'38.3940'}    {'21.9118'}    {'7' }    {'0.9'}

% create output file
writetable(data,'outputFile.txt','Delimiter',';')

Now preview the output file

type('outputFile.txt')

DATETIME;LAT;LONG;DEPTH;MAG
01-01-2023T00:31:34.1;38.5625;23.6833;14;2.0
01-01-2023T00:38:24.7;38.3304;20.4172;19;1.0
01-01-2023T00:47:15.0;38.3940;21.9118;7;0.9