October 9, 2012 at 5:12 am
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
October 9, 2012 at 8:38 am
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[/url] 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.
October 10, 2012 at 6:55 pm
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.
March 21, 2013 at 3:13 pm
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
April 15, 2013 at 10:35 am
Google is your friend: EDI ISA
http://msdn.microsoft.com/en-us/library/bb259967(v=bts.20).aspx
April 15, 2013 at 5:06 pm
Heh... the neat thing about EDI is that it has so very many standards. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2014 at 9:20 am
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
August 7, 2014 at 11:14 am
My advice is to contact the source provider of the EDI file - plain and simple.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 13, 2014 at 1:28 am
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...
August 26, 2014 at 6:54 am
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.
August 27, 2014 at 6:42 am
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)
~ = 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.
August 27, 2014 at 7:01 am
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
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 27, 2014 at 7:03 am
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.
+1000
September 2, 2014 at 1:43 pm
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.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply