Writing Data in a Recordset to a .csv File

[Martin5260]

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

[netSurfer]

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.

    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.

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

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.