Bulk Insert Text Files with Powershell

Import-Module -Name 'SQLPS' -DisableNameChecking   
$svrname = "MC\MySQL2014" 
#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 
$svr.ConnectionContext.StatementTimeout = 0

#remove the filename column in  the target table
$q1 = @"
Use test1;
IF COL_LENGTH('dbo.myRegions','filename') IS NOT NULL
ALTER TABLE test1.dbo.myRegions DROP COLUMN filename;
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database  master -Query  $q1

$dt = (get-date).ToString("yyyMMdd") 
$destination_formatfilename ="$($workdir)$($formatfilename)"
$cmdformatfile="bcp $table format nul -c -x -f $($destination_formatfilename) -T  -t\t  -S $($svrname) "
Invoke-Expression $cmdformatfile

#Delay 1 second
Start-Sleep -s 1

$q2 = @"
Alter table test1.dbo.myRegions  Add filename varchar(500) Null;
#add the filename column to the target table
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database  master -Query  $q2 

$files = Get-ChildItem $workdir
$items = $files | Where-Object {$_.Extension -eq ".txt"} 
for ($i=0; $i -lt $items.Count; $i++) {
$strFileName = $items[$i].Name
$strFileNameNoExtension= $items[$i].BaseName
$query = @"
BULK INSERT test1.dbo.myRegions from '$($workdir)$($strFileName)' WITH (FIELDTERMINATOR = '\t', FIRSTROW = 2, FORMATFILE =  '$($destination_formatfilename)');
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database master  -Query  $query  -querytimeout 65534

#Delay 10 second
Start-Sleep -s 10

# Update the filename column
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database master  -querytimeout 65534  -Query  "Update test1.dbo.myRegions SET filename= '$($strFileName)' WHERE filename is null; "

# Move uploaded file to archive
If ((Test-Path "$($workdir)$($strFileName)") -eq $True) { Move-Item -Path  "$($workdir)$($strFileName)" -Destination  "$($workdir)Processed\$($strFileNameNoExtension)_$($dt).txt"}

Catch [Exception]
 write-host "--$strFileName "$_.Exception.Message


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