Writing Data in a Recordset to a .csv File

Hi there!

I am trying to turn data from a recordset into a comma delimited file.

So I have data that looks like this: rs("FirstName")
and I need to write it to a .csv file.

How does one write data to a separate file? Ultimately, someone else will look at that file in Notepad.

Thank you, I appreciate your help!!

Andrea :)

Here is a code I use to go through a table, grab the filed names, write those to the first line of a CSV file, then it goes through and writes all the records as well. dbsData is set to my database.

Code:

---

    Set rcdTable = dbsData.OpenRecordset("select * from Table")
    intFieldCount = rcdTable.Fields.Count
    ReDim strFieldNames(intFieldCount - 1)
    For intJ = 0 To intFieldCount - 1
        strFieldNames(intJ) = rcdTable.Fields(intJ).Name
    Next intJ

    intFreeFile = FreeFile      'sets to next free file number
    strCSVFile = "table.csv"
    Open strCSVFile For Output As #intFreeFile
    strTemp = ""
    For intJ = 0 To intFieldCount - 1
        If strTemp <> "" Then
            If spaceCheck(nonulls(strFieldNames(intJ))) = True Then  'need to put it in quotes
                strTemp = strTemp & "," & Chr(34) & nonulls(strFieldNames(intJ)) & Chr(34)
            Else
                strTemp = strTemp & "," & nonulls(strFieldNames(intJ))
            End If
        Else
            If spaceCheck(nonulls(strFieldNames(intJ))) = True Then  'need to put it in quotes
                strTemp = Chr(34) & nonulls(strFieldNames(intJ)) & Chr(34)
            Else
                strTemp = nonulls(strFieldNames(intJ))
            End If
        End If
    Next intJ
    Print #intFreeFile, strTemp
    'now do the data
    Do Until rcdTable.EOF  'now cycle through the dataset and write all the data for this table
        'need to do check for a space in the string and put it in quotes
        strTemp = ""
        For intJ = 0 To intFieldCount - 1
            If strTemp <> "" Then
                If spaceCheck(nonulls(rcdTable(intJ))) = True Then  'need to put it in quotes
                    strTemp = strTemp & "," & Chr(34) & nonulls(rcdTable(intJ)) & Chr(34)
                Else
                    strTemp = strTemp & "," & nonulls(rcdTable(intJ))
                End If
            Else
                If spaceCheck(nonulls(rcdTable(intJ))) = True Then  'need to put it in quotes
                    strTemp = Chr(34) & nonulls(rcdTable(intJ)) & Chr(34)
                Else
                    strTemp = nonulls(rcdTable(intJ))
                End If
            End If
        Next intJ
        Print #intFreeFile, strTemp
        rcdTable.MoveNext
    Loop
    Close #intFreeFile
    rcdTable.Close

---

The spaceCheck and nonulls are 2 function that I use. The nonulls makes sure that it's not a null value because it messes things up. The spaceCheck checks to see if there is a space in the value because if there is it has to put " around that value.

Code:

---

Function nonulls(s As Variant) As String
'          - string = NoNulls(string)
'                Checks to see if the string passed is null, if so then return "" else return
'                the passed string.  A wrapper for access stuff.
If IsNull(s) Or Len(s) = 0 Then
  'nonulls = ""  changing this to a space, the blank isn't what I need
  nonulls = ""
Else
  nonulls = s
End If
End Function

---

Code:

---

Function spaceCheck(strString As String) As Boolean
'will take the input string and check it for a space, if one exists, send back a true value
Dim intI As Integer

For intI = 1 To Len(strString)  'do loop according to length of string
    If Asc(Mid(strString, intI, 1)) = 32 Then  'equals space
        spaceCheck = True
        Exit Function
    End If
Next intI
spaceCheck = False

End Function

---

It's a little more than you said you needed to do but it illusrates how to write to a csv file. If you have any questions, just let me know.