Results 1 to 8 of 8
  1. #1
    zooz is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2012
    Posts
    3

    Export Table->Txtfile + add custom Text in the resulting txt file

    Hi @ all!



    I have a question

    Id like to export a MS Acc 2007 Table into a txt file
    To have the right formatting, I created a certain custom Export Specification.

    But now I'd like to insert a custom text in the first line of my text file, followed by a blank space and then the name of the table.
    In my case it's

    ADD VALUELABELS "NameOfTable"

    One line below there should be the normal text specified by Export Specification

    But, into the LAST line, I would like to add custom text again, in my case:

    EXECUTE.


    Can you help me with some VBA Code or another solution to realize this?

    Thank you so much in advance, Z

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The export specification uses a query? Show the query sql statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    Here is a technique I have used in the past to add header information and footer information to an export:

    Method: Create a temporary table, load your custom text in the first row of the temporary table, insert records from your live table, insert last row with custom text

    1. Create a temporary table based on the table you are exporting. You can use a create table query to do this. If there is an autonumber field, change it in the temporary table to just an integer value. Or if you don't need it for your export you can just remove the field. The first field of the temporary table should be defined as a text field (regardless of what the actual data type is in the live table)
    2. In your temporary table create a sort order column (integer data type)
    3. Using VBA, insert one record into your temporary table. The sortOrder Colunn should have a value of 1. The first field in the table (regardless of what the field name is) should have the value = ADD VALUELABELS "NameofTable" text. All other fields leave blank.
    4. Load all the records from your table into the temporary table (using VBA Code). Pass a value of 2 into the sortOrder column for all of these records. (example: "INSERT INTO temptable (field1,field2,field3..., sortOrder) SELECT field1, field2, field3..., 2 FROM livetable")
    5. Insert one record into your temporary table passing sortORder a value of 3 (currentdb.execute("INSERT INTO temptable (field1, sortORder) VALUES ('EXECUTE', 3)")
    6. You will need to modify your export specification to export from the temporary table instead of your live table. You should sort by the sortOrder column in ascending order first.

    Here is some sample code:

    Sub ExportData
    'first clear your temporary table that you created with the make table query
    currentdb.execute("delete * from TEMPTABLE")

    'now insert first row
    currentdb.execute("insert into TEMPTABLE (Field1, SortOrder) VALUES ('ADD VALUELABELS " & chr(34) & "NameofTable" & chr(34) & "',1)")

    'insert records from live table
    currentdb.execute("INSERT INTO TEMPTABLE(Field1, Field2, Field3.....FieldX, SortOrder) SELECT Field1, Field2, Field3....FieldX, 2 FROM [NameOfTable]")

    'insert last row
    currentdb.execute("insert into TEMPTABLE (Field1, SortOrder) VALUES ('EXECUTE',3)")

    'run your export specification
    DoCmd.TransferText acExportDelim, "specificationName", "tablename", "filename"
    End Sub

    Hope this helps you!

  4. #4
    zooz is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2012
    Posts
    3
    Thank you so much for the code!
    But I have to reask a similar question again , because I ignored an important fact in my first question:

    In my main table, I have 3 variables for example, which are called "country1", "country2", "country3"
    For each of these variables i look up the values in a certain lookup table, which is called "country". In my example, it just contains 2 countries, USA and Canada.

    Now, my question is:
    Is there a program, which first checks the lookup-table "country" for the variables it serves as lookup table (in my example: country1, country2, country3).
    Then, it produces this text:

    ADD VALUE LABELS country1
    1 USA
    2 Canada
    .
    EXECUTE

    ADD VALUE LABELS country2
    1 USA
    2 Canada
    .
    EXECUTE

    ADD VALUE LABELS country3
    1 USA
    2 Canada
    .
    EXECUTE

    This output can be saved either in one txt file or 3 separate ones, it doesn't matter.
    The only thing important is that the output is automatically reproduced with each variable the table "country" serves as a lookup-table, as already shown above.

    Adding to this, it would be cool, if the program could ask to choose a certain table at the beginning.

    Thank you so much again, N.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Sounds like you could use DLookup function. Access Help has info on domain aggregate functions. Or Google the topic.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    Use DAO to get the values from your country table and use file system object to write to a text file: Here is some code:
    Sub textfile()
    Dim fs As Object
    Dim file As Object
    Dim sText As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set file = fs.CreateTextFile("C:\text.txt", True) 'NOTE - you may need to choose a file location with proper permissions if you are on Windows 7 or Vista.
    sText = "ADD VALUE LABELS Country1" + vbCrLf
    sText = sText & GetCountryList()
    file.writeLine sText
    file.writeLine

    sText = "ADD VALUE LABELS Country2" + vbCrLf
    sText = sText & GetCountryList()
    file.writeLine sText
    file.writeLine

    sText = "ADD VALUE LABELS Country3" + vbCrLf
    sText = sText & GetCountryList()
    file.writeLine sText
    file.writeLine
    file.Close
    End Sub

    Private Function GetCountryList() As String
    Dim rs As DAO.Recordset
    Dim sValues As String
    sValues = ""
    Set rs = CurrentDb.OpenRecordset("select * from Country")
    If Not rs.EOF Then
    Do Until rs.EOF
    sValues = sValues & rs("CountryID") & " "
    sValues = sValues & rs("Country") & vbCrLf
    rs.MoveNext
    Loop
    End If
    rs.Close
    Set rs = Nothing
    GetCountryList = sValues
    End Function

    In order to select a table to export you need to load the table names into it's own table. You can do this using DAO to loop through the table definitions and grab the table name (or you can just type the list manually if you don't have many tables). Create a new table called tblTableNames with one field and populate the table with the names of your tables. Add a combo box to the form you will use to choose a table and set the rowsource property of the combo box to select from tblTableNames.

  7. #7
    zooz is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2012
    Posts
    3
    Quote Originally Posted by AccessMSSQL View Post
    Use DAO to get the values from your country table and use file system object to write to a text file: Here is some code:
    Sub textfile()
    Dim fs As Object
    Dim file As Object
    Dim sText As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set file = fs.CreateTextFile("C:\text.txt", True) 'NOTE - you may need to choose a file location with proper permissions if you are on Windows 7 or Vista.
    sText = "ADD VALUE LABELS Country1" + vbCrLf
    sText = sText & GetCountryList()
    file.writeLine sText
    file.writeLine

    sText = "ADD VALUE LABELS Country2" + vbCrLf
    sText = sText & GetCountryList()
    file.writeLine sText
    file.writeLine

    sText = "ADD VALUE LABELS Country3" + vbCrLf
    sText = sText & GetCountryList()
    file.writeLine sText
    file.writeLine
    file.Close
    End Sub

    Private Function GetCountryList() As String
    Dim rs As DAO.Recordset
    Dim sValues As String
    sValues = ""
    Set rs = CurrentDb.OpenRecordset("select * from Country")
    If Not rs.EOF Then
    Do Until rs.EOF
    sValues = sValues & rs("CountryID") & " "
    sValues = sValues & rs("Country") & vbCrLf
    rs.MoveNext
    Loop
    End If
    rs.Close
    Set rs = Nothing
    GetCountryList = sValues
    End Function
    Hi AccessMSSQL,

    thank you! The output I get already looks good
    But there is one problem: The bold words above are custom text. Is it possible to have these values, (which represent related fields) inserted automatically and flexibly, dependent on the table I choose?

    For example: I have another table "hobbies", which serves as a lookup-table for 4 fields in my main table: "hobby1", "hobby2" "hobby3", "hobby4"
    The output text should be like this

    ADD VALUE LABELS hobby1
    1 reading
    2 skiing
    .
    EXECUTE

    ADD VALUE LABELS hobby2
    1 reading
    2 skiing
    .
    EXECUTE

    ADD VALUE LABELS hobby3
    1 reading
    2 skiing
    .
    EXECUTE

    ADD VALUE LABELS hobby4
    1 reading
    2 skiing
    .
    EXECUTE



    Shortly:
    The bold text should be flexible, defined by the name of the fields the table "hobbies" is related to. If the table "hobbies" is just related to the field "blah", the macro should produce the text:

    ADD VALUE LABELS blah
    1 reading
    2 skiing
    .
    EXECUTE

    and so on

    Thank you for your help again, I'm really thankful for the time you invested!

  8. #8
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    I knew that question was on it's way! You need the program to dynamically pull from the correct lookup table and automatically create the text for the joined fields. As long as there are foreign key relationships set up on the lookup tables for every field - that can be done. We could use VBA and the MySys tables to examine the relationships but a problem with this is you may have other child tables with relationships that aren't necessarily your lookup tables (the code would pick these up also). How many of these lookup tables and fields do you have and will they change much? If it's a small number we can actually just create a reference table with all the data we need. For example: TableName, FieldName, Lookup Table and then reference that table based on the table chosen to export. Another possibility is if the prefix of all of your field names equals the name of your lookup table we can also do that. For example if your fieldname is Hobby1 we would just assume a table exists called hobby and try to select from that table. But it looks like you have the table called "hobbies", not Hobby. If you can clarify more I'll give you some code.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Get Export File name from Unbound Text Box
    By FinChase in forum Import/Export Data
    Replies: 1
    Last Post: 01-11-2012, 11:09 AM
  2. How to update table if linked ot text file
    By cory_jackson in forum Import/Export Data
    Replies: 7
    Last Post: 01-03-2012, 03:19 PM
  3. trying to export data to a text file
    By itm in forum Access
    Replies: 1
    Last Post: 06-30-2011, 10:40 AM
  4. Replies: 1
    Last Post: 06-09-2011, 11:10 PM
  5. Export to Text File Using DoCmd Access 2007
    By tonere in forum Programming
    Replies: 1
    Last Post: 03-30-2011, 06:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums