Results 1 to 3 of 3
  1. #1
    ngshivakumar is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2015
    Posts
    2

    Exporting to txt file

    Hi All,

    I am looking for a macro for exporting data from access database with below requirements.

    I got a table with below columns
    type origin record number name minutes date
    com ncrm 1234567890 test1 012 12032015
    sec ncrm 0987654321 test2 013 12032015
    sec ncrm 0987654321 test3 001 12032015


    Conditions of creating txt file are below.

    File name should be with below requirement.

    1) File name should start with "Type"_
    2) if first character of origin is "n" next charater of file name should be "CR"_
    3) Third character should be date and time when file is being created siffixed with _
    4) LAst character should be record number

    For example for first row file name should be as below.

    com_cr_20150702104521_1234567890

    Records in each txt file.


    1) Each row of records in seperate txt file with tab delimited
    2) If the record number is same on two rows then both rows data need to be included in single text file

    Forexample
    row 1 dat goes into first file
    and row 2 &3 data ges into next text fiel as record numebr for these two rows is same.

    Thanks,
    Shiva

  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    look at using the transfertext function which you can call from a macro.

    with regards your file naming requirements, I don't use macro's so can't advise, but in vba it would be something like

    filename=[Type] & "_" & iif(left([origin],1)="n","CR_","") & right([date],4) & mid([date],3,2) & left([date],2) & [minutes] & "_" & [record number]

    note that type, name and date are reserved words, if these are your real names I recommend changing them otherwise you will experience unexpected and misleading errors - putting square brackets round them helps but will not always solve the problem

    for the bit about what goes into each text file, you will need to create a recordset of the filename elements (e.g. SELECT DISTINCT [Type], origin, [record number] from myTable)

    then loop through this recordset to
    1. build a query string - something like "select * from myTable where [Type]='" & rsth![type] & "' AND origin='" & rsth!origin & "' and [record number]=" & rsth![record number]
    2. create and save a querydef with this sql string
    3. use transfertext to export this query

  3. #3
    ngshivakumar is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2015
    Posts
    2
    Thank you Mate




    Quote Originally Posted by Ajax View Post
    look at using the transfertext function which you can call from a macro.

    with regards your file naming requirements, I don't use macro's so can't advise, but in vba it would be something like

    filename=[Type] & "_" & iif(left([origin],1)="n","CR_","") & right([date],4) & mid([date],3,2) & left([date],2) & [minutes] & "_" & [record number]

    note that type, name and date are reserved words, if these are your real names I recommend changing them otherwise you will experience unexpected and misleading errors - putting square brackets round them helps but will not always solve the problem

    for the bit about what goes into each text file, you will need to create a recordset of the filename elements (e.g. SELECT DISTINCT [Type], origin, [record number] from myTable)

    then loop through this recordset to
    1. build a query string - something like "select * from myTable where [Type]='" & rsth![type] & "' AND origin='" & rsth!origin & "' and [record number]=" & rsth![record number]
    2. create and save a querydef with this sql string
    3. use transfertext to export this query

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

Similar Threads

  1. Exporting a query to Excel File
    By crowegreg in forum Import/Export Data
    Replies: 2
    Last Post: 08-08-2013, 05:25 PM
  2. Exporting to formatted Excel file
    By Xerin in forum Access
    Replies: 4
    Last Post: 10-21-2011, 03:33 PM
  3. Exporting Report to a word file or PDF or whatever
    By AccessDatabaseGuy in forum Access
    Replies: 1
    Last Post: 05-03-2011, 02:03 PM
  4. Exporting to Text File
    By blandow in forum Import/Export Data
    Replies: 2
    Last Post: 08-06-2010, 06:02 PM
  5. Exporting data to text file
    By NC_juggler in forum Import/Export Data
    Replies: 0
    Last Post: 11-21-2008, 10:51 AM

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