Bulk Insert txt Files (templated files) into SQL Server Table and Add File Name Column with Powershell





Import-Module -Name 'SQLPS' -DisableNameChecking   
 
$workdir="C:\temp\test\"
$svrname = "MC047012\MSSQL2014" 
 
 Try
{
 
#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 
$svr.ConnectionContext.StatementTimeout = 0
$table="test1.dbo.myRegions"
 
$q1 = @"
Use test1;
IF COL_LENGTH('dbo.myRegions','filename') IS NOT NULL
BEGIN
ALTER TABLE test1.dbo.myRegions DROP COLUMN filename;
END
"@
#add the filename column to the target table
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database  master -Query  $q1

$dt = (get-date).ToString("yyyMMdd") 
$formatfilename="$($table)_$($dt).xml"
$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

# 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
 }
 
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