Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Lakeland, Florida 33805-9595
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export fixed fields with no spaces between fields

    Export fixed fields to text with no spaces between fields for upload to mainframe. Should I use a third party report generator? Using Access 2000. Sample with leading zeros:
    DID14001111 20001120ANY CIGARETTE DISTRIBUTOR 1234 ANYPLACE DR DEARBORN MI000048126LAST_NAME FIRST_NAME 12345678901234567891
    UIR2300110136 012300110136WINSTON K/S 0200N 0000000190
    UIR2300111133 012300111133WINSTON LIGHT K/S 0200N 0000000185
    UIR2300112130 012300112130WINSTON BOX 0200N 0000000067
    UIR2300113137 012300113137WINSTON LIGHT 100 0200N 0000000109
    UIR2300114134 012300114134WINSTON 100 0200N 0000000195
    UIR2300117135 012300117135WINSTON ULTRA LIGHT 0200N 0000000180
    UIR2300118132 012300118132WINSTON ULTRA LIGHT 100 0200N 0000000162
    UIR2300119139 012300119139WINSTON LIGHT BOX 0200N 0000000180
    UIR2300120135 012300120135SALEM K/S 0200N 0000000191
    UIR2300122139 012300122139SALEM 100 0200N 0000000135
    UIR2300124133 012300124133SALEM LIGHT K/S 0200N 0000000120
    UIR2300125130 012300125130SALEM LIGHT 100 0200N 0000000118
    UIR2300126137 012300126137SALEM SLIM LIGHT 100 BOX 0200N 0000000212
    ROI8253902082539020CUSTOMER NUMBER 1 720 ELM STREET PONTIAC MI000048341
    PCH82539020825390202300126137 012300126137000000010
    PCH82539020825390202300124133 012300124133000000009
    PCH82539020825390202300114134 012300114134000000005
    ROI8356222583562225CUSTOMER NUMBER 2 26081 OAK STREET SOUTHFIELD MI000048034
    PCH83562225835622252300110136 012300110136000000008
    PCH83562225835622252300111133 012300111133000000015
    PCH83562225835622252300114134 012300114134000000020
    PCH83562225835622252300120135 012300120135000000021
    ROI8357473783574737CUSTOMER NUMBER 3 20041 LOCUST ST SOUTHFIELD MI000048076
    PCH83574737835747372300117135 012300117135000000005
    PCH83574737835747372300120135 012300120135000000010
    PCH83574737835747372300126137 012300126137000000007
    PCH83574737835747372300114134 012300114134000000014
    TTL14001111200012010000001200000003000000011000000 0001240000000000000000000000000000000000000000

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export fixed fields with no spaces between fields

    What question are you asking? How to do it, how to trim the fields, or what? What specifically is wrong with the sample data? What structure are you trying to export to?
    Charlotte

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    Lakeland, Florida 33805-9595
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    Charlotte: What question are you asking?
    Answer: Hot to export access report to fixed fields with no trailing spaces after each field.

    Charlotte: Specifically is wrong with the sample data?
    Answer: Sample data furnished by trade group has no trailing spaces between fields. Did not come from Access but from AS 400 system. When I export from dBASE as SDF it does not add trailing spaces.

    Charlotte: What structure to?
    Answer: ASCII Text file whee each field begins and ends at specific character location: Sample of data structure follows (The ROI is Orders and PCH is Order Details grouped):
    INPUT DISTRIBUTOR DATA FILE FORMAT

    COLUMNS LENGTH DESCRIPTION

    DID 1-3 3 Record Type
    4-11 8 Distributor ID#
    12-15 4 Filler
    16-23 8 Week and Date (YYYYMMDD)
    24-55 32 Distributor Name
    56-87 32 Distributor Address
    88-119 32 Distributor Address
    120-143 24 Distributor City
    144-145 2 Distributor State
    146-154 9 Distributor Zip+4
    155-174 20 Distributor Contact Last Name
    175-194 20 Distributor First Name
    195-204 10 Distributor Contact Phone #
    205-214 10 Distributor Contact Fax #

    UIR 1-3 3 Record Type
    4-13 10 UPC
    14-17 4 Filler
    18-29 12 Unique Item Code
    30-69 40 Distributor Item Name
    70-73 4 Sticks per Carton
    74 1 Promotion Identifier
    75-122 48 Promotion Description
    123-132 10 Inventory Quantity in Cartons

    ROI 1-3 3 Record Type
    4-11 8 Customer Number
    12-19 8 Customer Shipping Number
    20-51 32 Customer Name
    52-83 32 Customer Address
    84-115 32 Customer Address
    116-139 24 Customer City
    140-141 2 Customer State
    142-150 9 Customer Zip+4

    PCH 1-3 3 Record Type
    4-11 8 Customer Number (chain)
    12-19 8 Customer Shipping number
    20-29 10 UPC
    30-33 4 Filler
    34-45 12 Distributor Item (unique SKU)
    46-54 9 Net Quantity Shipped

    TTL 1-3 3 Record Type
    4-11 8 Distributor ID #
    12-19 8 Week End Date (YYYYMMDD)
    20-27 8 # of UIR Records
    28-35 8 # of ROI Records
    36-44 9 # of PCH Records
    45-56 12 Ttl # of Cartons over all PCH Records
    57-66 10 Not currently used
    67-76 10 Not currently used
    77-86 10 Not currently used
    87-96 10 Not currently used

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Lakeland, Florida 33805-9595
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    The DID is one record for the Distributor Information for report header..
    The UIR is products listing sold in one week as part of detail band..
    The ROI is Orders and PCH is Order Details grouped (chained)in Detail Band.
    The TTL is one record with counts and totals in Report Footer.

  5. #5
    Lounger
    Join Date
    Apr 2001
    Location
    Lakeland, Florida 33805-9595
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    INPUT DISTRIBUTOR DATA FILE FORMAT
    did not paste in columns, attached is TEXT file
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    I

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    The May 2001 issue of Smart Access has the following available online at <A target="_blank" HREF=http://www.smartaccessnewsletter.com/smartaccess/samag.nsf/PastIssueIndex!openform>Smart Access</A>

    You may find this useful.

    The ADO Stream Object Peter Vogel

    Following up his article on the latest of the ADO objects, Peter Vogel discusses the Stream object. This useful object mimics a sequential text file. (online subscribers only)

    Smart Access are offering 90 days free trial.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    IF I understand what you're getting at (as another respondent said, detailed explanations make answers easier), export your data as text. When the Export Text Wizard pops up, specify Fixed Width and then click the Advanced button on the same screen. Then, change all the Start and Width fields in the list to suit your needs. That will let you run your fixed width character fields together (I'm assuming that if you have a variable width character field you want to export it as some fixed maximum number of characters with trailing spaces to fit). You'll need to do that separately for each table.

    Now, if you are outputting numeric fields (fields defined as numbers, not character fields that just happen to contain digits) and you want leading zeros, it becomes more complicated.

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    I believe I understand what the solution could be...

    1. <LI>Import the AS400 file into one table, as one long text field. Do not attempt to manipulate the data at this point.

      <LI>Create a separate query to isolate each type of record, using Left([RawData],3)=??? to establish the criteria to split up the main table.

      <LI>For each query created above, create a second query that uses Left([field],#) and Mid([field],#,#) functions to parse each record into separate fields. For fields that will require leading zeros, a slight modification will be required. This can be done using Right("000000"&Mid([field],#,#),6), adjusted as necessary for length, or using spaces instead of zeros for text fields.

      <LI>Use the advanced features of the Export command to export as your desired text format, making sure to save the export specs for later use.

      <LI>Automate the entire process with a macro: import (or perhaps even ODBC link) the data in from the AS400; "Transfer Text" using the output file type needed, pulling from the appropriate query.
    It's kind of late, and I'm tired, so I apologize if some of the above isn't quite clear, or if I've muddled the commands a bit. The process does work though -- it is used regularly at the office to translate and upload data from a variety of sources to our AS400. I don't know what your required end format is, but given that you have multiple record types with differing data layouts, I suspect that having a different output file for each record type would make it easier for you in the end.

  10. #10
    Lounger
    Join Date
    Apr 2001
    Location
    Lakeland, Florida 33805-9595
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    David,
    I have printed out your response and will try it. The data exists at the distributor level in Access 2000 SR1 and I am only doing the export from Access. The text file would be sent over the Internet to the trade association

  11. #11
    Lounger
    Join Date
    Apr 2001
    Location
    Lakeland, Florida 33805-9595
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    Douglas,
    The Products can be export as a table but the OrderID/Order Details Group cannot. But I will try a Query for the OrderID and add a Subdatasheet of the Order Details and see if I get the Fixed Width option.
    Thanks.
    Paul

  12. #12
    Lounger
    Join Date
    Apr 2001
    Location
    Lakeland, Florida 33805-9595
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    I don't want to trim fields. Need full padded width of field to be exported with no space after the field. Need to upload to mainframe. Like dBase SDF export. But my report has Group of Orders followed by Order Details. I tried Crystal Reports and didn't do well. R&R Report Writer does better as it has feature that second field and so on can begin at end of previous field with no space between field. But R&R doesn't do grouping well.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export fixed fields with no spaces between fields

    I think we're getting confused by what you're saying. You talk about padding a field but not having spaces between fields. What are you padding the field with, and what do you mean by spaces between the field. No fixed width file has spaces "between" fields, although it may have spaces within a field. Whatever parses the file counts the characters, including the spaces, to find the beginning of the next field.

    When you export a report, you are NOT exporting just the data, you're exporting the whole thing, which is why you don't get the option for fixed width--it wouldn't make sense to export labels and blank lines and spaces to a fixed width file. What you have to do is export the recordsource for the report, which is a query or table, and that does give you the option of fixed width.
    Charlotte

  14. #14
    Lounger
    Join Date
    Apr 2001
    Location
    Lakeland, Florida 33805-9595
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    The problem is the Grouped Orders/Order Details for one week. One OrderId has to be followed by the Order Details then the Second OrderID, etc. There may be 50 orders in one week. (Chained) How do I export that to fixed fields? I have one Query that is a subreport that I could export separately and join in a text editor.

  15. #15
    Lounger
    Join Date
    Apr 2001
    Location
    Lakeland, Florida 33805-9595
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export fixed fields with no spaces between fields

    The ROI is the Order Information.
    The PCH lines are the Order Details:
    (How can I export from a Query as Fixed Fields as they are in Report with an Order Id Header and the Order Details are in the Detail Band.)
    ROI8253902082539020CUSTOMER NUMBER 1 720 ELM STREET PONTIAC MI000048341
    PCH82539020825390202300126137 012300126137000000010
    PCH82539020825390202300124133 012300124133000000009
    PCH82539020825390202300114134 012300114134000000005
    ROI8356222583562225CUSTOMER NUMBER 2 26081 OAK STREET SOUTHFIELD MI000048034
    PCH83562225835622252300110136 012300110136000000008
    PCH83562225835622252300111133 012300111133000000015
    PCH83562225835622252300114134 012300114134000000020
    PCH83562225835622252300120135 012300120135000000021

Page 1 of 2 12 LastLast

Posting Permissions

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