Help with .csv extract with first row containing static information

[CindyR19]

Hi,

I have to export the results of a query into .csv, but the first row must only contain header information(Static), such as run time, run date, Client ID.. How would I go about doing that?

Example of desired output.

10 Client Code 20110627 180617 9444 <~~~ Static info.
20 12323123 20110627 N 132132132 <~~~ Data from query export
20 12323123 20110627 N 132132133

Thanks in advance for your help.

[boblarson]

Put this function into a standard module and then save the module as a different name than the function.

Function ExportText(TableOrQuery As String, FileAndPath As String, Optional HeaderInfo As String, Optional ExportSpec As String)
    Dim i      As Integer
    Dim j      As Integer
    Dim strHold As String
    Dim strFile As String
    Dim strTempFile As String
    Dim strFileOut As String
 
    ' sets a temporary file
    strTempFile = CurrentProject.Path & "\temptext.csv"
 
    ' checks to see if there is an export specification
    If Len(ExportSpec) > 0 Then
        DoCmd.TransferText acExportDelim, ExportSpec, TableOrQuery, FileAndPath, False
    Else
        DoCmd.TransferText acExportDelim, , TableOrQuery, FileAndPath, False
    End If
 
    ' gets the available file number
    i = FreeFile
 
    ' opens the exported file to use for the temp file
    Open FileAndPath For Input As #i
 
    ' gets the available file number
    j = FreeFile
 
    ' opens the temp file to start adding the header info and rest of the exported text file
    Open strTempFile For Output As #j
 
    ' if there is header info passed then it adds that
    If Len(HeaderInfo) > 0 Then
        strHold = HeaderInfo
        Print #j, strHold
    End If
 
    ' loops through the previously exported text file to get the values and put them in
    ' to the temp file
    Do Until EOF(i)
        Line Input #i, strHold
        Print #j, strHold
    Loop
 
    Close #i
    Close #j
 
    ' checks to see if the file exists and, if so, it deletes it
    If Dir(FileAndPath) <> vbNullString Then
        Kill FileAndPath
    End If
 
    ' moves the temp file to the original text file name and path
    Name strTempFile As FileAndPath
 
 
End Function

And then you can call it like this:

ExportText "TableOrQueryNameHere", "C:\Temp\YourFileNameToSaveAsHere.csv", "10,12323123," & Format(Date(), "yyyymmdd")

But if you want it tab delimited then you would need to set up an export specification for that table or query (see here for how) and then you would call that by name and your static info would also need to be passed slighly differently. So an example is:

ExportText "TableOrQueryNameHere", "C:\Temp\YourFileNameToSaveAsHere.csv", "10" & Chr(9) & "12323123" & Chr(9) & Format(Date(), "yyyymmdd"), "ExportSpecNameHereInQuotes"

[CindyR19]

Thank you so very much for your detailed explanation. I have two other questions related to this export if I may,

1. in the last example you have this "ExportSpecNameHereInQuotes" at the end of your code. What specifically do I have to put there?

2. Is there a way to add static information such as "99" and a sum of the records at the end of the file?

Thanks again.

[boblarson]

1. Did you create an Export specification in the database? If so, you would use the name you chose to save that export specification. Did you look at the link I gave you which shows you how you create an export specification? That shows you how to create and name it. Then you use the name inside double quotes like I showed.

2. Put the static info between these two lines of code in the above function.

    Loop
 
    Close #i

[CindyR19]

Thanks again. So I created the Export Specifications and here is my code..

ExportText "TestExtract_CM", "C:\Documents and Settings\martinc\Desktop\TestingExport.csv", "10" & Chr(9) & "12323123" & Chr(9) & Format(Date, "yyyymmdd"), "TestExportSpec"

The header info is not being tabbed apart. It all appears in A1.

[boblarson]

Okay, mabybe this one will work for you. I've modified the handling of the header parameter so send it without the Chr(9) and just as a comma delimited string.

So replace your function I gave you with this one:

Function ExportText(TableOrQuery As String, FileAndPath As String, Optional HeaderInfo As String, Optional ExportSpec As String)
    Dim i      As Integer
    Dim j      As Integer
    Dim strHold As String
    Dim strFile As String
    Dim strTempFile As String
    Dim strFileOut As String
    Dim varSplit As Variant
    Dim intCount As Integer

    ' sets a temporary file
    strTempFile = CurrentProject.Path & "\temptext.csv"
 
    ' checks to see if there is an export specification
    If Len(ExportSpec) > 0 Then
        DoCmd.TransferText acExportDelim, ExportSpec, TableOrQuery, FileAndPath, False
    Else
        DoCmd.TransferText acExportDelim, , TableOrQuery, FileAndPath, False
    End If
 
    ' gets the available file number
    i = FreeFile
 
    ' opens the exported file to use for the temp file
    Open FileAndPath For Input As #i
 
    ' gets the available file number
    j = FreeFile
 
    ' opens the temp file to start adding the header info and rest of the exported text file
    Open strTempFile For Output As #j
 
    ' if there is header info passed then it adds that
    If Len(HeaderInfo) > 0 Then
        varSplit = Split(HeaderInfo, ",", , vbTextCompare)
        For intCount = 0 To UBound(varSplit)
            strHold = strHold & varSplit(intCount) & Chr(9)
        Next
        strHold = HeaderInfo
        Print #j, strHold
    End If
 
    ' loops through the previously exported text file to get the values and put them in
    ' to the temp file
    Do Until EOF(i)
        Line Input #i, strHold
        Print #j, strHold
    Loop
 
    Close #i
    Close #j
 
    ' checks to see if the file exists and, if so, it deletes it
    If Dir(FileAndPath) <> vbNullString Then
        Kill FileAndPath
    End If
 
    ' moves the temp file to the original text file name and path
    Name strTempFile As FileAndPath

End Function

Then you just call it like:
ExportText "TestExtract_CM", "C:\Documents and Settings\martinc\Desktop\TestingExport.csv", "10,12323123" & Format(Date, "yyyymmdd"), "TestExportSpec"

[CindyR19]

I did as you said and the Header now has two fields populated. The "10" is in A1 and the rest of the info is in B1??? Also if you could show me where I would insert the code to add a footer to the .csv, it would be most appreciated. The footer should have two fields populated the first with "99" and the second with a count of records from the detail with leading zeros. It must be 10 characters long.

Thank you so very much for all of your help. You are a patient man.

[CindyR19]

Hi Bob.. I have figured out how to fix the first issue. Now I just need help with the footer information . Thanks again.

[boblarson]

Paste this function into the same module as your other code:

Function GetFooterInfo(strTQ As String) As String
    GetFooterInfo = Format(DCount("*", strTQ), "0000000000")
End Function

And then add this exactly as written:

strHold = "99" & Chr(9) & GetFooterInfo(TableOrQuery)
Print #j, strHold

Where I show in red here:

    ' loops through the previously exported text file to get the values and put them in
    ' to the temp file
    Do Until EOF(i)
        Line Input #i, strHold
        Print #j, strHold
    Loop
 
    strHold = "99" & Chr(9) & GetFooterInfo(TableOrQuery)
    Print #j, strHold

    Close #i
    Close #j

[CindyR19]

Thank you so much. You are my hero.

[boblarson]

Thank you so much. You are my hero.

Glad we could be of help.