'CSVReader: Option Public Option Explicit Class CSVReader %REM This is a convenience class for reading and parsing the contents of a comma-delimited file. An example of use is: Dim fileName As String Dim headerArray As Variant Dim dataArray As Variant Dim reader As CSVReader fileName = "C:\testdata.csv" Set reader = New CSVReader(fileName) '** for our example file, the first non-blank line is a set of '** column headers headerArray = reader.getNextLine Do Until reader.isEOF dataArray = reader.getNextLine If Not Isnull(dataArray) Then Print headerArray(0) & " = " & dataArray(0) End If Loop If you're reading a file that has other delimiter characters (a Chr(9) tab character is common), you can change the delimiter value prior to processing like this: Set reader = New CSVReader(fileName) reader.delim = Chr(9) version 1.1 Julian Robichaux -- http://www.nsftools.com August 9, 2004 %END REM Public delim As String Public quoteChar As String Public newLine As String Private csvFile As Integer Private lastLineText As String Public Sub New (csvFileName As String) csvFile = Freefile Open csvFileName For Input As csvFile '** these initial values are Public members, so they '** can be changed as necessary by the user (for '** example, you might have a tab-delimited file '** instead of a comma-delimited one) delim = |,| quoteChar = |"| newLine = Chr(13) & Chr(10) End Sub Public Sub close () '** you can either close the file explicitly when you're '** done with it, or it should happen automatically when '** the instance of this class gets destroyed On Error Resume Next If (csvFile > 0) Then Close csvFile csvFile = 0 End If End Sub Public Sub Delete () Me.close End Sub Public Function isEOF () As Integer '** returns True if we're at the end of the file isEOF = Eof(csvFile) End Function Private Function getNextNonEmptyLine () As String '** a private function to get the next line of text for processing by the '** getNextLine method. Blank lines are skipped, linefeeds that are '** embedded within quoted strings should be handled properly, '** and an empty string is returned if there's no more data Dim lineText As String Dim dataText As String Dim qcount As Integer Dim pos As Integer Do While Not Eof(csvFile) Line Input #csvFile, lineText dataText = dataText & lineText '** if there are an even number of quoteChars on this line, we can process it; '** otherwise, there's probably just a linefeed embedded in a quoted line, and '** we should skip the line processing and append the next line to this one '** (see the rules for using quotes in the DelimSplit function) qcount = 0 pos = Instr(1, dataText, quoteChar) Do While (pos > 0) qcount = qcount + 1 pos = Instr(pos + 1, dataText, quoteChar) Loop If (qcount Mod 2 = 1) And Not Eof(csvFile) Then '** quoted line with embedded linefeeds: we'll need to loop and append dataText = dataText & newline Elseif (Len(Trim(dataText)) > 0) Then '** if there's data in the buffer and the quote count is even (or we're at the '** end of the file), return it to the user Exit Do End If Loop getNextNonEmptyLine = dataText End Function Public Function getNextLine () As Variant '** get the next line of data, returned as an array of strings (Null is '** returned if there's no more data). For files that list the column '** header names at the top, you'll normally want to store the results '** of the first call to this method in reference array (or ignore it). lastLineText = getNextNonEmptyLine() If (Trim(lastLineText) = "") Then getNextLine = Null Else getNextLine = DelimSplit(lastLineText, delim, quoteChar) End If End Function Public Function getLineText () As String '** get the line of text that was most recently parsed by the getNextLine '** routine (useful for debugging) getLineText = lastLineText End Function Private Function delimSplit (fullString As String, delim As String, quoteChar As String) As Variant '** Split a string at the specified delimiters, adjusting for delimiters that are '** within quoted strings (you can use any quoteChar you want, but it's '** virtually always going to be the " character) '** '** RULES FOR USING / INTERPRETING QUOTES '** 1. If you want to encapsulate a data string in quotes, there should be '** no whitespace before or after the quoted string, unless that string '** is the first or last element on the line (in other words, there should '** be no spaces or tabs or characters between the delimiter characters '** that begin and end the string and the quotes that encapsulate it) '** 2. If you want to include a literal quote character within a string, you need '** to encapsulate the string in quotes (per #1 above) and use a pair of '** quote characters for every single quote character you wish to represent '** (for example, "" in a quoted string represents " ) '** 3. There should be no single instances of a quote character within a '** quoted string, unless you are ending the quoted string with that character '** 4. If you want to use the delimited character as a literal within a string, '** you must encapsulate the string in quotes (per #1 above) '** '** You could also do this sort of thing with the Input # statement, but that would '** require you to know the number and type of fields in each line in advance, '** and you could run into problems if one of the lines doesn't have the proper '** number of fields. '** '** This function uses the following ND6-specific functions: Split and Join '** (write your own equivalents if you need this for R5) Dim tempArray As Variant Dim quotedArray As Variant Dim returnArray As Variant Dim emptyArray(0) As String Dim count As Integer Dim i As Integer, j As Integer '** split the string along the delimiter '** if there is no quote char, or it doesn't appear in the string, we're done tempArray = Split(fullString, delim) If (Len(quoteChar) = 0) Or (Instr(fullString, quoteChar) = 0) Then delimSplit = tempArray Exit Function End If '** initialize the temporary arrays Redim finalArray(0) As String quotedArray = emptyArray '** start processing For i = 0 To Ubound(tempArray) quotedArray = Arrayappend(quotedArray, Split(tempArray(i), quoteChar)) If (Ubound(quotedArray) Mod 2 = 1) Or (i = Ubound(tempArray)) Then '** ignore any single quoteChars at the beginning or end of the string, '** and convert double quoteChars to single quoteChars For j = 2 To (Ubound(quotedArray) - 1) If (quotedArray(j) = "") And (quotedArray(j-1) <> quoteChar)Then quotedArray(j) = quoteChar End If Next '** add the string to the array that we'll return Redim Preserve finalArray(0 To count) finalArray(count) = Join(quotedArray, "") '** for some reason, items that only consist of quoteChar end up '** with an extra quoteChar in them If (Replace(finalArray(count), quoteChar, "") = "") Then finalArray(count) = Mid$(finalArray(count), 2) End If count = count + 1 quotedArray = emptyArray Else quotedArray = Arrayappend(quotedArray, delim) End If Next delimSplit = finalArray End Function End Class