Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Open EDI file in SQL Server Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 5:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 35,609, Visits: 32,200
Heh... the neat thing about EDI is that it has so very many standards.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1442540
Posted Wednesday, August 6, 2014 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 10:20 AM
Points: 7, Visits: 106
We have available both the XML document mappings for the EDI 4010 and 5010 and 6020 formats, and SQL Server table structures
including over 1,000 SQL Server table data element DDLs for each EDI format type: including 835, 837, 271, etc. as well
as over 60,000 lines of stored procedures to parse, validate and process them plus a sample SSIS package as well. But as you can imagine much time and effort has gone into this development process. The entire source code package costs $7,500.00 USD but if you only need portions it can be reduced to cover only those components you require. Contact: jfbevilaqua@gmail.com
Post #1600282
Posted Thursday, August 7, 2014 11:14 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, November 22, 2014 7:46 PM
Points: 3,220, Visits: 2,361
My advice is to contact the source provider of the EDI file - plain and simple.



Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #1600780
Posted Wednesday, August 13, 2014 1:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:53 PM
Points: 891, Visits: 5,705
What is the source of your EDI data? Whoever supplies the data should have the format for you. I think there are folks that write EDI parsers too. CozyRoc http://www.cozyroc.com/ssis/edi-source does some EDI ones...

It might be worth it to buy one...
Post #1602596
Posted Monday, August 25, 2014 12:33 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 1,379, Visits: 510
The data seems to be in EDI X12 format. Maybe here you will find more details about it.
Post #1607179
Posted Tuesday, August 26, 2014 6:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:36 PM
Points: 378, Visits: 2,615
Go read the wikipedia article on EDI.

http://en.wikipedia.org/wiki/Electronic_data_interchange

This will give you the key words to use when asking for information. Its sort of like a csv file, sure you can split the columns, but then you are left with anonymous values that may be data, but without knowing what the values describe, they're certainly not information (except maybe information about what column # of row # is).

At least some csv files and xml files include names for columns / values, edi doesn't even do that.


Organizations that send or receive documents between each other are referred to as "trading partners" in EDI terminology. The trading partners agree on the specific information to be transmitted and how it should be used. This is done in human readable specifications (also called Message Implementation Guidelines). While the standards are analogous to building codes, the specifications are analogous to blue prints.

Post #1607444
Posted Wednesday, August 27, 2014 6:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:47 AM
Points: 25, Visits: 466
This appears to be a HIPAA EDI file. The 271 file layout can be found here (Though there are different versions, it will be important you know what version of the file you have)
http://www.cms.gov/Research-Statistics-Data-and-Systems/CMS-Information-Technology/HETSHelp/downloads/HETS270271CompanionGuide5010.pdf

~ = New "loop" of the data
* = column delimiter
If you go through the guide you will find that the first few characters of each row define what "loop" of data it is, and that then defines what each column means.

On the plus side, the 271 is the simplest of the HIPAA files to work with.
Post #1607803
Posted Wednesday, August 27, 2014 7:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:48 PM
Points: 1,754, Visits: 4,966
Using T-SQL to parse EDI records is probably not the best way to accomplish whatever it is you're doing. You would get more useful advice posting this question in a C# or BizTalk forum. I'd suggest using an EDI integration API like Mirth to extract only the columns you need and in the format you need to a flat text file, then bulk copy the text file into SQL Server.
http://www.mirthcorp.com/products/mirth-connect
http://blogs.msdn.com/b/biztalknotes/archive/2014/05/02/installing-hl7-accelerator-for-biztalk-server.aspx
Post #1607812
Posted Wednesday, August 27, 2014 7:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:47 AM
Points: 25, Visits: 466
Eric M Russell (8/27/2014)
Using T-SQL to parse EDI records is probably not the best way to accomplish whatever it is you're doing. You would get more useful advice posting this question in a C# or BizTalk forum. I'd suggest using an EDI integration API like Mirth to extract only the columns you need and in the format you need to a flat text file, then bulk copy the text file into SQL Server.
http://www.mirthcorp.com/products/mirth-connect
http://blogs.msdn.com/b/biztalknotes/archive/2014/05/02/installing-hl7-accelerator-for-biztalk-server.aspx


+1000
Post #1607815
Posted Tuesday, September 2, 2014 1:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:48 AM
Points: 106, Visits: 510
There are many standards for EDI and within each standard a distinct definition for each type of file. Which type of file are you trying to import ? An Order, an Invoice, etc.

A file definition includes the definition of each type of line - for instance, an invoice header/master, a list of invoice item lines, etc. So an EDI file is a collection of lines of different format. Merely dumping each line into a table consisting of a varchar(128) column will not be very useful in itself. An incoming Purchase Order file would have to interpreted to put the Buyer Name, order date into a "PO_Header" table, the item lines would have to be extracted to go into a "PO_Detail" table. Then you would have to check against each item in the PO against your own database of items to check for errors on the part of the customer, check prices, etc.

Short of buying a commercial package, parsing multiple types of incoming EDI files is far from being a trivial effort.
Post #1609778
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse