Open EDI file in SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2012 » SQL Server 2012 - T-SQL » Open EDI file in SQL Server

21 posts, Page 2 of 3

««1 2 3 »»

Open EDI file in SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

Evil Kraig F

Posted Friday, September 28, 2012 2:41 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 5,658, Visits: 6,100

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1366118

Nadrek

Posted Tuesday, October 02, 2012 1:31 PM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:25 AM
Points: 670, Visits: 2,026

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).

Post #1367259

ramadesai108

Posted Thursday, October 04, 2012 6:43 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

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.

Post #1368793

siggemannen

Posted Monday, October 08, 2012 2:05 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:12 PM
Points: 82, Visits: 201

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 :)

Post #1370030

ramadesai108

Posted Monday, October 08, 2012 7:49 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

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.

Post #1370129

Lowell

Posted Tuesday, October 09, 2012 5:12 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605, Visits: 27,645

ramadesai108 (10/8/2012)

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.

that's what we keep saying...unless you have the layout of the EDI file, any bulk insert would be a meaningless single column table; and that single column of data then needs to be parsed into...you guessed it: the columns identified in the EDI Layout document.

find the layout document, which must exist, you just haven't found it yet. it's very, very likely that different rows of the document go into multiple different tables. In that example I showed you, there's something like 40 plus tables that can possibly be in the EDI transmission.

--in via bulk insert
CREATE TABLE RAWEDI(RAWDATA varchar(max) )
BULK INSERT RAWEDI FROM 'c:\Data\my127.edi'
           WITH ( 
               DATAFILETYPE = 'char', 
               FIELDTERMINATOR = '[||]', --this never occurs in the real data, so it's a single wide row.
               ROWTERMINATOR = '~ST', --Segment Start/terminator
               FIRSTROW = 1 
            )

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1370320

Nadrek

Posted Tuesday, October 09, 2012 8:38 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:25 AM
Points: 670, Visits: 2,026

You need the over X12N layout.
AND
You need the companion guide.
AND
More than likely, you need to speak with someone at the generating end, because (more than likely) the layout + the companion guide are incomplete, ambiguous, mutually contradictory, and/or inaccurate in at least one detail.

No, you should never need SSIS.

No, you _cannot_ predict that || won't show up in the file unless it is explicitly forbidden.

SQL Server is, quite frankly, not the only tool to be using unless you nail down, in writing from the generator of the file (and the sender if that's different than the generator) all details of the format. As this is your first file, you're unlikely to guess all the weird edge cases that may apply. Your fields are likely to be * separated, but your segments may be ~ separated, ~LF separated, ~CRLF separated, or ??? separated. In particular, the first three options may change file to file or environment to environment or even within the same file (unlikely but possible).

If you can nail down the separators, and be _explicit_, not implicit, about it (most humans ignore LF and/or CRLF completely, not even noticing they're there or that there's a difference... and BCP/BULK INSERT with a format file cares), then you have a chance. Worse, some people will simply add in LF or CRLF when they want to look at the file by hand, and then take them out when they don't.

Pull out a hex editor like HxD and start looking at sample files, comparing them, byte by byte, field by field, segment by segment, with the layout and companion guide; without one, you're lost, because you assume that the people that coded the file generation used the same documentation you have accurately and completely. In most cases, they didn't even have the documentation you do, because both code and documentation changed over time, and not necessarily in lockstep.

Also, I'd recommend no longer using the term 'EDI file' - it's just a data file, and I think you're imposing rules in your mind that don't exist in the minds of others - EDI is just electronic data interchange; that can be a packed decimal EBCDIC file, or a 0 byte file where all value is in the filename, which is encrypted - 'EDI' means data is being interchanged, that's all, no specifics. Personally, I'd also say rename the extension to .txt if it's text data (as you've indicated it likely is), but I'm a little old-fashioned, and there's no actual issue with using the .edi extension.

Post #1370425

ramadesai108

Posted Wednesday, October 10, 2012 6:55 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

Lowel and Nadrek,
I truly appreciate your help and time. I will certainly try out the code from Lowell but it will be a while before I give you any feedback. I have now found the accompanying PDF document with all the columns. Since I am now working with some other pressing issues, I will not touch the EDI file import for a month. Your explanation was clear and very helpful. Thanks again for your time.

Post #1371171

jaime.haire

Posted Thursday, March 21, 2013 3:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 21, 2013 3:11 PM
Points: 1, Visits: 4

You may need to purchase the 271 implementation guide --
http://store.x12.org/store/healthcare-5010-consolidated-guides

Here is more info on the 271 format:
http://www.1edisource.com/learn-about-edi/transaction-sets/tset/271

Post #1434049

palotaiarpad

Posted Monday, April 15, 2013 10:35 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:20 AM
Points: 841, Visits: 317

Google is your friend: EDI ISA
http://msdn.microsoft.com/en-us/library/bb259967(v=bts.20).aspx

Post #1442405

« Prev Topic | Next Topic »

21 posts, Page 2 of 3

««1 2 3 »»

Permissions