Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    Jacksonville, FL
    Posts
    14

    Red face Unanswered: Help Exporting to Flat File

    I need to export my data to a flat file. The file will be imported to a cobol application.. Because of this my data needs to be fixed length records. The fields need to be the same size as they are defined in the table. I also cannot use field delimeters. .Each record should be all the fields together as one record. .

    My question is which is a good way to approach this? ( i need help either way. .clueless here). . Should I try and create a report with all the fields i need and then export that to a flat file? Or a select statement of all the fields? How can I set this up?. . How can I export a report to flat file, or create a flat file from a select clause? Any help or links that help with this subject is appreciated.. . thanks!
    The early bird may get the worm. . .
    but it's the second mouse that gets the cheese!

  2. #2
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    read the fields and do some string activties to get it to the fixed length with a cobol filler character where needed, add all those fixed length strings to one string and print that one string to the file.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You may have to resort to a bit of VBA attached to a command button
    basic schema

    command_click
    <open file name> - suggest to a local file (use environ("USERPROFILE") & "\" & <filename>
    <open recordset>
    <while not recordset.eof>
    <format to correct size as required in the cobol>
    if you need towrite as a single file (eg card image) then build the strign using concatenation & check to make sure you append the corred number of characters / nulls etc...
    <write to file>
    <movenext>
    <endwhile>
    <close file>
    end sub
    Last edited by healdem; 11-23-04 at 07:49.

  4. #4
    Join Date
    Apr 2004
    Location
    Jacksonville, FL
    Posts
    14

    Talking

    First.. thanks for the suggestions. . I found another way of doing this without as much code. .. To create an export with fixed field/record lengths I used docmd.transfertext command.

    The following is what I did to accomplish this.. .

    1. created a query using the fields I needed for my record layouts.

    2. right-clicked on the query and did an export using fixed-record length(non delimited) options.. Under that area I clicked the advanced options and "SAVED" my qryExport specs. This saved the record layout internally.

    3. used the following command to perform the extracts (attached to cmd button):

    DoCmd.TransferText acExportFixed, "QryFtpExpSpecs", "qryFTP" _
    , "D:\BLG\exportflatfixed.txt"


    This created a fixed record length export to flat file. . where acEXPORTFIXED is the setting to do a Fixedlength extract, "QryFtpExpSpecs" is the name I saved my qryexport specs as.. "qryFTP" is the name of my query, followed by the path/name of the extract file. .
    The early bird may get the worm. . .
    but it's the second mouse that gets the cheese!

Posting Permissions

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