NTuF1Hnt

1# https://www.sqlteam.com/articles/fast-csv-import-in-powershell-to-sql-server
2
3[System.Reflection.Assembly]::LoadFrom(".\CsvDataReader.dll") | Out-Null
4
5
6
7$ServerName = "servername"
8$DatabaseName = "dbname"
9$path = "P:\Filepath\"
10
11
12function Invoke-SQL {
13    param(
14        [string] $connectionString = "",
15        [string] $sqlCommand = $(throw "Please specify a query.")
16      )
17
18
19    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
20    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
21    $connection.Open()
22
23    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
24    $dataset = New-Object System.Data.DataSet
25    $adapter.Fill($dataSet) | Out-Null
26
27    $connection.Close()
28    $dataSet.Tables
29
30}
31
32$cred = Get-Credential
33$files = Get-ChildItem -path "$($path)*.csv" -Name
34$ImportDate = Get-Date -UFormat "%Y%m%d"
35$Marshal=[Runtime.InteropServices.Marshal];
36$Handle=[IntPtr]::Zero;
37$Result= try {
38  [Runtime.InteropServices.Marshal]::PtrToStringBSTR(($Handle=$Marshal::SecureStringToBSTR($cred.Password))) 
39}catch {}
40finally{
41[Runtime.InteropServices.Marshal]::ZeroFreeBSTR($Handle)
42}
43
44$ConnectionString = "Server=$($ServerName); Database=$($DatabaseName);User Id=$($cred.UserName);Password=$($result);"
45
46foreach ($row in $files)
47{
48    #create the table name by using the file name but removing non-alpha characters
49    $TableName = "$($row)".Replace(".csv","").Replace("[^a-zA-Z]","")
50
51    #get the string to create the table by reading the column names
52    $reader = New-Object SqlUtilities.CsvDataReader("$($path)$($row)")
53    $CreateTable = "Create table [Import$($ImportDate)_$($TableName)] ("
54    For ($i=0; $i -lt $reader.FieldCount; $i++) {
55     $CreateTable = "$($CreateTable)[$($reader.GetName($i))$(If ([string]::IsNullOrEmpty($reader.GetName($i))) {"Column $($i)"} Else {}) ] varchar(5000), "
56    }
57    $CreateTable = "$($CreateTable) rowid int identity primary key)"
58       
59    
60    #Drop the table if it exists, then create it so we can import to it
61    $DropTable = "IF OBJECT_ID('Import$($ImportDate)_$($TableName)') IS NOT NULL DROP TABLE [Import$($ImportDate)_$($TableName)]"
62    Invoke-SQL -connectionString $ConnectionString -sqlCommand $DropTable
63    Invoke-SQL -connectionString $ConnectionString -sqlCommand $CreateTable
64
65    #Load the data
66    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnectionString
67    $bulkCopy.DestinationTableName = "Import$($ImportDate)_$($TableName)"
68
69    $bulkCopy.WriteToServer($reader)
70    $reader.Close();
71    $reader.Dispose();
72
73    
74}