Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Open EDI file in SQL Server RE: Open EDI file in SQL Server

  • October 9, 2012 at 5:12 am

    #1547156

    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

    --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!