472,129 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,129 software developers and data experts.

Bulk Insert Issue...

Hi Guys and Girls,

Tough one for me this, Im trying to bulk insert quite a big file, iv taken a small snippet of the file so you can slowly understand what im trying to get at! Please see below

24/06/2010 19:18:23 /IServerManager.GetPublicKey (timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)
24/06/2010 19:18:23 /IServerManager.Login (timings: authenticate=0 authorise=0 execute=4524029 logError=0 teardown=468003 total=4992032)
24/06/2010 19:20:31 /IServerManager.GetPublicKey (timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)

I have created a new database within SQL Server with the column names as follows

CREATE TABLE [dbo].[MedWayFiles](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[Date and Time] [datetime] NULL,
[Commands] [nvarchar](50) NULL,
[Timings] [nvarchar](50) NULL,
[Authenticate] [nvarchar](50) NULL,
[Authorise] [nvarchar](50) NULL,
[Execute] [nvarchar](50) NULL,
[LogError] [nvarchar](50) NULL,
[TearDown] [nvarchar](50) NULL,
[Total] [nvarchar](50) NULL,
[SLA Breach] [nvarchar](50) NULL,

As you can see the column names reference words that are within the Text file! Can some one please help me identifying the keywords within the text-file and assigning them to the
Relevant column! i.e bulkinsert! As I have 47 Text-Files and they all hold about 300+ lines of information and doing them one by one, well we all know will take many years!!!

Thanks in Advance!
Aug 10 '10 #1
1 1413
Jerry Winston
145 Expert 100+
This is doable. Just use BULK INSERT with ROWTERMINATOR like '\n' to access log entries one row at a time. Parse the log entry using CHARINDEX, SUBSTRING, and CAST inserting the derived values into your table [medWayFiles].

Once you perfect parsing the log strings, you can write a script to create the other 300 scripts. If your file names are numbered ie 'log0001.txt','log0002.txt' the potential to create a loop to generate the BULK INSERT functions are obvious. If they is no pattern to the file names, use
Expand|Select|Wrap|Line Numbers
  1. dir /b *.[myLogFileExtension] > myLogFileList.txt
from the windows command shell to create a list of file names.

This code creates a list of BULK INSERT statements you can copy from the file list created using the windows command line.
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE #myLogFiles(LogEntryFile VARCHAR(MAX))
  3. BULK INSERT #myLogFiles 'C:\data\myLogFileList.txt
  4. WITH
  5. (ROWTERMINATOR = '\n')
  7. SELECT 'BULK INSERT [#LogEntry] ''' +
  8. LogEntryFile +''' WITH (ROWTERMINATOR = ''\n'')'
  9. FROM #myLogFiles
From SSMS copy the code from the grid view below this code into a new query window:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE #LogEntry (Entry VARCHAR(MAX))
Insert your parsing code below your BULK INSERT statements in the new window:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [MedWayFiles]
  4. (Entry,
  5. CHARINDEX('attribute1',Entry),
  6. CHARINDEX('attribute2',Entry)-CHARINDEX('attribute1',Entry)
  7. ),
  8. ...
  9. FROM
  10. #LogEntry
It's not elegant, but it'll get the job done. An elegant solution IMO would do this all in one punch making use of sp_executesql, CURSORs, and CTE's.
Aug 10 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by php newbie | last post: by
6 posts views Thread by pk | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.