本文发表在 rolia.net 枫下论坛Processing Delimited Text Files

Delimited text files contain multiple fields in each line of text. Even if no serious programmer would ever use delimited text files as the primary means to store an application's data, these files nevertheless play an important role because they offer a great way to exchange data between different database formats. For example, delimited text files are often the only viable way to import and export data to mainframe databases. Here's the structure of a simple semicolon-delimited text file. (Note that it's customary for the first line of the file to hold the field's names.)

Name;Department;Salary
John Smith;Marketing;80000
Anne Lipton;Sales;75000
Robert Douglas;Administration;70000




Taken together, the Split and the Join functions are especially useful for importing and exporting delimited text files. For example, see how easy it is to import the contents of a semicolon-delimited data file into an array of arrays:

' The contents of a delimited text file as an array of strings arrays
' NOTE: requires the GetTextFileLines routine
Function ImportDelimitedFile(filename As String, _
Optional delimiter As String = vbTab) As Variant()
Dim lines() As String, i As Long
' Get all lines in the file.
lines() = Split(ReadTextFileContents(filename), vbCrLf)
' To quickly delete all empty lines, load them with a special char.
For i = 0 To UBound(lines)
If Len(lines(i)) = 0 Then lines(i) = vbNullChar
Next
' Then use the Filter function to delete these lines.
lines() = Filter(lines(), vbNullChar, False)
' Create a string array out of each line of text
' and store it in a Variant element.
ReDim values(0 To UBound(lines)) As Variant
For i = 0 To UBound(lines)
values(i) = Split(lines(i), delimiter)
Next
ImportDelimitedFile = values()
End Function

' An example of using the ImportDelimitedFile routine
Dim values() As Variant, i As Long
values() = ImportDelimitedFile("c:\datafile.txt", ";")
' Values(0)(n) is the name of the Nth field.
' Values(i)(n) is the value of the Nth field on the ith record.
' For example, see how you can increment employees' salaries by 20%.
For i = 1 to UBound(values)
values(i)(2) = values(i)(2) * 1.2
Next




Using an array of arrays is a particularly good strategy because it makes it easy to add new records:

' Add a new record.
ReDim Preserve values(0 To UBound(values) + 1) As Variant
values(UBound(values)) = Split("Roscoe Powell;Sales;80000", ";")




or delete existing ones:

' Delete the Nth record
For i = n To UBound(values) - 1
values(i) = values(i + 1)
Next
ReDim Preserve values(0 To UBound(values) _ 1) As Variant




Writing an array of string arrays back to a delimited file is also a simple task, thanks to this reusable routine that builds on the Join function:

' Write the contents of an array of string arrays to a delimited
' text file.
' NOTE: requires the WriteTextFileContents routine
Sub ExportDelimitedFile(values() As Variant, filename As String, _
Optional delimiter As String = vbTab)
Dim i As Long
' Rebuild the individual lines of text of the file.
ReDim lines(0 To UBound(values)) As String
For i = 0 To UBound(values)
lines(i) = Join(values(i), delimiter)
Next
' Create CRLFs among records, and write them.
WriteTextFileContents Join(lines, vbCrLf), filename
End Sub

' Write the modified data back to the delimited file.
ExportDelimitedFile values(), "C:\datafile.txt", ";"




All the routines described in this section rely on the assumption that the delimited text file is small enough to be held in memory. While this might sound like a serious limitation, in practice text files are mostly used to create small archives or to move small quantities of data between different database formats. If you find that you're having problems because of the size of the array, you need to read and write it in chunks using multiple Line Input # and Print # statements. In most cases, you can deal with files up to 1 or 2 megabytes in size (or even more, depending on how much RAM memory you have) without any problem.更多精彩文章及讨论，请光临枫下论坛 rolia.net