Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create text file from table content (A2K Sr-1)

    Hi,
    I need to create a text file (csv) from data in a table or query.
    The text file needs to have column names, (eg. "measure.assigned.custom.orderid", yes they NEED the dots between them) as the first entry and subsequent lines with double quotes around text and date entries, and no quotes around numeric data.

    sample output
    "measure.assigned.orderid","measure.assigned.descr iption","measure.assigned.date"
    1234,"description","12-Nov-2004"

    I have created a table which contains the column headings as content (one record only), which I export to a file using an export specification.
    I have created a table which contains the data, using the correct data types, which I also export using a different export specification.
    I then use the DOS copy command to 'stitch' together the above two created text files.

    I works, but I'm sure there is a more elegant and quicker way.

    I've looked at the Write # and Print # help file within Access, but can't seem to make the samples work.

    Any help gratefully appreciated.

    Michael

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create text file from table content (A2K Sr-1)

    See if you can use or adapt the code in the attached text file. It requires you to set a reference to the Microsoft DAO 3.6 Object Library (Tools | References... in the Visual Basic Editor)

    Instructions:
    - Set the Caption property of the fields in the data table to the name you want in the first line of the exported file (measure.assigned.orderid etc.)
    - Call the procedure like this, in the Immediate window, or in the On Click event procedure of a command button on a form:

    TestExport "MyTable", "C:AccessMyExport.csv"
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create text file from table content (A2K Sr-1)

    Thanks Hans,
    I needed to have all columns individually delimited with double quotes
    as well as all text and date data fields.
    My mod was to include chr(34) at each end.
    strExport = strExport & "," & Chr(34) & rst.Fields(i).Value & Chr(34)

    Thanks again

Posting Permissions

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