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

Question

roborrr 2022-2-23

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1656855-importing-data-from-a-text-file-with-txt-extension-data-processing-and-export-it-to-excel

评论： Arif Hoq 2022-3-4

采纳的回答： Arif Hoq

example.txt

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:

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Arif Hoq 2022-3-1

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1656855-importing-data-from-a-text-file-with-txt-extension-data-processing-and-export-it-to-excel#answer_906745

编辑：Arif Hoq 2022-3-1

在 MATLAB Online 中打开

example.xlsx

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 2

roborrr 2022-3-1

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1656855-importing-data-from-a-text-file-with-txt-extension-data-processing-and-export-it-to-excel#answer_906845

编辑：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 个评论
显示 4更早的评论隐藏 4更早的评论

Peter Perkins 2022-3-3

在 MATLAB Online 中打开

I had not realized that there was both a .txt and a .xlsx file attached to this post. I think the .xlsx is not what you are starting from, and in fact would be a big step towards what you want to end up with. But you need to write code that can make sense of understand the .txt automatically.

xlsread is for spreadsheets, and is not recommended any more. I was thinking readcell, but actually, readtable does a decent job:

>> readtable('example.txt','delimiter','\t')
ans =
  16×8 table
             Var1              Var2        Var3       Var4        Var5       Var6    Var7                Var8             
    _______________________    _____    __________    _____    __________    ____    ____    _____________________________
    2022-02-13 23:35:11.200    41.15    {'N'     }    43.97    {'E'     }      2       3     {'ARMENIA'                  }
    2022-02-13 20:56:02.400    41.28    {'N'     }    43.96    {'E'     }      2       2     {'GEORGIA (SAK'ART'VELO)'   }
    2022-02-13 20:26:08.500    41.15    {'N'     }    44.02    {'E'     }      2     2.1     {'ARMENIA'                  }
    2022-02-13 19:11:45.800    38.66    {'N'     }    44.84    {'E'     }     10     2.4     {'TURKEY-IRAN BORDER REGION'}
    2022-02-13 18:50:38.700    41.31    {'N'     }    43.99    {'E'     }      2     2.1     {'GEORGIA (SAK'ART'VELO)'   }
                        NaT      NaN    {0×0 char}      NaN    {0×0 char}    NaN     NaN     {0×0 char                   }
                        NaT      NaN    {0×0 char}      NaN    {0×0 char}    NaN     NaN     {0×0 char                   }
    2022-02-13 18:34:59.500    41.23    {'N'     }    44.12    {'E'     }      2     3.1     {'GEORGIA (SAK'ART'VELO)'   }
                        NaT      NaN    {0×0 char}      NaN    {0×0 char}    NaN     NaN     {0×0 char                   }
                        NaT      NaN    {0×0 char}      NaN    {0×0 char}    NaN     NaN     {0×0 char                   }
    2022-02-13 18:28:46.000    41.17    {'N'     }    43.97    {'E'     }      2     4.2     {'GEORGIA (SAK'ART'VELO)'   }
                        NaT        6    {0×0 char}      NaN    {0×0 char}    NaN     NaN     {0×0 char                   }
                        NaT      NaN    {0×0 char}      NaN    {0×0 char}    NaN     NaN     {0×0 char                   }
    2022-02-13 18:25:56.200    41.14    {'N'     }    43.99    {'E'     }     10     5.4     {'ARMENIA'                  }
    2022-02-13 12:09:38.900     38.9    {'N'     }    43.51    {'E'     }      1       3     {'EASTERN TURKEY'           }
    2022-02-13 05:31:04.100    42.58    {'N'     }    45.39    {'E'     }      5     2.8     {'CAUCASUS REGION, RUSSIA'  }

Now just clean things up:

delete rows with NaT times
delete Var3 and Var5 (I think)
probably turn Var8 into categorical

That pretty much gets you there, I think.

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.

Arif Hoq 2022-3-4

i was wrong on my step. I followed what you said. it works. thanks a lot Peter.

请先登录，再进行评论。

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

更多回答（1 个）

6 个评论
显示 4更早的评论隐藏 4更早的评论

另请参阅

类别

标签

Community Treasure Hunt

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

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

更多回答（1 个）

6 个评论 显示 4更早的评论隐藏 4更早的评论

另请参阅

类别

标签

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

6 个评论
显示 4更早的评论隐藏 4更早的评论