EDI - Flat File - SQL

I have a flat file that gets converted from an EDI file.  What is the best way to get this file imported into a SQL database?  

The top part is the header information and the bottom are the line items.   Thanks

"ORDER_CUSTOMER",1135,"Maltest - Test","","","","","","",""
"ORDER_VENDOR",2556,"test Inter","","","1111 Com Parkway","","LAKE","FL","33825"
"ORDER_ADDRESS","BT","Bill To","0089650630000","9","DUNS+4","MALTEST","","","P.O. BOX 4056","","DANVILLE","IL","64334"
"ORDER_ADDRESS","ST","Ship To","008965063W008","9","DUNS+4","MALTEST VERNON","","","78 LAKE TER N E","","MT. VERNON","IL","65464"
"ORDER_TERMS","ITD","02","End of Month (EOM)",60,,0.00,0,,"3","Invoice Date","","",,0.00,0.00,0.00,0.00,0.00," NET 60 ROG"
"ORDER_DATE_TIME","DTM",03/04/2005,,"002","Delivery Requested","",""
"ORDER_NOTE","CONTACT NUMBER [TE]: 867 940-9014 X2286"
"ORDER_NOTE","CONTACT NUMBER [TE]: (618) 244-9100"
"ORDER_REF_NUM","089528","VN","Vendor Order Number",""
"ITEM",0,"002","708862001202","UP","UPC Consumer Package Code (1-5-5-1)",1.0000,"CA",360.00,"","","","",""
"ITEM_DETAILS",0.00,0.00,0.00,0,0.00,"",0,"LENSES +150"
"ITEM_ID","708862001202","UP","UPC Consumer Package Code (1-5-5-1)"
"ITEM_ID","814112","IN","Buyer Item Number"
"ITEM",1,"003","708862001042","UP","UPC Consumer Package Code (1-5-5-1)",1.0000,"CA",360.00,"","","","",""
"ITEM_DETAILS",0.00,0.00,0.00,0,0.00,"",0,"LENSES +200"
"ITEM_ID","708862001042","UP","UPC Consumer Package Code (1-5-5-1)"
"ITEM_ID","814113","IN","Buyer Item Number"
"ITEM",2,"004","708862001233","UP","UPC Consumer Package Code (1-5-5-1)",1.0000,"CA",360.00,"","","","",""
"ITEM_DETAILS",0.00,0.00,0.00,0,0.00,"",0," LENSES +250"
"ITEM_ID","708862001233","UP","UPC Consumer Package Code (1-5-5-1)"
"ITEM_ID","814114","IN","Buyer Item Number"
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
If you just want to load it into a VARCHAR() column, BULK INSERT command should be the fastest way.
cat4larryAuthor Commented:
I want to split it apart into seperate columns
Anthony PerkinsCommented:
>>I want to split it apart into seperate columns<<
Since there is a varying number of columns, you can place it in a temporary table with a varchar column first as Scott has suggested.  You may be able to do is a table with two columns, the first to indicate the type of record, the second the rest.  Once it is in this temporary table you can pull the relevant parts out into the final tables by parsing the delimitted fields.

The other approach you can do is import all the rows into a temporary table with 20 columns, and then query the individual record types into the final table.  Not sure if you can do this with BULK INSERT as there will be rows with missing fields, but I suspect you can, otherwise you certainly can import it in with DTS.
The easiest way to import a text file to SQL is with DTS -
you can map the file columns to the database columns and import

 Create a DTS package

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.