Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    29

    Red face Unanswered: Exporting from Access to .CSV and having it append

    I'm trying to export a file from Access. I need it to be in .CSV format with quotations as text qualifier. The problem is I need the headers of the table to be export as well, but without the quotes. And unless Access or VBA can do this. Then I need to have two exports. One to just export the headers (which I can make as a table itself) and then the other to export the data and append it to the first export. Can anyone help?

    Thanks in advance!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    How are you exporting the data? Using the wizard/built-in capability?

  3. #3
    Join Date
    Oct 2003
    Posts
    29
    No, I'm using VBA to export the file.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by bailee220
    No, I'm using VBA to export the file.
    Well since you're doing it yourself, why not write out the header record? You have all the columns and titles at hand anyway ...

  5. #5
    Join Date
    Oct 2003
    Posts
    29
    What do you mean by write out the header record? I guess maybe I need to clarify my statement. I'm writing out the export through VBA command, but simulating the Macro exporting process. This is the statement I use:

    DoCmd.TransferText acExportDelim, "PrefCard Upload Export Specification", "Export File part " & loopcontrol, "C:\Surginet\Surginet Prefcard Upload part " & loopcontrol & ".csv", True

    The loopcontrol is a variable I'm needing to add to the file name for a number count.

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Modify this to meet you export specifications:



    Function WriteFile(strTable As String, strOutFile As String, strDelimiter As String, fHeader As Boolean) As Boolean
    ' Comments : Writes recordset to delimited file
    ' Parameters: strTable - name of the table or query
    ' strOutFile - path and name of file to write
    ' strDelimiter - Delimiting character
    ' fHeader - True to include header row
    ' Returns : True if successful, False if not
    '
    ' Example : ?WriteFile("TableOrQueryName","C:\Temp.csv",",",Tr ue)
    '
    Dim intOutFile As Integer
    Dim intCounter As Integer
    Dim intFieldCount As Integer
    Dim strText As String
    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database

    On Error GoTo PROC_ERR

    ' Get free file handle
    intOutFile = FreeFile

    ' Open the file in Output mode, write to it, and close it
    Open strOutFile For Output As intOutFile

    ' Set to current database
    Set dbs = CurrentDb()

    ' Open the recordset
    Set rst = dbs.OpenRecordset(strTable, dbOpenSnapshot)

    ' Get the field count
    intFieldCount = rst.Fields.Count

    With rst
    ' Append the header row
    If fHeader Then
    ' Loop through the field collection
    For intCounter = 0 To intFieldCount - 1
    strText = strText & .Fields(intCounter).Name & strDelimiter
    Next

    ' Trim off the last Delimiter
    strText = Left(strText, Len(strText) - 1)

    ' Write to the file
    Print #intOutFile, strText
    End If

    ' Loop through the recordset
    Do Until .EOF
    ' Clear our string
    strText = ""

    ' Loop through the field collection
    For intCounter = 0 To intFieldCount - 1
    ' Add quotes if the field type is text or memo
    If .Fields(intCounter).Type = dbMemo Or .Fields(intCounter).Type = dbText Then
    strText = strText & Chr(34) & .Fields(intCounter).Value & Chr(34) & strDelimiter
    Else
    strText = strText & .Fields(intCounter).Value & strDelimiter
    End If
    Next intCounter

    ' On to the next record
    .MoveNext

    ' Trim off the last Delimiter
    strText = Left(strText, Len(strText) - 1)

    ' Write to the file
    Print #intOutFile, strText

    Loop
    End With

    ' Clean up
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Close #intOutFile

    'Success
    WriteFile = True

    PROC_EXIT:
    Exit Function

    PROC_ERR:
    Close #intOutFile
    WriteFile = False
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume PROC_EXIT

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •