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!