A N

February 4, 2016

Get content of larg files, replace and bulkimport into SQL

Filed under: Tips — donrsh @ 2:23 pm

I had a few and larg log files which was around 500 MB each and looked like this:

Login Succeeded	server38	admin	2013-07-11 16:10:35	1
..Logout Succeeded	server4438	admin1	2013-07-11 16:15:35	1
Login Succeeded	server4438	admin2	2013-07-11 16:18:14	1
..Logout Succeeded	server4438	admin	2013-07-11 16:24:05	1
Login Succeeded	server1106	admin1	2013-07-12 10:53:20	1
Login Succeeded	server1106	admin2	2013-07-12 11:34:29	1

[System.IO.File]::ReadAllLines Read More Here

$Files = Get-ChildItem  'C:\Files'
foreach ($File in $Files)
{
	$FullName = $File.FullName
	([System.IO.File]::ReadAllLines($FullName)) -replace ('..Logout Succeeded', 'Logout') -replace ('Login Succeeded', 'Login') |
	Out-File -FilePath 'C:\Files\Export.csv' -Append
}

The result is:

Login server38    admin   2013-07-11 16:10:35 1
Logout  server4438  admin1   2013-07-11 16:15:35 1
Login server4438  admin2   2013-07-11 16:18:14 1
Logout  server4438  admin   2013-07-11 16:24:05 1
Login server1106  admin1  2013-07-12 10:53:20 1
Login server1106  admin2  2013-07-12 11:34:29 1

Now lets bulk import it to SQL

-- 1
GO
CREATE TABLE CSVTest
(
            Event VARCHAR(40),
            CitrixServer VARCHAR(40),
            Username VARCHAR(40),
            "DateTime" VARCHAR(40),
            "status" VARCHAR(40)
)
GO

-- 2

BULK
INSERT CSVTest
FROM 'C:\Files\Export.csv'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
GO

--3

select [Event],[CitrixServer],[UserName],CONVERT(datetime,[DateTime]) [DateTime],[Status] into Logs from CSVTest

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: