Get file information and save to a table with Powershell







Function AutoImportCommaFlatFiles($location, $file, $extension, $server, $database)
{
    $full = $location + $file + $extension
    $all = Get-Content $full
    $columns = $all[0]
    $columns = $columns.Replace(" ","")
    $columns = $columns.Replace(",","] VARCHAR(100), [")
    $table = "if object_id( '"+ $file + "','U')is not null drop table " + $file + " CREATE TABLE " + $file + "([" + $columns + "] VARCHAR(100))"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $buildTable = New-Object System.Data.SqlClient.SqlCommand
    $insertData = New-Object System.Data.SqlClient.SqlCommand
    $connection.ConnectionString = "Data Source=" + $server + ";Database=" + $database + ";integrated security=true"
    $buildTable.CommandText = $table
    $buildTable.Connection = $connection
    ## Added to function
    $x = 0
    $insertData.CommandText = "EXECUTE stp_CommaBulkInsert @1,@2"
    $insertData.Parameters.Add("@1", $full)
    $insertData.Parameters.Add("@2", $file)
    $insertData.Connection = $connection
    $connection.Open()
    $buildTable.ExecuteNonQuery()
    $connection.Close()
    ## Added to function
    $x = 1
    if ($x = 1)
    {
        $connection.Open()
        $insertData.ExecuteNonQuery()
        $connection.Close()
    }
}




## export fil info to a csv file
$myexport="c:\temp\mypdffiles.csv"
 $mypath="\\myshare\ftp.mycompany.net\users\me"
##Copy file from one location to another
 $mytarget="C:\temp\test"
 Copy-Item $mypath $mytarget -recurse 
Get-ChildItem -Path $mytarget 

Get-ChildItem -Path $mypath  -Filter *.pdf -Recurse  -File -EA 0 | Select DirectoryName, Name,LastWriteTime,Length | convertto-csv -NoTypeInformation | % { $_ -replace '"', ''} | out-file $myexport | out-null
### bulk insert csv file to a table
AutoImportCommaFlatFiles -location "c:\temp\" -file "mypdffiles" -extension ".csv" -server "MC\MSSQL2014" -database "mydb1" | out-null



 
  
 
  CREATE PROCEDURE stp_CommaBulkInsert
@file NVARCHAR(250), @table NVARCHAR(250)
AS
BEGIN
 DECLARE @f NVARCHAR(250), @t NVARCHAR(250), @s NVARCHAR(MAX)
 SET @f = @file
 SET @t = @table
 
 SET @s = N'BULK INSERT ' + @t + '
  FROM ''' + @f + '''
  WITH (
   FIELDTERMINATOR = '',''
   ,ROWTERMINATOR = ''0x0a''
   ,FIRSTROW=2
  )'
 
 EXEC sp_executesql @s
END
 
--Check data from the target table
Select * from mypdffiles


 

Code from https://www.mssqltips.com/sqlservertip/3208/automating-flat-file-sql-server-imports-with-powershell/

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