HELP! How to do mixed length record export from Access ? (EDI Fixed Length Ascii or X.12)

T

tmb

We have a vendor who want's their orders from us to be submitted via FTP'ing
a file up to their server. (EDI)

They give us 2 choices... ASCII fixed length or X.12.

THE PROBLEM it seems to me is that each record has a different length... and
each record has a different number of fields.

For the ASCII Fixed Length option...
- The "Order Batch Header" record is 35 bytes long
- The "Order Header" record is 225 bytes long
- Etc, etc for additional records to comprise the order

For X.12 Same problem but the use an astrik "*" as a delimiter it seems.

I'm guessing that in either case each 'record' is terminated with a
'cr/lf''

And, they will be sending data back to us as well by the same methods
regarding shipping status, etc.

Can anyone give me any tips on how I may do this with Access?

thanks - tmb
 
R

Rick Brandt

tmb said:
We have a vendor who want's their orders from us to be submitted via
FTP'ing a file up to their server. (EDI)

They give us 2 choices... ASCII fixed length or X.12.

THE PROBLEM it seems to me is that each record has a different
length... and each record has a different number of fields.

For the ASCII Fixed Length option...
- The "Order Batch Header" record is 35 bytes long
- The "Order Header" record is 225 bytes long
- Etc, etc for additional records to comprise the order

For X.12 Same problem but the use an astrik "*" as a delimiter it
seems.

I'm guessing that in either case each 'record' is terminated with a
'cr/lf''

And, they will be sending data back to us as well by the same methods
regarding shipping status, etc.

Can anyone give me any tips on how I may do this with Access?

thanks - tmb

You can certainly use file i/o to create any file that they specify. Just
loop through some RecordSets and write the records into a text file. I
would however put the onus on them to supply *several* example files that
are in the format that they want. Your bigger problem will be in processing
such a mixed format file that they send back to you.

You would basically have a loop within a loop. The outer loop would
traverse the Recordset row by row and the inner loop would go through all of
the fields returned for each row. The Write or Print methods would be used
to write the data to the file.
 
N

Nikos Yannacopoulos

I'm afraid this will require some VBA coding to handle reading text
from/to file, and data from/to tables. If you're comfortable with VBA it
shouldn't be very hard, if you're not it probably looks horrendous...
That is, I don't know of any third party app to handle EDI messages, but
it wouldn't hurt to google it!

Nikos
 
T

tmb

While I did program years ago I have really never done VBA.

If need be I'll ask for help on that later... but how about this...

I could put all their "records" into a single Access Record.

I could insert 2 single byte fields at the end of each of their 'records'
and make them 10 (CR) and 13 (LF)

So my Access record would look like this...

Field 1 data
Field 2 data
Field 3 data
Field 4 CR
Field 5 LF
Field 6 data
Field 7 data
Field 8 CR
Field 9 LF

with the CR LF indicating an end to 'their' records.

Problem with this is when I attempt to export it as a fixed length Ascii
file I think the byte holding the decimal number 10 for a CR and the byte
holding the decimal number 13 for a LF will be converted to two bytes
each... an ascii 1 an ascii 0 for the CR and an ascii 1 and an ascii 3 for
the LF

Anybody knbow a workaround fo this w/o VBA?

thanks again for all the help.

tmb
 
N

Nikos Yannacopoulos

Well, I'm far from expert when it comes to EDI messages, but I think
some time ago I saw a couple that I could open in any text editor, so I
suppose they are just plain text files of a predefined format; so, if my
assumption is correct, you need not worry about CR abd LF at the end of
the line when writing a line in a text file, VBA will put it (them)
there for you anyway without any action on your part. At any rate, the
only way I see is by trial and error!
Have you got any sample files?

If anybody out there knows more on the subject of EDI messages, please
enlighten us, and correct me if I'm wrong!

Nikos
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top