Grouping affiliated stores listed in EDI Flat File

Sonny Jim

Registered User.
Local time
Today, 06:43
Joined
Jan 24, 2007
Messages
98
Lured by the promise of data entry automation, my client, a 3rd party freight forwarder, has made an investment that involves receiving Carrier invoices via EDI. With EDI, everything comes in as a flat file, with only the carrier's name assigned an industry wide identification number called the SCAC Code ID. I do not believe the shippers nor the consignees are entered in uniformly with assigned I.D.'s amongst the carriers. I need to make a report that will group affiliated stores that are consignees. I do not know for certain that these carriers use relative databases, particularly if they receive EDI transmissions from others.

My first thought is to trim away spaces and punctuation (to minimize variations in naming conventions used) & then take the first 4 - 6 characters from what is left, & stripping away any trailing store numbers to create a common corporate name.

Does anyone have any better ideas on how to do this?
 
Lured by the promise of data entry automation, my client, a 3rd party freight forwarder, has made an investment that involves receiving Carrier invoices via EDI. With EDI, everything comes in as a flat file, with only the carrier's name assigned an industry wide identification number called the SCAC Code ID. I do not believe the shippers nor the consignees are entered in uniformly with assigned I.D.'s amongst the carriers. I need to make a report that will group affiliated stores that are consignees. I do not know for certain that these carriers use relative databases, particularly if they receive EDI transmissions from others.

My first thought is to trim away spaces and punctuation (to minimize variations in naming conventions used) & then take the first 4 - 6 characters from what is left, & stripping away any trailing store numbers to create a common corporate name.

Does anyone have any better ideas on how to do this?

Jim,

I think you need to talk to your client. How did they do this process before the EDI automated data entry? How do they distinguish the different players?

Before I dreamt up some new codification, I'd find out what exists, what used to exist and what issues have evolved/been resolved. Your comments regarding whether or not the carriers have electronic databases is a sign, to me, that many more questions need answers before a solution or approach is proposed. Just my $.02
 
If I understand the problem correctly you are now receiving data in electronic form, however there are inconsistencies, in that the same carrier, or company, can be referred to by different names.
For example in one row the carrier may be referred to as “Fed X” in another it may be “FX” in another it may be “Federal Express”.

ElectronicData.png

If that’s the case then I would recommend that you write a routine to scan through the data and correct any anomalies before transferring the data to your main table. You could import the flat file data into a temporary table and then run a routine against this table.

Basically you would examine one row of information at the time, look at the carrier name, if the name existed in a Variations Lookup Table provided for the purpose:

VariationsLookup.png

extract the code from this Table representing that carrier and place it in the new record:

GoodsRec.png


CarrierLookup.png

If the carrier name was new to the system, it could well be an existing carrier like “Federal Express” but someone may have used a different spelling or possibly made a typo, “F eX” “TX” something like that, if this occurred and then the import process would be stopped and the operator would be asked to provide a correction for this new anomaly.
 
EDI imports are new to my client and a new table structure is being created to accommodate it. My client currently uses a relational database to store Shipper & consignee data, so although EDI reduces the burden of data entry the new table structure being designed to meet the needs of the incoming EDI flat file is a regression.

Your suggestion to contact the IT departments represented by the carriers is a good one, though. I have only been dealing with the EDI contractor but it sounds like it would be a good time to go beyond them. Thanks for your advice jdraw!

Jim,

I think you need to talk to your client. How did they do this process before the EDI automated data entry? How do they distinguish the different players?

Before I dreamt up some new codification, I'd find out what exists, what used to exist and what issues have evolved/been resolved. Your comments regarding whether or not the carriers have electronic databases is a sign, to me, that many more questions need answers before a solution or approach is proposed. Just my $.02
 
Using a lookup table is a good idea. I had been thinking along those lines as I have used those a lot in Excel. I am not familiar with how to do it with Access but I figured there must be a way. I will have to look into that further. Thank you very much for your help!

If I understand the problem correctly you are now receiving data in electronic form, however there are inconsistencies, in that the same carrier, or company, can be referred to by different names.
For example in one row the carrier may be referred to as “Fed X” in another it may be “FX” in another it may be “Federal Express”.

ElectronicData.png

If that’s the case then I would recommend that you write a routine to scan through the data and correct any anomalies before transferring the data to your main table. You could import the flat file data into a temporary table and then run a routine against this table.

Basically you would examine one row of information at the time, look at the carrier name, if the name existed in a Variations Lookup Table provided for the purpose:

VariationsLookup.png

extract the code from this Table representing that carrier and place it in the new record:

GoodsRec.png

CarrierLookup.png

If the carrier name was new to the system, it could well be an existing carrier like “Federal Express” but someone may have used a different spelling or possibly made a typo, “F eX” “TX” something like that, if this occurred and then the import process would be stopped and the operator would be asked to provide a correction for this new anomaly.
 
surely you get a separate file from EACH supplier - EDI files just dont randomly appear - you have to collect them from an ftp site or something similar, dont you?

either these files are fixed width text format, or are csv files - either way, you just need an access template to split them into something that lets you get at individual fields. You must know what the file structure is.
 
A freight brokerage software company combines incoming EDI files sent by various carriers and then converts them to .csv files to be forwarded to us.

surely you get a separate file from EACH supplier - EDI files just dont randomly appear - you have to collect them from an ftp site or something similar, dont you?

either these files are fixed width text format, or are csv files - either way, you just need an access template to split them into something that lets you get at individual fields. You must know what the file structure is.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom