PSScript 1:
The below mentioned PowerShell script is used to load all CSV files into SQL Server at a time.
Note: Before executing this script need to check the SQL Server Instance name, Database name, and finally path of the CSV files.
function CSVUpload
{
param(
[parameter(Mandatory)]
[ValidateNotNullOrEmpty()]
[string] $serverName,
[parameter(Mandatory)]
[ValidateNotNullOrEmpty()]
[string] $dbCheck,
[parameter(Mandatory)]
[ValidateNotNullOrEmpty()]
[string] $pth
)
$strQuery = ""
$fls = Get-ChildItem -Path 'C:\\PSScript\\RINDLA\\'
#$flsDifrectories = $fls | where {$_.mode -eq 'd----' } | select Name
#$flsDifrectories | Out-Host
$csvAll=$fls | where {$_.name.EndsWith('.csv') } | select Name
foreach ($tree in $csvAll) {
$fileName='C:\\PSScript\\RINDLA\\'+$tree.Name
if((Get-Content $fileName))
{
$file = Import-Csv $fileName | Get-Member -MemberType NoteProperty
#Check for table exists or not
$cn2 = new-object system.data.SqlClient.SQLConnection("Data Source="+'RAJ-PC\RINDLA'+";Integrated Security=SSPI;Initial Catalog="+'Sales_Info'+"");
$strQueryTest= "select name from sys.tables where name = '"+$tree.Name.Replace(".csv","").Replace(".","_")+"'"
$cmd = new-object system.data.sqlclient.sqlcommand( $strQueryTest, $cn2);
$cn2.Open();
$movedUserDetails= $cmd.ExecuteReader()
try
{
if(! $movedUserDetails.HasRows )
{
$isExists="false"
}
if( $movedUserDetails.HasRows)
{
while ($movedUserDetails.Read())
{
if($movedUserDetails.GetValue(0) -eq $tree.Name.Replace(".csv","").Replace(".","_"))
{
$isExists="true"
}
}
}
}
catch
{
#log error
throw "Error reading file "+$tree.Name
}
finally
{
$cn2.Close()
}
#create table for new file
if($isExists -eq "false")
{
$strQuery = "Create Table ["+ $tree.Name.Replace(".csv","").Replace(".","_") +"]"
$strQueryCol=""
foreach ($col in $file) {
$strQueryCol= $strQueryCol+ ",["+$col.Name + "] varchar (max)"
}
if($strQueryCol.Length -gt 0)
{
$isExists ="true";
$strQueryCol = $strQueryCol.Substring(1,$strQueryCol.Length-1)
if( $strQueryCol -ne "")
{
$strQuery = $strQuery + " ( " + $strQueryCol + " )"
}
$cmdNew = new-object system.data.sqlclient.sqlcommand( $strQuery, $cn2);
$cn2.Open();
$cmdNew.ExecuteNonQuery();
$cn2.Close()
}
}
#Insert data in table
$QueryInsert=""
$data =Import-Csv $fileName
$fileName
$i=0;
if(!$data.length)
{
foreach ($dt in $data[0])
{
$Name=""
$Values=""
$dt.psobject.Properties | foreach {
if($Name -eq "")
{
$Name =$Name +"["+$_.Name +"]"
}
else
{
$Name =$Name +","+"["+$_.Name +"]"
}
if($Values -eq "")
{
$Values =$Values +"'"+$_.Value.Replace("'","''")+"'"
}
else
{
$Values =$Values +",'"+$_.Value.Replace("'","''")+"'"
}
}
$QueryInsert = "Insert into ["+$tree.Name.Replace(".csv","").Replace(".","_")+"] "
$QueryInsert=$QueryInsert + "( "+ $Name + " )";
$QueryInsert=$QueryInsert +" Values ("+ $Values +")";
$cmdNewInsert = new-object system.data.sqlclient.sqlcommand( $QueryInsert, $cn2);
$cn2.Open();
$cmdNewInsert.ExecuteNonQuery();
$cn2.Close()
}
}
while( $i -lt $data.length)
{
foreach ($dt in $data[$i])
{
$Name=""
$Values=""
$dt.psobject.Properties | foreach {
if($Name -eq "")
{
$Name =$Name +"["+$_.Name +"]"
}
else
{
$Name =$Name +","+"["+$_.Name +"]"
}
if($Values -eq "")
{
$Values =$Values +"'"+$_.Value.Replace("'","''")+"'"
}
else
{
$Values =$Values +",'"+$_.Value.Replace("'","''")+"'"
}
}
$QueryInsert = "Insert into ["+$tree.Name.Replace(".csv","").Replace(".","_")+"] "
$QueryInsert=$QueryInsert + "( "+ $Name + " )";
$QueryInsert=$QueryInsert +" Values ("+ $Values +")";
$QueryInsert
$cmdNewInsert = new-object system.data.sqlclient.sqlcommand( $QueryInsert, $cn2);
$cn2.Open();
$cmdNewInsert.ExecuteNonQuery();
$cn2.Close()
}
$i =$i +1
}
}
}
}
CSVUpload -serverName 'RAJ-PC\RINDLA' `
-dbCheck 'Sales_Info' `
-pth 'C:\\PSScript\\RINDLA\\'
==================================================================================================================================================
PSScript 2:
The below mentioned PowerShell script is used to load the same CSV file structure of all files into SQL Server at a time.
Note: Before executing this script need to check the SQL Server Instance name, Database name, Table name, and finally path of the CSV files.
#Read CSV file
#open connection to database using bulkcopy
#convert array to data table
#bulkload data into table
#note: column sequence and data types should match
function Get-Type
{
param($type)
$types = @(
'System.Boolean',
'System.Byte[]',
'System.Byte',
'System.Char',
'System.nvarchar',
'System.Datetime',
'System.Decimal',
'System.Double',
'System.Guid',
'System.Int16',
'System.Int32',
'System.Int64',
'System.Single',
'System.UInt16',
'System.UInt32',
'System.UInt64')
if ( $types -contains $type ) {
Write-Output "$type"
}
else {
Write-Output 'System.String'
}
}
function Out-DataTable
{
[CmdletBinding()]
param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject)
Begin
{
$dt = new-object Data.datatable
$First = $true
}
Process
{
foreach ($object in $InputObject)
{
$DR = $DT.NewRow()
foreach($property in $object.PsObject.get_properties())
{
if ($first)
{
$Col = new-object Data.DataColumn
$Col.ColumnName = $property.Name.ToString()
if ($property.value)
{
if ($property.value -isnot [System.DBNull]) {
$Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)")
}
}
$DT.Columns.Add($Col)
}
if ($property.Gettype().IsArray) {
$DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1
}
else {
$DR.Item($property.Name) = $property.value
}
}
$DT.Rows.Add($DR)
$First = $false
}
}
End
{
Write-Output @(,($dt))
}
} #Out-DataTable
$fileExtension = '*csv*.*'#you can use wildcards here for name and for extension
$searchinfolder = 'C:\PSScript\RINDLA'
$FileNames=Get-ChildItem -Path $searchinfolder -Filter $fileExtension -Recurse | %{$_.FullName}
$fileName = [System.IO.Path]::GetFileNameWithoutExtension($FileNames)
foreach($Files in $FileNames)
{
$file=$Files
$cn = new-object system.data.SqlClient.SQLConnection("Data Source="+'RAJ-PC\RINDLA'+";Integrated Security=SSPI;Initial Catalog="+'Estimation_Details'+"");
$cn.Open()
$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
$bc.BatchSize = 10000;
$bc.BulkCopyTimeout = 1000
$bc.DestinationTableName = "All_Region_Sales"
#$bc.DestinationTableName = $fileName
$data = Import-Csv $file | Out-DataTable
$bc.WriteToServer($data)
}
==================================================================================================================================================
PSScript 3:
The below mentioned PowerShell script is used to Extract the query result from SQL Server to CSV files
Note: Before executing this script need to check the SQL Server Instance name, Database name, Query, and finally path of the CSV files.
[string] $Server= "RAJ-PC\RINDLA"
[string] $Database = "AdventureWorks"
[string] $UserSqlQuery= $("SELECT * FROM sys.tables")
# declaration not necessary, but good practice
$resultsDataTable = New-Object System.Data.DataTable
$resultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery
# executes a query and populates the $datatable with the data
function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {
$Datatable = New-Object System.Data.DataTable
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
$Reader = $Command.ExecuteReader()
$Datatable.Load($Reader)
$Connection.Close()
return $Datatable
}
#validate we got data
Write-Host ("The table contains: " + $resultsDataTable.Rows.Count + " rows")
$resultsDataTable | export-Csv -Path "D:\RAJ\Queryoutput.csv"
==================================================================================================================================================
PSScript 4:
The below mentioned PowerShell script is used to Extract the query result from multiple SQL Server Instances to CSV files
Note: Before executing this script need to check the SQL Server Instance name list, SQL Scripts path, and finally output path of the CSV files.
Push-location
import-module sqlps
Pop-Location
$OutputPath = "D:\RAJ\Output"
$SQLScriptsPath = 'D:\RAJ\SE'
$ServerList = "D:\RAJ\ServerNames.txt"
Get-ChildItem $SqlScriptsPath -Filter *.sql | Sort-Object Name | ForEach-Object {
$File = $_
Get-Content $ServerList | ForEach-Object {
$Server = $_
# A directory and file based on the server name from the text file
New-Item "$OutputPath\$Server" -ItemType Directory -Force | Out-Null
$OutputFile = "$OutputPath\$Server\$($File.BaseName).csv"
$OutputFile
try {
Invoke-SqlCmd –ServerInstance $Server -QueryTimeout 0 -InputFile $File.FullName |
Export-Csv $OutputFile -NoTypeInformation
} catch {
Write-Host $_.Exception.Message -ForegroundColor Red
}
}
}
==================================================================================================================================================
PSScript 4:
The below mentioned PowerShell script is used to save the result of SQL Query output into CSV files.
Note: Before executing this script need to check the SQL Server Instance name list, Output path(CSV), and SQL Scripts path.
Push-location
import-module sqlps
Pop-Location
$OutputPath = "E:\DBScripts\Raj\Output"
$SQLScriptsPath = 'E:\DBScripts\Raj\Scripts'
$ServerList = "E:\DBScripts\Raj\ServerNames.txt"
Get-ChildItem $SqlScriptsPath -Filter *.sql | Sort-Object Name | ForEach-Object {
$File = $_
Get-Content $ServerList | ForEach-Object {
$Server = $_
# A directory and file based on the server name from the text file
New-Item "$OutputPath\$Server" -ItemType Directory -Force | Out-Null
$OutputFile = "$OutputPath\$Server\$($File.BaseName).csv"
$OutputFile
try {
Invoke-SqlCmd –ServerInstance $Server -InputFile $File.FullName |
Export-Csv $OutputFile -NoTypeInformation
} catch {
Write-Host $_.Exception.Message -ForegroundColor Red
}
}
}
==================================================================================================================================================
No comments:
Post a Comment