SQLTeam.com | Weblogs | Forums

Open EDI files in MSSQL server and store data in tables

sql2012

#1

Is there a way to open EDI files in MSSQL server express and store the data in tables. I have tried

DECLARE @SQL VARCHAR(8000)

IF OBJECT_ID('TempDB..#BulkInsert') IS NOT NULL
BEGIN
DROP TABLE #BulkInsert
END

CREATE TABLE #BulkInsert
(

Line    VARCHAR(MAX)

)

SET @SQL = 'BULK INSERT #BulkInsert FROM ''c:\csvtest\TXTtest.txt''
WITH (ROWTERMINATOR = ''/'')'
EXEC (@SQL)

SELECT * FROM #BulkInsert

this inserts the info into the sql server but I would like it to break up into columns as well

another issue I have come across is the delimiters are not always the same. Sometimes they are / for the row terminator and * for the field terminator other times it is | and ~

is there a way to do this??

thank you in advance for any advice!


#2

Are the different delimiters within the same file or is it when you get a different file?
There are several parsers out there. An older splitter is Tally OH! An Improved SQL 8K “CSV Splitter” Function by Jeff Moden