how to insert a specific data in text file(with .log extension) into database table?

Question

I have managed to insert the whole text-file data in SQL-SERVER database table with this statement

BULK INSERT [dbo].[tablename] FROM 'c:\weblog.log' WITH (
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n' )

But my text-file is not organized in any format and it contains some data i want to omit from the insertion process. So i am looking for a way to be able to only insert some of the data in the text-file into my database table?

Answer 1

There are two ways. One way is to write some code that will read the specific data, to be inserted into the database, from the file and then insert it to the database. Second, if you have some minimal data that you want to remove from the file, you might run a Regex query to find and replace them with none (deleting the unwanted portion) from the file and then doing a bulk insert.

For bulk insert to work, you need it to be a delimited text file. So if your log file is not a delimited log file, you might not be able to insert it using the bulk insert.