Open EDI file in SQL Server

Question

Open EDI file in SQL Server

ramadesai108

SSChampion

Points: 10609
More actions
September 27, 2012 at 6:23 pm

#264928

Is there a way to open EDI file in SQL Server and insert the data into tables?
Thanks.

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

There are many formats for EDI files ... generally set by the partners (sender & receiver). So like many things in SQL the phrase "It depends" applies to your request.

To help those who can and will help you can you supply a sample of the file ?

Be sure NOT to include any propriatory data, or data which can iidentify a particular individual and / or business.

With that data I am sure someone will assist you.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ramadesai108 SSChampion Points: 10609 More actions · Answer 2

Thank you Ron. Here is the EDI 271 File sample: I would truly appreciate if someone can help.

ISA*00* *00* *ZZ*0000110101 *ZZ*GrpSS *120510*0613*^*00501*131061332*0*P*:~GS*HN*123456789*123456789*20120510*0613*1*X*005010X212~ST*277*000000001*005010X212~BHT*0010*08*QA0000*20120510*0513*DG~HL*1**20*1~NM1*PR*2*UC*****PI*11111~HL*2*1*21*1~NM1*41*2*HC*****46*0003290000~HL*3*2*19*1~NM1*1P*2*HC*****XX*0003290000~HL*4*3*22*0~NM1*IL*1*FName*LName****MI*000368000~TRN*2*000009700~STC*A4:0*00000500~DTP*472*D8*00020002

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 3

formatted a bit for readability;

similar to some EDI files I handle, it looks like the tilde(~) is the delimiter for message segments;

the ISA segment is a control header for "who from" information;

each segment is sandwiched between ~ST for Segment Start and ~STC for Segment end.

the internal part is different from what I've processed myself, this may help folks a bit.

ISA*00* *00* *ZZ*0000110101 *ZZ*GrpSS *120510*0613*^*00501*131061332*0*P*:~GS*HN*123456789*123456789*20120510*0613*1*X*005010X212

~ST*277*000000001*005010X212

~BHT*0010*08*QA0000*20120510*0513*DG

~HL*1**20*1

~NM1*PR*2*UC*****PI*11111

~HL*2*1*21*1

~NM1*41*2*HC*****46*0003290000

~HL*3*2*19*1

~NM1*1P*2*HC*****XX*0003290000

~HL*4*3*22*0

~NM1*IL*1*FName*LName****MI*000368000

~TRN*2*000009700

~STC*A4:0*00000500

~DTP*472*D8*00020002

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ramadesai108 SSChampion Points: 10609 More actions · Answer 4

Thank you Lowell. This is very helpful. I am very new with EDI.

ramadesai108 SSChampion Points: 10609 More actions · Answer 5

Lowell,

If you have dealt with EDI before, can you please point me in the right direction? How did you import the data from EDI to SQL Server?

Thanks.

Lowell SSC Guru Points: 323518 More actions · Answer 6

well, like Ron/bitbucket said; there's many formats to an EDI file; somewhere someone on your side should have put together a document mapping out each element;

once you have that document, it's fairly easy to split the data on the delimiter and stick them in the right tables;

but without that layout i couldn't offer much except to point you to examples, sorry.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ramadesai108 SSChampion Points: 10609 More actions · Answer 7

We do not have the layout file, how do I create one or where do I get one?

Thanks.

Lowell SSC Guru Points: 323518 More actions · Answer 8

ramadesai108 (9/28/2012)
We do not have the layout file, how do I create one or where do I get one?
Thanks.

ouch, there is seriously no "easy" way on this then.

to the best of my knowledge, you cannot jsut magically create one;

each field, their meaning, data type and min/max values are all business dependent;

it might be if you have some code that either creates or reads the edi file iteself, you need to go through the code.

here's an example layout of one file the US gove happens to use; it goes to teh trouble to breakd own every value;

you need something similar to make heads or tails of the incoming data.

http://www.hud.gov/offices/cpd/systems/idis/edi/idis_edi_records_layout.pdf

Even an experienced code monkey like me would need something like that as a starting reference.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ramadesai108 SSChampion Points: 10609 More actions · Answer 9

Thank you, but I do not have any layout. There must be some one who is expert on this, or has some idea how I can proceed.

Thanks.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

ramadesai108 (9/28/2012)
Thank you, but I do not have any layout. There must be some one who is expert on this, or has some idea how I can proceed.
Thanks.

Who's sending you this document? Ask them for the layout docs. Unless you get lucky and someone who reads this thread has worked with that exact file before, EDI is too varied without the layout docs.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Nadrek SSC-Insane Points: 20039 More actions · Answer 11

Those are almost certainly HIPAA formats, i.e. ASC X12 270 ASC X12 271 ASC X12 834 ASC X12 837

A very brief overview:

http://healthit.hhs.gov/pdf/electronic-eligibility/appendix-e.pdf

You need to ask for the general ASC X12 "Implementation Guide" as well as the "Companion Guide" from whoever is generating the file in question - code for one 271 file may not work for another 271 file.

And example "Companion Guide" - DO NOT USE THIS ONE FOR YOUR PROJECT.

http://www.cms.gov/Research-Statistics-Data-and-Systems/CMS-Information-Technology/HETSHelp/Downloads/HETS270271CompanionGuide5010.pdf

Note that a correct implementation is significantly more difficult than a minimally working implementation for a single file - look for third party products or another language entirely. The meaning of different segments depends on context, not all fields are always filled in, and it gets more interesting from there. There should also be loop counters (make sure you understand the loops, as well).

ramadesai108 SSChampion Points: 10609 More actions · Answer 12

Thank you Nadarek and Kraig.

Nadarek, That document did help me. By looking at the content of the file clarified some things, but I think I need to have a different specification.

If I get the 271 document, can I import the data just using SQL Server? or do I have to use SSIS?

Thanks for your time.

siggemannen SSCrazy Points: 2436 More actions · Answer 13

Well, you need to split the file by newlines and import content of each line into a staging table that looks something like:

CREATE TABLE dbo.t_stage (txt NVARCHAR(MAX), row_id INT IDENTITY PRIMARY KEY)

You can use bcp or BULK INSERT for this kind of job, or SIS, it usually depends what kind of rights you have accessing the file system, and what sort of automation is required.

Then you need to parse each line according to whatever EDI-standard they are using, and this is the hard part 🙂

ramadesai108 SSChampion Points: 10609 More actions · Answer 14

siggemannen,

Say for example I have a file named my127.edi. How to do bcp or bulk insert for this file?

Thanks for your time.