Importing data from a text file (with ".txt" extension), data processing and export it to excel

4 次查看(过去 30 天)
There is a text file "example.txt" (file attached here) containing the following data:
Date & Time Latitude Longitude Depth Mag Region name
2022-02-13 23:35:11.2 41.15 N 43.97 E 2 3.0 ARMENIA
2022-02-13 20:56:02.4 41.28 N 43.96 E 2 2.0 GEORGIA (SAK'ART'VELO)
2022-02-13 20:26:08.5 41.15 N 44.02 E 2 2.1 ARMENIA
2022-02-13 19:11:45.8 38.66 N 44.84 E 10 2.4 TURKEY-IRAN BORDER REGION
2022-02-13 18:50:38.7 41.31 N 43.99 E 2 2.1 GEORGIA (SAK'ART'VELO)
30
IV
2022-02-13 18:34:59.5 41.23 N 44.12 E 2 3.1 GEORGIA (SAK'ART'VELO)
29
V
2022-02-13 18:28:46.0 41.17 N 43.97 E 2 4.2 GEORGIA (SAK'ART'VELO)
303 6
V
2022-02-13 18:25:56.2 41.14 N 43.99 E 10 5.4 ARMENIA
2022-02-13 12:09:38.9 38.90 N 43.51 E 1 3.0 EASTERN TURKEY
2022-02-13 05:31:04.1 42.58 N 45.39 E 5 2.8 CAUCASUS REGION, RUSSIA
I need to import this file into matlab, Delete all columns consisting of only one character - "N" or "E", remove the all spaces at the beginning and end of each row, and inside the row from several subsequent spaces, leave only one. I also want to remove all the empty rows and also the rows which have a different format from the general one, create correct table and export it to excel. As a result, the following table should appear in the excel sheet:

采纳的回答

Arif Hoq
Arif Hoq 2022-3-1
编辑:Arif Hoq 2022-3-1
I have export your text data into excel. then i work with this excel in matlab.
[number,str,all]=xlsread('example.xlsx'); % export data from excel file
all(:,[3 5])=[]; % deleting unnecessay column 3 and 5
A=all;
B=string(A); % convert into string array
D=rmmissing(B); % delete the missing entries
Name=D(1,:); % variable name
datetime=D(2:end,1);
D1=split(datetime," "); % split the date and time
variablename=['Date' 'Time' Name(2:end)]; % concatenate variable name
matrix=[D1 D(2:end,2:end)]; % conctenation
output=[variablename;matrix]; % concatenate the expected output
out1=cellstr(output);
writematrix(output, 'MyData.xlsx') % string array
writecell(out1,'MyData2.xlsx') % cell array

更多回答(1 个)

roborrr
roborrr 2022-3-1
编辑:roborrr 2022-3-1
Thanks for the answer, but you manually exported the data from a text file to an Excel file(example.txt to example.xlsx) and manually gave the table such a correct form when all columns have separate headers. this is my main problem, so that matlab does this programmatically. I want matlab to read data directly from a text file, determine that the last column should be a character vector and not divide by spaces, and then matlab acted as described in the task. This last phase of the task you carried out beautifully, for which many thanks.
  6 个评论
Peter Perkins
Peter Perkins 2022-3-3
I don't understand. If you have this as a spreadsheet, just read that into a table and proceed as I descibed with the table you get back. Getting number,str,all isn't going to help you.

请先登录,再进行评论。

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by