Export fixed fields with no spaces between fields

Topic

New Reply

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
TTL140011112000120100000012000000030000000110000000001240000000000000000000000000000000000000000

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

I

Reply | Quote

I believe I understand what the solution could be…

1. Import the AS400 file into one table, as one long text field. Do not attempt to manipulate the data at this point.
2. Create a separate query to isolate each type of record, using Left([RawData],3)=??? to establish the criteria to split up the main table.
3. 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.
4. 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.
5. 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.

Reply | Quote

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

Reply | Quote

David,
I use part of your suggested code. Thanks for your help.
Had a brainstorm while driving in the car.
(I do my best programming in my head then with a note pad handy.)

I solved the problem of exporting fixed fields for the Grouped Orders and Order Details.

The queries and parameters were all done with SQL statements.
All the bands were done only in VBA Code including Counts and Sums.

The Report Header has Company Information and Products (as subreport) sold that week all in VBA Code.
The Order ID header has Orders information in VBA Code.
The Order Details in the Detail Band is in VBA Code.
The Report Footer with the Counts and Sums is in VBA Code.

I wrote the code in a Text Editor and pasted into Access Report bands.
Paul

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Charlotte,
Thanks for your help.
Had a brainstorm while driving in the car.
(I do my best programming in my head then with a note pad handy.)

I solved the problem of exporting fixed fields for the Grouped Orders and Order Details.

The queries and parameters were all done with SQL statements.
All the bands were done only in VBA Code including Counts and Sums.

The Report Header has Company Information and Products (as subreport) sold that week all in VBA Code.
The Order ID header has Orders information in VBA Code.
The Order Details in the Detail Band is in VBA Code.
The Report Footer with the Counts and Sums is in VBA Code.

I wrote the code in a Text Editor and pasted into Access Report bands.
Paul

Reply | Quote

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

Reply | Quote

Can you build you data in to one single line?

For example your data was:-

ROI8253902082539020CUSTOMER NUMBER 1 720 ELM STREET PONTIAC MI000048341
PCH82539020825390202300126137 012300126137000000010
PCH82539020825390202300124133 012300124133000000009
PCH82539020825390202300114134 012300114134000000005

And now becomes:-

Row 1
ROI8253902082539020CUSTOMER NUMBER 1 720 ELM STREET PONTIAC MI000048341
PCH82539020825390202300126137 012300126137000000010

Row 2
ROI8253902082539020CUSTOMER NUMBER 1 720 ELM STREET PONTIAC MI000048341
PCH82539020825390202300124133 012300124133000000009

Row 3
ROI8253902082539020CUSTOMER NUMBER 1 720 ELM STREET PONTIAC MI000048341
PCH82539020825390202300114134 012300114134000000005

Note all the above are single rows.

Reply | Quote

That is a novel idea but can only have one ROI line for the Customer’s Order, then the multiple PCH lines for the order details following it. As it is a weekly report your novel idea would work for a few orders as could delete the duplicate ROI information for each order in a text editor. Then the second Order follows, etc.
As the report outputs puts spaces after the fields like on paper, I will try to concatenate the data into one text box for the ROI and for each PCH line in the group.
I might have to rewrite the invoicing program in Visual Basic to get the weekly report the way the trade group wants it. Or just do the report in Visual Basic. The data is transfered over the Internet.

Reply | Quote

Rupert,
Thanks for your help.
Had a brainstorm while driving in the car.
(I do my best programming in my head then with a note pad handy.)

I solved the problem of exporting fixed fields for the Grouped Orders and Order Details.

The queries and parameters were all done with SQL statements.
All the bands were done only in VBA Code including Counts and Sums.

The Report Header has Company Information and Products (as subreport) sold that week all in VBA Code.
The Order ID header has Orders information in VBA Code.
The Order Details in the Detail Band is in VBA Code.
The Report Footer with the Counts and Sums is in VBA Code.

I wrote the code in a Text Editor and pasted into Access Report bands.
Paul

Reply | Quote

The May 2001 issue of Smart Access has the following available online at Smart Access

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Douglas,
Thanks for your help.
Had a brainstorm while driving in the car.
(I do my best programming in my head then with a note pad handy.)

I solved the problem of exporting fixed fields for the Grouped Orders and Order Details.

The queries and parameters were all done with SQL statements.
All the bands were done only in VBA Code including Counts and Sums.

The Report Header has Company Information and Products (as subreport) sold that week all in VBA Code.
The Order ID header has Orders information in VBA Code.
The Order Details in the Detail Band is in VBA Code.
The Report Footer with the Counts and Sums is in VBA Code.

I wrote the code in a Text Editor and pasted into Access Report bands.
Paul

Reply | Quote

Thanks to all of you for your help.
Had a brainstorm while driving in the car.
(I do my best programming in my head then with a note pad handy.)

I solved the problem of exporting fixed fields for the Grouped Orders and Order Details.

The queries and parameters were all done with SQL statements.
All the bands were done only in VBA Code including Counts and Sums.

The Report Header has Company Information and Products (as subreport) sold that week all in VBA Code.
The Order ID header has Orders information in VBA Code.
The Order Details in the Detail Band is in VBA Code.
The Report Footer with the Counts and Sums is in VBA Code.

I wrote the code in a Text Editor and pasted into Access Report bands.
Paul

Reply | Quote