Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Post

    Hi, all! I have a little issue , I would like to combine multiple records from different tables that have different columns amount. Then, export this file without any field titles as a fixed width text file with all data in it? I tried using a union query but because every table has different amount of columns it doesnt run and doesnt display the data for me; any suggestions are appreciated...Have a great day!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could export each table to a separate text file, then combine the contents of the text files, for example by copying and pasting, or by using the good old COPY command in a command prompt window:

    COPY File1.txt + File2.txt + File3.txt > File4.txt

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Another approach is to create a query for each of the tables, and to select just the columns that you want to export to text. Then do a union query that selects each of the queries you created. That way they all have the same number of columns, though you do have to be careful to make sure your data types line up. In some cases you may want to create expressions if a table doesn't have enough rows.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795860' date='01-Oct-2009 16:50']You could export each table to a separate text file, then combine the contents of the text files, for example by copying and pasting, or by using the good old COPY command in a command prompt window:

    COPY File1.txt + File2.txt + File3.txt > File4.txt[/quote]

    Thank y'all for the help. What i ended up doing: I created a event procedure to execute the three file to be exported. I would like to add a command to do the copy of all individual exported text files into one doc from code..This is what I have:

    Private Sub Command0_Click()
    On Error GoTo Command0_Err

    DoCmd.RunSavedImportExport "Exporting-HDR"
    DoCmd.RunSavedImportExport "Exporting-LN"
    DoCmd.RunSavedImportExport "Exporting-LAB"
    MsgBox "All files have being properly exported"

    Command0_Exit:
    Exit Sub

    Command0_Err:
    MsgBox Error$
    Resume Command0_Exit

    End Sub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Assuming that you export to text files, you could use

    Code:
    Dim strFile1 As String
    Dim strFile2 As String
    Dim strFile3 As String
    Dim strFile4 As String
    strFile1 = "C:\Test\Export1.txt"
    strFile2 = "C:\Test\Export2.txt"
    strFile3 = "C:\Test\Export3.txt"
    strFile4 = "C:\Test\Export4.txt"
    Shell "cmd /c copy " & Chr(34) & strFile1 & Chr(34) & "+" & _
      Chr(34) & strFile2 & Chr(34) & "+" & _
      Chr(34) & strFile3 & Chr(34) & " > " & _
      Chr(34) & strFile4 & Chr(34)
    Substitute the correct paths and filenames.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795899' date='01-Oct-2009 19:53']Assuming that you export to text files, you could use

    Code:
    Dim strFile1 As String
    Dim strFile2 As String
    Dim strFile3 As String
    Dim strFile4 As String
    strFile1 = "C:\Test\Export1.txt"
    strFile2 = "C:\Test\Export2.txt"
    strFile3 = "C:\Test\Export3.txt"
    strFile4 = "C:\Test\Export4.txt"
    Shell "cmd /c copy " & Chr(34) & strFile1 & Chr(34) & "+" & _
      Chr(34) & strFile2 & Chr(34) & "+" & _
      Chr(34) & strFile3 & Chr(34) & " > " & _
      Chr(34) & strFile4 & Chr(34)
    Substitute the correct paths and filenames.[/quote]

    Thanks, HansV for all your help. it looks like the file is not copying the data from the other text files into the new file strFile 4; instead, it is only copying the location of the files inside strFile4..See below also how can I keep the strFile4 to be kept as a ASCII txt file

    C:\TEST\HDR.txt
    C:\TEST\LN.txt
    C:\TEST\LAB.txt
    1 file(s) copied.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yuk - my knowledge of MS-DOS commands has become rusty. It should have been

    Code:
    Shell "cmd /c copy " & Chr(34) & strFile1 & Chr(34) & "+" & _
      Chr(34) & strFile2 & Chr(34) & "+" & _
      Chr(34) & strFile3 & Chr(34) & " " & _
      Chr(34) & strFile4 & Chr(34)
    (No > before the destination)

  8. #8
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795921' date='01-Oct-2009 20:51']Yuk - my knowledge of MS-DOS commands has become rusty. It should have been

    Code:
    Shell "cmd /c copy " & Chr(34) & strFile1 & Chr(34) & "+" & _
      Chr(34) & strFile2 & Chr(34) & "+" & _
      Chr(34) & strFile3 & Chr(34) & " " & _
      Chr(34) & strFile4 & Chr(34)
    (No > before the destination)[/quote]

    It works!..you're still the best..by the way, what does this symbol "" means at the end of the data

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It's the end-of-file marker (ASCII character 26). The Copy command includes those in the end result because it simply appends the files after each other.

Posting Permissions

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