Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Open EDI file in SQL Server Expand / Collapse
Author
Message
Posted Friday, September 28, 2012 2:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 6,147, Visits: 7,202
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
Posted Tuesday, October 2, 2012 1:31 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:41 AM
Points: 861, Visits: 2,357
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
Posted Thursday, October 4, 2012 6:43 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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
Posted Monday, October 8, 2012 2:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 5, 2014 1:00 AM
Points: 109, Visits: 257
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
Posted Monday, October 8, 2012 7:49 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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
Posted Tuesday, October 9, 2012 5:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:55 AM
Points: 12,881, Visits: 31,817
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
Posted Tuesday, October 9, 2012 8:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:41 AM
Points: 861, Visits: 2,357
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
Posted Wednesday, October 10, 2012 6:55 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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
Posted Thursday, March 21, 2013 3:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, April 15, 2013 10:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:15 AM
Points: 1,295, Visits: 470
Google is your friend: EDI ISA
http://msdn.microsoft.com/en-us/library/bb259967(v=bts.20).aspx
Post #1442405
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse