IT Knowledge Base

01. 早前，終於從PDF檔案內容轉換成為文字(text)檔案，打開各檔案看一下，雖則都是用戶的帳單，但有些內容卻出現不同走位情況。

02. 下一步，就是如何利用Excel VBA，做到我想要的結果。

Sub textfile_to_excel()
' Define the source folder containing the text files.
Dim folder_path As String
Dim file_name As String
Dim row_num As Integer

' Define the target Excel sheet and search keywords.
Dim excel_sheet As String
Dim first_search As String
Dim second_search As String

' Define file handling variables in Excel.
Dim full_path As String
Dim file_num As Integer
Dim data_line As String

' Define working folder location.
folder_path = "C:\temp\PDF Folder\"

' Get all text files inside the working folder.
file_name = Dir(folder_path & "*.txt")

' Initialize the starting row in Excel.
row_num = 1

' Define target Excel worksheet.
excel_sheet = "Sheet1"

' Define search keywords.  They must match text exactly and appear in sequence.
first_search = "Monthly Mobile Service Fee"
second_search = "Monthly Incentive"

Do While file_name <> ""
full_path = folder_path + file_name
file_num = FreeFile()
Open full_path For Input As #file_num
Do While Not EOF(file_num)

' Read text file line by line.
Line Input #file_num, data_line

' Write the file name into first row of Excel worksheet.
Sheets(excel_sheet).Cells(row_num, 1).Value = file_name

' If first keyword is found, skip next 2 lines and capture the 3rd line.
If (data_line = first_search) Then
Line Input #file_num, data_line
Line Input #file_num, data_line
Sheets(excel_sheet).Cells(row_num, 2).Value = data_line
End If

' If second keyword is found, capture the next line.
If (data_line = second_search) Then
Line Input #file_num, data_line
Sheets(excel_sheet).Cells(row_num, 3).Value = data_line

'  Exit loop after collecting all required data for text file
Exit Do
End If

' Continue reading until end of file.
Loop

' Move to the next file.
file_name = Dir()

' Move to next row in Excel worksheet.
row_num = row_num + 1
Loop
End Sub

03. 結果因為PDF檔案內容轉換成為文字(text)檔案時，每個檔案結構並不是一致性，所以以上VBA程式只完成了80%工作，餘下的就只有人手去解決，不過已比全手動節省不少時間。