locked
Convert EDI to XML RRS feed

  • Question

  • Hi,

    I am trying to convert an EDI to XML so I can add the data to the MSSQL 2012. I have no idea where to start I have taken several courses on c# and asp.net with visual studio. I have found several articles on the matter but they are all old and out dated. I am new to the visual studio an c# world and would like any advice and or basic steps to get me started.

    Thank you in advance!

    Thursday, August 2, 2018 7:55 PM

All replies

  • It’s a profound question!<o:p></o:p>

    C# is a programming language and do not provide native functions to parse any EDI. You can obviously write code as ultimately EDI is just an text document. Also, may be online you may buy some EDI processing tools/API for C# as well.<o:p></o:p>

    You would probably know that EDI is a special format of a particular type of transaction. There are 1000s of different EDIs formats based on the document type and every document is different.<o:p></o:p>

    Any program, if it has to understand EDI, need to know that EDI document type first which you are interested in and its related processing rules. Segments, loops and restrictions etc.<o:p></o:p>

    Microsoft in its product BizTalk Server; has provided native capabilities to read and process EDIs. It ships EDI Schemas i.e. XSDs which can be used to parse EDIs to XML format.<o:p></o:p>

    Just be aware that BizTalk is not just an EDI processing tool whereas it’s much bigger tool for enterprise application integrations. Its cost is justified when you need much more than just EDI processing.<o:p></o:p>

    Your company may have BizTalk so you can leverage it and learn to process EDIs in BizTalk. There are many online video’s tutorials available. Nay be check YouTube. <o:p></o:p>

    If you don’t have BizTalk; and your nature of work is just doing some ad-hoc processing; you probably search on the internet and you may get some tools/APIs which can help you processing it. You need to search based on EDI Type like 837, 834 etc. Also, these are X12 format which is prevalent in USA.<o:p></o:p>

    You are in a forum where we discuss a BizTalk EDI specific questions. 

    Mark it as Answer OR Please Vote as Helpful by clicking the upward arrow mark <o:p></o:p>


    Friday, August 3, 2018 1:10 AM
  • What is your business requirements?
    What kind of EDI are we talking about

    If X12 as suggested there's open source parsers on eg Codeplex or GitHub

    Both X12 and Edifact are open specifications, when you come down to each element the handling is clearly specified
    When you get the hang of it the format is as easy as any other format used for interchanging business documents

    In the Enterprise and world of C# you have BizTalk and Azure Logic Apps as platforms

    /Peter


    When asking a question please be as thoroughly as possible this will make it easier to assist you http://www.catb.org/esr/faqs/smart-questions.html


    Friday, August 3, 2018 6:12 AM
  • Two very important questions to start:

    Will you be using BizTalk Server?  BizTalk Server Tutorials, Including EDI

    Can you use Microsoft Azure?  Logic Apps Enterprise Integration, Including EDI

    Friday, August 3, 2018 12:10 PM
  • Thank you for your reply!! The company I am working for does not have BizTalk. It is a small company and I was asked to do this as inexpensive as possible... The EDI type is 855 and 810. I need to either convert the info from the edi because one of my issues is there are multiple customers and they use different delimiters, most of them use * as a delimiter and \ as the header so I would * to start a new column and \ to begin a new row in the specific MSSQL 2012 server... here is a sample of one the EDI files

    some of the customers add more then one report per file so every time ISA is present in the file a new table would need to start.

    ISA*00*          *00*          *01*001903202      *12*8005901300     *180723*0622*U*00401*000076400*0*P*<\GS*PR*001903202*5859242176*20180723*0622*855001490*X*004010\ST*855*372127454\BAK*00*AD*223763/79164/98070*20180723****0364513606*20180723\PER*BD*DOUG\N1*SE*Graybar*92*RONY\N1*BT*O'CONNELL ELECTRIC\N3*830 PHILLIPS RD\N4*VICTOR*NY*145649417*US\N1*ST*O CONNELL ELECTRIC*92*0008037312\N3*830 PHILLIPS ROAD\N4*VICTOR*NY*145649417*US\PO1*000100*10*EA*1.59*PE*UI*78101121330*VC*B22SH-120GLV*VP*88035486*MF*COOPER B-LINE SYSTEMS\PID*F****SH CHNL 1 5/8 X 1 5/8-9/16 X 1 1/8 10FT\PO4*1*1*EA\ACK*IC*10*EA*017*20180723\PO1*000200*10*EA*0.5303*PE*UI*78101160424*VC*B2209PAZN*VP*88035509*MF*COOPER B-LINE SYSTEMS\PID*F****MULTIGRIP PIPE CLM PREASSEM 3/4IN\PO4*100*1*EA\ACK*IC*10*EA*017*20180723\PO1*000300*100*EA*0.6184*PE*UI*98001002002*VC*3/4-EMT*VP*88272942*MF*GENERIC VENDOR-TUBPA\PID*F****STEEL THINWALL CONDUIT\PO4*100*1*EA\ACK*IC*100*EA*017*20180723\PO1*000400*6*EA*0.15*PE*UI*78456410451*VC*451*VP*88073671*MF*COOPER CROUSE-HINDS DIVISION\PID*F****3/4 EMT SS CONN\PO4*1*1*EA\ACK*IC*6*EA*017*20180723\PO1*000500*24*EA*1.0095*PE*UI*78599170370*VC*R 648*VP*88254255*MF*THOMAS & BETTS CORP - ELECTRICAL\PID*F****STEEL THREADED ROD 1/2 X 6FT 13IN\PO4*100*1*EA\ACK*IC*24*EA*017*20180723\CTT*5\SE*32*372127454\GE*1*855001490\IEA*1*000076400\ISA*00*          *00*          *01*001903202      *12*8005901300     *180723*0625*U*00401*000076401*0*P*<\GS*PR*001903202*6154740500*20180723*0625*855002398*X*004010\ST*855*372124953\BAK*00*AD*551488BCR*20180723****0364514807*20180723\PER*BD*PEDRO ARRIBILLAGA\N1*SE*Graybar*92*BCMA\N1*BT*SULLIVAN & MCLAUGHLIN\N2*ACCOUNTS PAYABLE\N3*74 LAWLEY STREET\N4*BOSTON*MA*021223608*US\N1*ST*SULLIVAN & MCLAUGHLIN*92*0000182096\N2*ACCOUNTS PAYABLE\N3*74 LAWLEY STREET\N4*BOSTON*MA*021223608*US\PO1*000100*1*EA*13.32*PE*UI*78100212210*VC*SDS14814*VP*25085488*MF*L H DOTTIE CO\PID*F****ROTARY HAMMER DRILL BIT\PO4*1*1*EA\ACK*IC*1*EA*017*20180723\PO1*000200*10*EA*1.1*PE*UI*05400750655*VC*1700C-WHITE-3/4X66FT*VP*95011176*MF*3M CO. - ELECTRICAL MARKETS DIV.\PID*F****3/4X66FT VINYL CODE TAPEWHITE\PO4*1*1*EA\ACK*IC*10*EA*017*20180723\PO1*000300*2*EA*13.87*PE*UI*78100229621*VC*K6HX*VP*93159948*MF*L H DOTTIE CO\PID*F****#10 HX/PHL/SLOT SCRKIT\PO4*1*1*EA\ACK*IC*2*EA*017*20180723\PO1*000400*25*EA*1.3181*PE*UI*78618910403*VC*TP403*VP*99477183*MF*COOPER CROUSE-HINDS DIVISION\PID*F****4SQ 2-1/8D BOX 1/2 AND 3/4 KO\PO4*100*1*EA\ACK*IC*25*EA*017*20180723\PO1*000500*6*EA*0.0666*PE*UI*78174720101*VC*101-S*VP*88031045*MF*BRIDGEPORT FITTINGS INCORPORATED\PID*F****1/2 IN UL STEEL CONDUIT LOCKNUT\PO4*100*1*EA\ACK*IC*6*EA*017*20180723\PO1*000600*3*EA*1.3106*PE*UI*78174791520*VC*1520-DC*VP*88031332*MF*BRIDGEPORT FITTINGS INCORPORATED\PID*F****1/2 IN OFFSET NIPPLE\PO4*100*1*EA\ACK*IC*3*EA*017*20180723\PO1*000700*1*EA*28.59*PE*UI*78331022288*VC*711C*VP*88121384*MF*GREENLEE TEXTRON  INC.\PID*F****BUSHING BAG/100 711C\PO4*1*1*EA\ACK*IC*1*EA*017*20180723\PO1*000800*3*EA*9.191*PE*UI*78618907078*VC*TP7078*VP*97215421*MF*COOPER CROUSE-HINDS DIVISION\PID*F****1G WP BOX W/3 3/4 HUBS\PO4*100*1*EA\ACK*IC*3*EA*017*20180723\CTT*8\SE*46*372124953\GE*1*855002398\IEA*1*000076401\ISA*00*          *00*          *01*001903202      *12*8005901300     *180723*0636*U*00401*000076402*0*P*<\GS*PR*001903202*6154740500*20180723*0636*855002399*X*004010\ST*855*372118577\BAK*00*AD*551060NAV*20180723****0364514427*20180723\PER*BD*PEDRO\N1*SE*Graybar*92*BCMA\N1*BT*SULLIVAN & MCLAUGHLIN\N2*ACCOUNTS PAYABLE\N3*74 LAWLEY STREET\N4*BOSTON*MA*021223608*US\N1*ST*SULLIVAN & MCLAUGHLIN*92*0000182096\N2*ACCOUNTS PAYABLE\N3*74 LAWLEY STREET\N4*BOSTON*MA*021223608*US\PO1*000100*2*EA*4.31*PE*UI*78351052820*VC*F44GCPNK*VP*88132992*MF*HOFFMAN ENCLOSURES  INC\PID*F****WW TYPE 1 CLOSURE PLATE\PO4*1*1*EA\ACK*DR*2*EA*017*20180731\PO1*000200*1*EA*32.97*PE*UI*78351052520*VC*F44G90E*VP*88132985*MF*HOFFMAN ENCLOSURES  INC\PID*F****WW TYPE 1 ELBOW 90 DEG\PO4*1*1*EA\ACK*DR*1*EA*017*20180731\CTT*2\SE*22*372118577\GE*1*855002399\IEA*1*000076402\

    Here is another example of an EDI...

    ISA|00|          |00|          |01|047059191TP    |12|8005901300     |180614|1518|U|00400|001505056|0|T|>~GS|PR|047059191TP|6036273230|20180614|1518|1505056|X|004010~ST|855|1505056~BAK|00|AC|95314-194960|20180614||||1505056~N1|ST|WORCESTER BEHAVIORAL INN|92|112601~N3|100 CENTURY DRIVE|DAVE SMITH 860-614-2457~N4|WORCESTER|MA|~PO1|1|1.0|EA|98.7600||VP|30800-600L|BP|30800-600L~PID|F||||HOLE SAW KIT ELECTRICAN~ACK|IA|1|EA~CTT|1~SE|10|1505056~GE|1|1505056~IEA|1|001505056~

    I am unsure of where to start with this project... thank you again for your reply

    Friday, August 3, 2018 3:26 PM
  • Thank you for your reply!! My business requirements are to take the EDI files type 855 and 810, convert them a way where they all read the same... some of the delimiters are * and \ others are | and ~ then get them into the MSSQL server they are human readable and able to use the data in an online format where it can be searched. I found in my research that maybe the best way is to convert the files to XML before inserting the data into the MSSQL server... I have seen a few things in GitHub and codeplex but I couldn't find anything that was from the last couple of years.

    below are a couple of examples of the EDI files...

    ISA*00*          *00*          *01*001903202      *12*8005901300     *180723*0622*U*00401*000076400*0*P*<\GS*PR*001903202*5859242176*20180723*0622*855001490*X*004010\ST*855*372127454\BAK*00*AD*223763/79164/98070*20180723****0364513606*20180723\PER*BD*DOUG\N1*SE*Graybar*92*RONY\N1*BT*O'CONNELL ELECTRIC\N3*830 PHILLIPS RD\N4*VICTOR*NY*145649417*US\N1*ST*O CONNELL ELECTRIC*92*0008037312\N3*830 PHILLIPS ROAD\N4*VICTOR*NY*145649417*US\PO1*000100*10*EA*1.59*PE*UI*78101121330*VC*B22SH-120GLV*VP*88035486*MF*COOPER B-LINE SYSTEMS\PID*F****SH CHNL 1 5/8 X 1 5/8-9/16 X 1 1/8 10FT\PO4*1*1*EA\ACK*IC*10*EA*017*20180723\PO1*000200*10*EA*0.5303*PE*UI*78101160424*VC*B2209PAZN*VP*88035509*MF*COOPER B-LINE SYSTEMS\PID*F****MULTIGRIP PIPE CLM PREASSEM 3/4IN\PO4*100*1*EA\ACK*IC*10*EA*017*20180723\PO1*000300*100*EA*0.6184*PE*UI*98001002002*VC*3/4-EMT*VP*88272942*MF*GENERIC VENDOR-TUBPA\PID*F****STEEL THINWALL CONDUIT\PO4*100*1*EA\ACK*IC*100*EA*017*20180723\PO1*000400*6*EA*0.15*PE*UI*78456410451*VC*451*VP*88073671*MF*COOPER CROUSE-HINDS DIVISION\PID*F****3/4 EMT SS CONN\PO4*1*1*EA\ACK*IC*6*EA*017*20180723\PO1*000500*24*EA*1.0095*PE*UI*78599170370*VC*R 648*VP*88254255*MF*THOMAS & BETTS CORP - ELECTRICAL\PID*F****STEEL THREADED ROD 1/2 X 6FT 13IN\PO4*100*1*EA\ACK*IC*24*EA*017*20180723\CTT*5\SE*32*372127454\GE*1*855001490\IEA*1*000076400\ISA*00*          *00*          *01*001903202      *12*8005901300     *180723*0625*U*00401*000076401*0*P*<\GS*PR*001903202*6154740500*20180723*0625*855002398*X*004010\ST*855*372124953\BAK*00*AD*551488BCR*20180723****0364514807*20180723\PER*BD*PEDRO ARRIBILLAGA\N1*SE*Graybar*92*BCMA\N1*BT*SULLIVAN & MCLAUGHLIN\N2*ACCOUNTS PAYABLE\N3*74 LAWLEY STREET\N4*BOSTON*MA*021223608*US\N1*ST*SULLIVAN & MCLAUGHLIN*92*0000182096\N2*ACCOUNTS PAYABLE\N3*74 LAWLEY STREET\N4*BOSTON*MA*021223608*US\PO1*000100*1*EA*13.32*PE*UI*78100212210*VC*SDS14814*VP*25085488*MF*L H DOTTIE CO\PID*F****ROTARY HAMMER DRILL BIT\PO4*1*1*EA\ACK*IC*1*EA*017*20180723\PO1*000200*10*EA*1.1*PE*UI*05400750655*VC*1700C-WHITE-3/4X66FT*VP*95011176*MF*3M CO. - ELECTRICAL MARKETS DIV.\PID*F****3/4X66FT VINYL CODE TAPEWHITE\PO4*1*1*EA\ACK*IC*10*EA*017*20180723\PO1*000300*2*EA*13.87*PE*UI*78100229621*VC*K6HX*VP*93159948*MF*L H DOTTIE CO\PID*F****#10 HX/PHL/SLOT SCRKIT\PO4*1*1*EA\ACK*IC*2*EA*017*20180723\PO1*000400*25*EA*1.3181*PE*UI*78618910403*VC*TP403*VP*99477183*MF*COOPER CROUSE-HINDS DIVISION\PID*F****4SQ 2-1/8D BOX 1/2 AND 3/4 KO\PO4*100*1*EA\ACK*IC*25*EA*017*20180723\PO1*000500*6*EA*0.0666*PE*UI*78174720101*VC*101-S*VP*88031045*MF*BRIDGEPORT FITTINGS INCORPORATED\PID*F****1/2 IN UL STEEL CONDUIT LOCKNUT\PO4*100*1*EA\ACK*IC*6*EA*017*20180723\PO1*000600*3*EA*1.3106*PE*UI*78174791520*VC*1520-DC*VP*88031332*MF*BRIDGEPORT FITTINGS INCORPORATED\PID*F****1/2 IN OFFSET NIPPLE\PO4*100*1*EA\ACK*IC*3*EA*017*20180723\PO1*000700*1*EA*28.59*PE*UI*78331022288*VC*711C*VP*88121384*MF*GREENLEE TEXTRON  INC.\PID*F****BUSHING BAG/100 711C\PO4*1*1*EA\ACK*IC*1*EA*017*20180723\PO1*000800*3*EA*9.191*PE*UI*78618907078*VC*TP7078*VP*97215421*MF*COOPER CROUSE-HINDS DIVISION\PID*F****1G WP BOX W/3 3/4 HUBS\PO4*100*1*EA\ACK*IC*3*EA*017*20180723\CTT*8\SE*46*372124953\GE*1*855002398\IEA*1*000076401\ISA*00*          *00*          *01*001903202      *12*8005901300     *180723*0636*U*00401*000076402*0*P*<\GS*PR*001903202*6154740500*20180723*0636*855002399*X*004010\ST*855*372118577\BAK*00*AD*551060NAV*20180723****0364514427*20180723\PER*BD*PEDRO\N1*SE*Graybar*92*BCMA\N1*BT*SULLIVAN & MCLAUGHLIN\N2*ACCOUNTS PAYABLE\N3*74 LAWLEY STREET\N4*BOSTON*MA*021223608*US\N1*ST*SULLIVAN & MCLAUGHLIN*92*0000182096\N2*ACCOUNTS PAYABLE\N3*74 LAWLEY STREET\N4*BOSTON*MA*021223608*US\PO1*000100*2*EA*4.31*PE*UI*78351052820*VC*F44GCPNK*VP*88132992*MF*HOFFMAN ENCLOSURES  INC\PID*F****WW TYPE 1 CLOSURE PLATE\PO4*1*1*EA\ACK*DR*2*EA*017*20180731\PO1*000200*1*EA*32.97*PE*UI*78351052520*VC*F44G90E*VP*88132985*MF*HOFFMAN ENCLOSURES  INC\PID*F****WW TYPE 1 ELBOW 90 DEG\PO4*1*1*EA\ACK*DR*1*EA*017*20180731\CTT*2\SE*22*372118577\GE*1*855002399\IEA*1*000076402\

    Here is another example of an EDI...

    ISA|00|          |00|          |01|047059191TP    |12|8005901300     |180614|1518|U|00400|001505056|0|T|>~GS|PR|047059191TP|6036273230|20180614|1518|1505056|X|004010~ST|855|1505056~BAK|00|AC|95314-194960|20180614||||1505056~N1|ST|WORCESTER BEHAVIORAL INN|92|112601~N3|100 CENTURY DRIVE|DAVE SMITH 860-614-2457~N4|WORCESTER|MA|~PO1|1|1.0|EA|98.7600||VP|30800-600L|BP|30800-600L~PID|F||||HOLE SAW KIT ELECTRICAN~ACK|IA|1|EA~CTT|1~SE|10|1505056~GE|1|1505056~IEA|1|001505056~

    Friday, August 3, 2018 3:34 PM
  • I need to this as inexpensive as possible... are those open source? I can use whatever needed to complete the task... I was asked to use MSSQL 2012 express version but I have seen Azure... I am unsure of the BizTalk server.
    Friday, August 3, 2018 3:43 PM
  • This is the X12 format
    https://www.gxs.co.uk/wp-content/uploads/tutorial_ansi.pdf

    Have look at some the open source projects on github
    https://www.google.dk/search?num=20&ei=zHdkW-vbFaHBgAax3rioAQ&q=X12+parser+C%23+site%3Agithub.com

    If you are not fluent in .NET / C# this can be a daunting task

    /Peter


    When asking a question please be as thoroughly as possible this will make it easier to assist you http://www.catb.org/esr/faqs/smart-questions.html


    Friday, August 3, 2018 3:48 PM
  • Both BizTalk and Azure Logic Apps are licensed software from Microsoft

    You could use MSSQL Express but this will not make your life easier
    Make sure management are made well aware that using Express is not for critical business applications

    /Peter


    When asking a question please be as thoroughly as possible this will make it easier to assist you http://www.catb.org/esr/faqs/smart-questions.html

    Friday, August 3, 2018 3:53 PM
  • Hey, if your requirement is just to create a solution to dump all EDI data in SQL tables, I don't think it would be that difficult to do. As I said; its just a text file. You need to understanding the delimiters used in the EDI based on ISA and just read them line by line like any text file.

    Complexity lies in designing the data model based on the format. Now you can choose to go as specific as possible which is more correct I guess. Understand your EDIs 855 and 810 format what each segment mean so you can visualize it better. Parsing is easy part 

    Other way is you can just create few standard tables like for ISA, GS, GE, ST, SE and Common Parent Child table.

    You can choose a primary key using ISA InterchangeControl Numbers or some combination of control numbers.


    Friday, August 3, 2018 5:22 PM
  • Well....here's the deal.  There's a lot more to doing EDI correctly than initially appears.  And it's not just technical 'correctly' it's doing proper interactions with the Trading Partners as well.

    For example, in addition to receiving EDI, you'll likely also have to acknowledge in EDI as well.  Some Trading Partners will get annoyed if you don't return 997s.

    Business wise, keep in mind that "as inexpensive as possible" also implies "that works and is reliable".

    In stead of trying to code this yourself, which is...let's just say don't...have you considered an EDI service bureau?

    For example, liaison.com.  Depending on volume, it can be in the $10s per month and they handle all the EDI goop.

    Friday, August 3, 2018 5:46 PM
  • I was thinking the same thing... I have been able to get some data into the sql server with this code

        

    DECLARE     @SQL VARCHAR(8000)

    IF OBJECT_ID('TempDB..#BulkInsert') IS NOT NULL
    BEGIN
        DROP TABLE #BulkInsert
    END

    CREATE TABLE #BulkInsert
    (

    Line    VARCHAR(MAX)
    )

    SET @SQL = 'BULK INSERT #BulkInsert FROM ''c:\pathfile\name'' 
    WITH (ROWTERMINATOR = ''/'')'
    EXEC (@SQL)

    SELECT * FROM #BulkInsert

    but as i was unable to break it up into columns... and it won't work for all my files because they have different delimiters. So I will give this a go...  thank you


    Friday, August 3, 2018 7:27 PM
  • I will look into that thanks...
    Friday, August 3, 2018 7:28 PM
  • Row terminator is not always " ' " it varies; Just read something on EDI so you can understand a little bit more.

    The way you are approaching in SQL is just very simple and probably not useful, if what you are doing is the requirement then you may as well just use NotePad++  with possibly EDI Plugin

    https://gist.github.com/bhattisatish/6b5f5c90443a64cef192

    Saturday, August 4, 2018 6:43 AM
  • Please take a step back and get a clear picture of the business requirements

    Receiving X12 PO Acknowledegement and Invoice to store data raw in some tables does not make any sense at all
    Those documents need to be converted to whatever format your ERP system can handle and processed accordingly

    855 PO Ack should update purchasing with expected delivery dates, any schedules, items unable to deliver, new item identification etc

    810 Invoice should update finance with due payments, amount, alowances, charges, currency etc

    Take a fresh start, download EDI notepad from Liaison and check what the documents actually are and why you need to work together with purchasing and finance departments to get solutions which will support your business 

    I can't streess it enough: What you are doing know does not make sense

    EDI Notepad Express (free) from Liaison will help you to understand the content of X12 files
    https://www.liaison.com/products/integrate/edi-notepad/

    Get in touch with Liaison for an informal workshop and let them show you and your management what they can do to help you make your start with EDI a success

    hth /Peter


    When asking a question please be as thoroughly as possible this will make it easier to assist you http://www.catb.org/esr/faqs/smart-questions.html

    Saturday, August 4, 2018 8:14 AM
  • Also, it's a bit unusual you would receive an 855 without first sending an 850.
    Saturday, August 4, 2018 11:15 AM