Posted by Steve on 10/10/06 00:11

batman wrote:
> i have a text file that is like:
>
--GIVEN this table dbo.ImportMyFile
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ImportMyFile](
[LineID] [int] IDENTITY(1,1) NOT NULL,
[LineText] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON

-- and your file example here named 'C:\MyFile.txt'
-- and this simple format file named C:\MyFormatFile.txt

9.0

1

1 SQLCHAR 0 1000 "\r\n" 2 LineText
""


-- execute this T-SQL
BULK INSERT AdventureWorks.dbo.ImportMyFile
FROM 'C:\MyFile.txt' WITH (FORMATFILE='C:\MyFormatFile.txt');
GO
DELETE FROM dbo.ImportMyFile
WHERE LineID < =(SELECT LineID FROM dbo.ImportMyFile WHERE LineText =
'START-OF-FILE ')
OR LineID >=(SELECT LineID FROM dbo.ImportMyFile WHERE LineText =
'END-OF-FILE')

See these topics in Books Online
BULK INSERT (Transact-SQL)
Understanding Non-XML Format Files
Using a Format File to Skip a Table Column


variations are endless.

• Next in forum: Re: how to delete lines from text file
• Prev in forum: Re: how to delete lines from text file
• Thread view: Re: how to delete lines from text file

[Reply to this message]