Results 1 to 11 of 11
  1. #1
    Join Date
    May 2012
    Posts
    89

    Unanswered: Export clean data from table to .txt file.

    Hi i want to export a table to a .txt file but when i do this i gets alle the labels and pipelines.
    I want the data to be export to an clean .txt file with out these label names and pipelines, and just the data that is in the table.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In a Standard module, create this procedure:
    Code:
    Sub ExportTable(ByVal TableName As String, ByVal FilePath As String, Optional ByVal Separator As String = ",")
    
        Const c_SQL As String = "SELECT * FROM [@T];"
        
        Dim rst As DAO.Recordset
        Dim strFileName As String
        Dim strSQL As String
        Dim strLine As String
        Dim lngCount As Long
        Dim intHandle As Integer
        Dim i As Long
        
        intHandle = FreeFile
        If Right(FilePath, 1) <> "\" Then FilePath = FilePath & "\"
        strFileName = FilePath & TableName & ".csv"
        Open strFileName For Output As intHandle
        strSQL = Replace(c_SQL, "@T", TableName)
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            lngCount = .Fields.Count - 1
            Do Until .EOF
                strLine = ""
                For i = 0 To lngCount
                    If Len(strLine) > 0 Then strLine = strLine & Separator
                    strLine = strLine & Nz(.Fields(i).Value, "")
                Next i
                Print #intHandle, strLine
                .MoveNext
            Loop
            .Close
        End With
        Close intHandle
        Set rst = Nothing
        
    End Sub
    To call it:
    Code:
    ExportTable "TableName","PathForTheExportedFile"
    Example:
    Code:
    ExportTable "Tbl_CF_DATA","U:\Access"
    Note: By default, the separator is a comma (,), to change it, use the third (optional) parameter:
    Code:
    ExportTable "Tbl_CF_DATA","U:\Access", ";"
    Have a nice day!

  3. #3
    Join Date
    May 2012
    Posts
    89
    Hi Sinndho.

    Thx for the reply.
    When i call the code:

    Private Sub Export_Click()
    ExportTable "tblIPadres", "C:\tblIPadres.txt"
    End Sub

    I get the error:

    Compile error:
    Expected variable or procedure, not module

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sounds like you named the module the same as the procedure, which you can't do.
    Paul

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Paul is right: The name of the module cannot be ExportTable (try Mod_ExportTable instead).

    Generally speaking, it's always best to compile a project immediately after inserting some external code into it. Should there be a syntax or compile problem, you'd be warned and the compiler would point out where and what the problem is.
    Have a nice day!

  6. #6
    Join Date
    May 2012
    Posts
    89
    I got a bit further.
    I dont know anything about external code, that will make it more complected but now im stuck at the 13e line:
    strSQL = Replace(c_SQL, "@T", tblIPadres)
    Constand expression required: c_SQL

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For some obscure reason, this happens sometimes when working with Access 2010 (and probably 2013 too). Usually, decompiling the project (from the command line):
    Code:
    "C:\Program Files\Microsoft Office\OFFICE14\msaccess.exe" "U:\MyProject.mdb" /decompile
    Then recompiling it solves the problem. Otherwise, you can try:
    Code:
    strSQL = Replace("SELECT * FROM [@T];", "@T", TableName)
    Or:
    Code:
    strSQL = "SELECT * FROM [" & TableName & "];")
    Have a nice day!

  8. #8
    Join Date
    May 2012
    Posts
    89
    The cod enow workes but there is an issue.
    When i run the: Exporttable "tblIPadres", "C:\tblIPadres.txt"
    I get the cocument "C:\tblIPadres.txt" but it is empty.
    When i run the: Exporttable "tblIPadres", "C:\tblIPadres.txt" again i get a runtime error 55: File already open.
    But this makes the document "C:\tblIPadres.txt" filed with the data that is in the table.

    When i run it when the app is decompiled i get the error: "You can't import, export, create, modify, or rename any forms, reports, pages or modules in een ACCDE, MDE or ADE database."

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're supposed to develop your application in a .mdb or .accdb database, not on an executable .mde or .accde one. I cannot figure out how you can even add code to a executable database.

    Try executing the code step by step and see what's written into the output file.
    Have a nice day!

  10. #10
    Join Date
    May 2012
    Posts
    89
    I created and run it in .accdb i just tried the compile to see if it makes a difference.

    The error is on the 13the line: "Open strFileName For Output As intHandle"

    When i run the: Exporttable "tblIPadres", "C:\tblIPadres.txt"
    I get the document "C:\tblIPadres.txt" but it is empty.

    When i run the: Exporttable "tblIPadres", "C:\tblIPadres.txt" again i get a runtime error 55: File already open.
    But this makes the document "C:\tblIPadres.txt" filled with the data that is in the table.
    --------------------------------------------------------------------------------------------------------------------
    Maybe is more convenient to know what i'm actually trying.
    I want to run an command-line (with the command prompt).

    The users need to input an ip-adres, i want to save the ip-adres in the database.
    Then i want to run the "nslookup" command-line and the "Findstr" command-line on the output to find the computer name.
    Then i want to import the computer name and save the computer name in the database.
    Then i want to run the "PSloggedon" command-line to find out who currently is logged on to the computer, and save this in the database.
    The total outcome needs to be that the user only need to input the ip-adres and then the computer-name and the username are all saved in the database in 1 record.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Rubberducksucker View Post
    When i run the: Exporttable "tblIPadres", "C:\tblIPadres.txt" again i get a runtime error 55: File already open.
    But this makes the document "C:\tblIPadres.txt" filled with the data that is in the table.
    You don't need to specify the full name of the file to export, only the folder where the file needs to be saved, so: Exporttable "tblIPadres", "C:\" and not Exporttable "tblIPadres", "C:\tblIPadres.txt"
    Quote Originally Posted by Rubberducksucker View Post
    I want to run an command-line (with the command prompt).
    This won't be as simple as that. You cannot convert an Access project into an executable (.exe). On the command line you'll need to start Access passing the full path to the .accdb file. In the project, you'll need to get back the parameter you passed and process them. For this you'll need a function that would be called from the AutoExec macro. This is far beyond the original request. You'd probably better trying to develop such a project in the .NET envoronment, (C# or VB.NET), where you can create an executable, or even in VB6, if you have that developent tool still available.
    Have a nice day!

Posting Permissions

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