SQL Clone
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 Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222716 Visits: 42003
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
John Bevilaqua-427875
John Bevilaqua-427875
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11338 Visits: 2503
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14528 Visits: 14168
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2434 Visits: 804
The data seems to be in EDI X12 format. Maybe here you will find more details about it.
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4482 Visits: 5925
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
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29865 Visits: 11559
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
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1297 Visits: 1213
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