Results 1 to 10 of 10

Thread: SQL question

  1. #1
    Join Date
    May 2004
    Posts
    159

    Unanswered: SQL question

    I have a program that exports data from a table to a comma deliminated text file. Now I need to add three more tables and export to text files in the same way but then combine them into one file separated by CR and LF's between each file's data section. I can make the separate files but am unsure of the code to combine them into one file. example file1 CRLF file2 CRLF file3 CRLF file4

    How would I do this as an sql statement or is there an easier way?
    Thanks for your help

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You can use a union query to join the tables together to create one virtual table that you can export as one file. Each table must be a similar structure
    Regards
    Justin

  3. #3
    Join Date
    May 2004
    Posts
    159
    A union query to combine 4 queries (off each table) would be very elegant.
    Plus it would be easy to insert into the current code.
    How would I add the CRLF's though? Can you give me an example?
    Thanks

  4. #4
    Join Date
    May 2004
    Posts
    159
    Would this work?
    DoCmd.TransferText acExportDelim, , "[qry1] & vblf &vbcr & [qry2] & vblf & vbcr & [qry3] & vblf & vbcr & [qry4]", strName, False

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Would this work?
    DoCmd.TransferText acExportDelim, , "[qry1] & vblf &vbcr & [qry2] & vblf & vbcr & [qry3] & vblf & vbcr & [qry4]", strName, False
    No, not if the required delimiter is CRLF

    Code:
    DoCmd.TransferText acExportDelim, , "[qry1] & vbcr & vblf & [qry2] & vbcr & vblf & [qry3] & vbcr & vblf &  [qry4]", strName, False
    might

    Code:
    DoCmd.TransferText acExportDelim, , "[qry1] & vbcrlf & [qry2] & vbcrlf & [qry3] & vbcrlf &  [qry4]", strName, False
    could

    however I do not expect your export to work. In an ideal world you should join the various columns to be exported into one query using a join or union or where clause

    so I'd suggest first off looking at the query designer and try to identify the common (linking) column, do a join on that/those columns

    add " & chr$(13) & chr$(10)" to the end of each column you want to have the CRLF appear. It may be " & chr$(10) & chr$(13)", I always get my ANSI codes wrong

  6. #6
    Join Date
    May 2004
    Posts
    159
    the do.cmd method does not seem to work as you predicted.
    I tried using the chr$10 and chr$13 but the problem that I was concerned about is that the field is exported as a line feed and carriage return but it is also comma deliminated. I am not sure if the extra commas are going to be a problem when importing data from this file.

  7. #7
    Join Date
    May 2004
    Posts
    159
    I can see that using a query on this problem will not work due to how a query will structure the data and will append the tables one row at a time. I need the completed files attached at the end of each table with a CRLF between.

    So it goes back to the question of a simple script to append them with CRLF in between.
    something like append (VBCRLF & (file2)) to file 1 then repeat for files 3 and 4
    I should be able to execue that as long as the complete path is in the name.
    I'm not an expert on SQL so I don't know if an append command even exists.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry you've lost me there
    you want to attach 3 files separated by a CRLF
    what are these files


    in th good old dos days I'd have mergeed the files using the copy command, it may even still work

    I think it was, but its as long time ago since I used it
    copy file1.ext+file2.ext newfile.ext
    copy newfile.ext+file3.ext newfile

  9. #9
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    When you export the union query to a text file as a CSV data you will get the CR LF at the end of each line by default. If you want some fine control over the export then you can use the following in Access.

    Scripting.FileSystemObject

    You will need to add a reference to the scripting module first in VBA. In the VBA editor go Tools, References and select Microsoft Scripting Runtime (the file name is C:\Windows\System32\scrrun.dll and is part of Windows). You will need to read the Help details on the Microsoft MSDN web site for the full syntax

    With this you can create a text file and export the data row by row formatting each row as you want. This will be a slow method if you have a large data set.
    Justin

  10. #10
    Join Date
    May 2004
    Posts
    159
    or maybe you could use this function to accomplish appending files


    ' Joins two or more files
    ' Call JoinFiles("File123.txt", vbNewLine, "File1.txt", "File2.txt", "File3.txt")
    Public Sub JoinFiles(sFileNew As String, sSeparator As String, _
    ParamArray sFiles() As Variant)

    Dim Index As Long
    Dim FileIn As Long
    Dim FileOut As Long
    Dim sBuf As String

    ' Delete output file if it exists
    On Error Resume Next
    Kill sFileNew

    ' Create the new output file
    FileOut = FreeFile
    Open sFileNew For Binary Access Write As #FileOut
    ' For each file in the sFiles array...
    For Index = LBound(sFiles) To UBound(sFiles)
    ' Open the input file
    FileIn = FreeFile
    Open sFiles(Index) For Binary Access Read As FileIn
    ' Create a string buffer to hold the entire file contents
    sBuf = Space(FileLen(sFiles(Index)))
    ' Read the contents of the file to the string buffer
    Get #FileIn, , sBuf
    ' Close the input file
    Close #FileIn
    ' Write the string buffer to the output file
    Put #FileOut, , sBuf
    ' If not the last file, write the separator string
    If Index < UBound(sFiles) Then
    ' Check if sSeparator is at end of file
    If Right$(sBuf, Len(sSeparator)) <> sSeparator Then
    sBuf = sBuf & sSeparator
    End If
    End If

    Next
    ' Close the output file
    Close #FileOut


    End Sub




    Thanks to
    Björn Holmgren
    Guide Konsult AB

Posting Permissions

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