OPENROWSET and BULK INSERT (3)

{Previous Article in Series}
{Next}
This is the promised post about using a Powershell script to inspect large files. It won’t be possible to open a very large file in Excel of course, and some text readers may struggle under the weight of data. This script is an alternative that runs under most if not all versions of Windows, and doesn’t need an installation or a licence.

When you run a Powershell script you may get the following error: “File … cannot be loaded. The file … is not digitally signed. You cannot run this script on the current system.” At this point I tend to lose interest, as I don’t want to be detoured from the main job either into signing the script or having the security policy relaxed, which doesn’t sound like a good idea even if it were allowed (which it won’t be). It’s probably not that difficult to sign a script but there’s never been time to find out how; if I do I’ll be sure to blog it here.

Fortunately you can just paste the contents of the file into the Powershell command window and run it (the Powershell ISE is really the best place for this, if you use the basic window you may have to mess with semi-colons to keep the lines together). In the ISE I do the composition in the top window, which means I can save my changes back to the file. I copy/paste to the lower window when I want to execute my code.

As discussed previously, if you’re investigating errors you’ll probably want to make a comparison of good and bad rows. The script allows you to specify a range. It doesn’t try to format the data but does put in line breaks so that it’s obvious where each row starts and ends.


$Path = {file path\name}
if(Test-Path $Path)
{
$reader = [System.IO.File]::OpenText($Path)
try
{
#Base ONE loop counter
$i=1
#ENTER YOUR REQUIRED LINE NUMBERS HERE
$firstrow = 1
$lastrow = 1
for(;;)
{
$line = $reader.ReadLine()
if ($i -ge $firstrow -and $i -le $lastrow)
{
$line
Write-Host
}
#if only one row wanted don't bother with $lastrow arg
elseif($firstrow -gt $lastrow -and $i -eq $firstrow)
{
$line
Write-Host
}
else
{
if ($i -gt $firstrow -and $i -gt $lastrow)
{
break
}
}
$i++
}
}
finally
{
$reader.Close()
}
}
else
{
Write-Host $Path not found.
}

Advertisements

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