September 30, 2009 at 3:12 pm
Hello all,
I have to figure out a way to analyze the EDI (electronic data interchange) files concerning ASN (advance ship notices) from our vendors. I'm not really sure where to start. I can identify the lines I need by the first series of letters in each line. Here is a sample:
ISA~00~ ~00~ ~01~929222446 ~01~042653634G ~090923~1407~U~00401~000006502~0~P}
GS~SH~929222446~042653634~20090923~1407~6502~X~004010}
ST~856~65020001}
BSN~00~CS-0171895~20090923~14045500}
DTM~011~20090923}
HL~1~~S}
TD1~CNT~1~~~~G~100~LB}
TD5~~2~PKUP~L~PICK UP}
TD3~TL~~010458}
REF~VR~131456}
N1~SF~XXXXXXXX XXXXXX~92~131456}
N3~2000 SOUTH MAIN BLVD.}
N4~XXXXXXXX~XX~XXXX}
HL~2~1~I}
LIN~0001~BP~0601759~VP~77417}
SN1~~72~EA}
PRF~110051897}
PID~F~~~~AC20 BLU XXXXXXX .8M PLN-PLN 24}
CTT~2}
I have tried desparately to import this into some type of understandable structure with the Import Wizard to no avail. Sorry to pose such a general question but I'm just stuck.
Thank you,
Lonnie
September 30, 2009 at 10:08 pm
I doubt you can do it with the import wizard, maybe with SSIS ... the problem is that each line has different segments so it doesn't map readily to a single table. Then consider that the same 3 character identifier has different meanings based on it's relative position in the file.
What exactly are you attempting to do? Something like http://www.softshare.com/software/edinotepad/ might help you in terms of analysis.
October 1, 2009 at 1:32 am
Parsing the file in .NET (VB, C#) may be another solution - are there any specs about working with those files?
October 1, 2009 at 4:30 am
I work with a similar file structure with Banking systems. the way i do it is via SSIS. i import the file into a staging table which stores the whole row in one column (using flat file import - ragged right), hopefully you have a line feed at the end so the import can tell the start of a new line.
Then, knowing what each of the header characters mean on the rows you probably have a data definition document from the vender, so for example you know a row starting with ABC has the item id in chars 4-9. I just do a select statement using substring to dump the data into a table (seperate table for each line type) so that you have the data split into columns.
That way you will end up with multiple tables that hold your import information. At the start of my procedure i get a new importbatchid and add this to each table so that i can link them up.
Hope you can kind of understand this. if you want more info let me know.
John
October 1, 2009 at 5:56 am
Performance might be better if you parse your file in .NET according to the data definition document.
October 2, 2009 at 11:34 am
Few years back when i was working on SQL Server 2000, i had a client requirement to import EDI files into our database, i had then used ActiveX vbscript wihtin the DTS to parse and load the file.
Using SSIS, you now have 2 options:-
1) Use .NET script to parse and load the contents to a tables.
2) Use Script component within the Dataflow task to do parsing. Google on script component and you'll find plenty of resources.
Compare the performances of both the methods and choose the best one.
All the best
Regards,
Amol
Amol Naik
October 5, 2009 at 2:39 pm
Hello All,
I just wanted to thank everyone for their input to this point. I am able to import the data and query out the pieces I needed. I'm going to have to "hold" on the SSIS or .net parsing for now because to this point I've only received a few sample files. I need to get a larger single dump to determine what the input file structure will ultimatley be.
Thank you again....and please stay tuned.
Lonnie
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply