时间戳

首页 > 解决方案 > 将文本行数据文件转换为 Cloumn 数据文件

csv - 将文本行数据文件转换为 Cloumn 数据文件

问题描述

我有一个包含电影数据的文本文件。

此文本文件类似于 csv 文件，数据按行排列。

每部电影都有多个记录：

Movie ID    
Movie Poster 1 Link    
Movie Poster 2 Link    
Movie Trailer Link    
Movie Title    
Movie Label ID    
Movie Year

现在的问题是每部电影都重复所有记录，除了：

Movie Poster 2 Link    
Movie Trailer Link

可能有一个值，也可能没有。

如果这 (2) 条记录没有价值，则该特定电影的记录本身将不存在。

我无法解决这个问题。我必须处理我手中的数据。

我找到了一种解决方案，可以将此文本文件中的行转换为 excel 中的列，但我现在的问题是我有大量数据，直接在 excel 中应用该解决方案变得非常困难。需要将此具有行数据的文本文件转换为具有列数据的 csv 文件，同时牢记那些 (2) 记录的出现或消失：

Movie Poster 2 Link
Movie Trailer Link

感谢任何解决此问题的建议。

下面是一个示例文本文件，其中包含 (5) 部电影及其记录：

Movie ID: 1001;
Movie Poster 1 Link: http ://poster_link1001_a;
Movie Trailer Link: http ://trailer_link1001;
Movie Title: Sleeping Beauty
Movie Label ID: SLEEPING_1001;
Movie Year: (1959);
Movie ID: 1002;
Movie Poster 1 Link: http ://poster_link1002_a;
Movie Title: Superman II
Movie Label ID: SUPERMANII_1002;
Movie Year: (1980);
Movie ID: 1003;
Movie Poster 1 Link: http ://poster_link1003_a;
Movie Poster 2 Link: http ://poster_link1003_b;
Movie Trailer Link: http ://trailer_link1003;
Movie Title: Maverick
Movie Label ID: MAVERICK_1003;
Movie Year: (1994);
Movie ID: 1004;
Movie Poster 1 Link: http ://poster_link1004_a;
Movie Title: Arthur
Movie Label ID: ARTHUR_1004;
Movie Year: (1981);
Movie ID: 1005;
Movie Poster 1 Link: http ://poster_link1005_a;
Movie Trailer Link: http ://trailer_link1005;
Movie Title: Before Sunset
Movie Label ID: BEFORESUNSET_1005;
Movie Year: (2004);

以下是将这些行转换为列的方式：

数据列

提前致谢。

标签： csv

解决方案

您可以在 VBA 中编写类似的内容。创建一个新模块，然后输入：

Public Sub ImportFile()

    Dim FileName As String
    Dim FileLine As String
    Dim RowToWrite As Integer

    ' This is the file containing the data
    FileName = "C:\test.txt"
    RowToWrite = 1

    ' Open the file
    FileNum = FreeFile()
    Open FileName For Input As #FileNum

    ' Loop through the file
    While Not EOF(FileNum)

        ' Read the line
        Line Input #FileNum, FileLine

        ' Ask WriteData subroutine to write data
        WriteData "Movie ID:", "A", RowToWrite, FileLine
        WriteData "Movie Poster 1 Link:", "B", RowToWrite, FileLine
        WriteData "Movie Poster 2 Link:", "C", RowToWrite, FileLine
        WriteData "Movie Trailer Link:", "D", RowToWrite, FileLine
        WriteData "Movie Title:", "E", RowToWrite, FileLine
        WriteData "Movie Label ID:", "F", RowToWrite, FileLine
        WriteData "Movie Year:", "G", RowToWrite, FileLine
    Wend

    ' Close the file and inform the user that work is done
    Close #FileNum
    MsgBox "Done"

End Sub



Private Sub WriteData(Heading As String, ColumnName As String, ByRef RowToWrite As Integer, FileLine As String)

    ' If the line has the heading we asked for, then do some work
    If InStr(FileLine, Heading) > 0 Then

        ' Remember to go to the next line when Movie ID is found
        If Heading = "Movie ID:" Then
            RowToWrite = RowToWrite + 1
        End If

        ' Remove the heading from the line. Also remove semicolon
        Dim DataToWrite As String
        DataToWrite = Replace(Replace(FileLine, Heading, ""), ";", "")

        ' Movie Year can have paranthesis. Remove them
        If Heading = "Movie Year:" Then
            Range(ColumnName & RowToWrite) = Replace(Replace(DataToWrite, "(", ""), ")", "")
        Else
            Range(ColumnName & RowToWrite) = DataToWrite
        End If
    End If
End Sub

假设您的文件是 test.txt，这将处理数据并从第 2 行 A 列开始转储它。试一试。

结果是这个

请注意，您可能必须添加自己的标题，这很容易。

结果

推荐阅读