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 (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88584 Visits: 41130
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
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4917 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
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4996 Visits: 13200
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.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2112 Visits: 776
The data seems to be in EDI X12 format. Maybe here you will find more details about it.
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1816 Visits: 5527
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 (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

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

Group: General Forum Members
Points: 12494 Visits: 10693
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 (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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
Say Hey Kid
Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)

Group: General Forum Members
Points: 685 Visits: 1207
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