Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2007
    Posts
    2

    Unanswered: Export column of data as row to a text file

    Can anyone give me some pointers or point me in the right direction for this? I need to read each column of data in a table and export it to a text file as a row of data. Basically, if I have 10 column of data, I would export it as 10 rows of data. Any help is greatly appreciated!
    Thank you,
    CP

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Possibly using a Cross Tab query. It depends on the data. Can you provide some? If not with a query then you will have to write some code to do it using a Recordset object.

  3. #3
    Join Date
    Sep 2007
    Posts
    2
    Thanks for the response, DCKunkle. I think I will have to write code using the Recordset object like you said. I am not that strong with writing to text files but I want to be able to open a text file, write data to a line, append more data to the line if needed, add end of line / new line, and then start again at next line, adding more data, eol, etc. Can anyone help get me going?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Cross-tab sounds easier for this problem...

    Anyhow - the real question is why do you want to translate your data in such a way?

    P.S. I think a search for textstream(s) in the help files will bring you up some information about manipulating text files.
    George
    Home | Blog

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    This will get you started - I think - This is code for the first field (column)
    Dim fso As New FileSystemObject
    Dim txtstr As TextStream
    dim db as database
    dim rst as recordset
    set db = currentdb
    dim myreturn
    myreturn = ""

    Strs = "Select * from mytable"
    set rst = db.openrecordset(strs)
    Set txtstr = fso.CreateTextFile("c:\" & "mytextfile.txt", True)
    rst.movefirst
    do until rst.eof
    myreturn = myreturn & " " & rst!field1
    rst.movenext
    loop
    With txtstr
    .WriteLine myreturn

    End With

    Set txtstr = Nothing
    Dale Houston, TX

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    you could

    also nest in the looping of rst!field2, and 3, 4, etc
    Dale Houston, TX

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I might be old school, but I tend to use the functions built into Access for file manipulation.

    Some of the functions I am referring to are:

    FreeFile - to get a pointer to the file.
    Open - to open a file
    Close - to close a file
    Print # - to print a line of text (includes a CRLF)
    Write # - similar to Print #
    Kill - delete a file


    Here is some sample code:

    Code:
        Dim intFileID As Integer
        Dim intTempFileID As Integer
        
        Dim strFilename As String
        Dim strTempFilename As String
        Dim strData As String
            
        PushCallStack "RemoveWFReorderHeaderTrailer"
        
        If gbooHandleErrors Then On Error GoTo ErrorHandler
        
        'Get the filename and path of the Wells Fargo Reorder file
        strFilename = COMPAFolderPath("COMPA Working") & LookupFilename(lngFileID)
        
        'Use a temporary file to copy the results into
        strTempFilename = GetPathFromFilename(strFilename) & "~~~~~~~~~~.txt"
        On Error Resume Next
        Kill strTempFilename
        If gbooHandleErrors Then On Error GoTo ErrorHandler
    
        'Start by opening the filename that will be read from
        intFileID = FreeFile(256)
        Open strFilename For Input As intFileID
            
        'Open the file that will be created
        intTempFileID = FreeFile(256)
        Open strTempFilename For Output As intTempFileID
        
        lngRecords = 0
        
        While Not EOF(intFileID)
            
            'strData = ReadLineCRLF(intFileID)
            Line Input #intFileID, strData
            
            If Left(strData, 3) = "DET" Then
            
                If Len(strData) <> 450 Then MsgBox Len(strData) & "    " & strData
                
                Print #intTempFileID, strData
                lngRecords = lngRecords + 1
                
            End If
            
        Wend
        
        Close #intFileID
        Close #intTempFileID
        
        'Delete the old file and rename the temporary file
        Kill strFilename
        Name strTempFilename As strFilename
    
        PopCallStack
        Exit Sub

  8. #8
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    I am old school as well

    but the filesystem object has been around a long time and is very effecient for creating and manipulating text files

    Bottom line is that these are two very good examples and we all should know how to exploit both methods

    good code btw
    Dale Houston, TX

Posting Permissions

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