Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Open EDI file in SQL Server


Open EDI file in SQL Server

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45241 Visits: 39928
Heh... the neat thing about EDI is that it has so very many standards. :-D

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
John Bevilaqua-427875
John Bevilaqua-427875
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 108
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
Rudyx - the Doctor
Rudyx - the Doctor
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3290 Visits: 2476
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."
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2185 Visits: 12547
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...
palotaiarpad
palotaiarpad
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1914 Visits: 753
The data seems to be in EDI X12 format. Maybe here you will find more details about it.
patrickmcginnis59 10839
patrickmcginnis59 10839
SSC Eights!
SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)

Group: General Forum Members
Points: 886 Visits: 5105
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.


to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
David.Lester
David.Lester
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 492
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.
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4643 Visits: 9579
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


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
David.Lester
David.Lester
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 492
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
j-1064772
j-1064772
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 1202
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search