1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
<#
.SYNOPSIS
    This PowerShell script connects to a SQL Server database, 
    reads data from a CSV file, and inserts or updates the data 
    in a corresponding table. It provides flexibility through 
    parameters such as SQL server credentials, file paths, 
    and options to clear old data.

.DESCRIPTION
    The script begins by defining parameters that allow customization 
    of SQL Server connection details, CSV file paths, and more. 
    It then includes functions for executing SQL commands 
    and managing database tables.

    The main functionalities include:
    1. Creating a new table or updating an existing one based on CSV file data.
    2. Importing CSV data and inserting it into the database table.
    3. Optionally clearing old data by creating a new table.
    4. Displaying a completion message when the process is finished.

#>

param (
    [string]$sqlUsername = "SuperAdmin",
    [string]$sqlPassword = "**********",
    [string]$serverName = "DESKTOP-*******",
    [string]$databaseName = "PBI_Inventory",
    [string]$csvFilePath = "C:\Users\ted\Downloads\",
    [string]$csvFileName = "workspaceSummary.csv",
    [bool]$clearOldData = $false
)

# Function to execute a SQL command with parameters
function Execute-DB-Command-With-Params {
    param(
        [System.Data.SqlClient.SqlConnection]$connection,
        [String]$command,
        [Hashtable]$parameters
    )

    $cmd = $connection.CreateCommand()
    $cmd.CommandText = $command
    foreach ($key in $parameters.Keys) {
        $cmd.Parameters.AddWithValue($key, $parameters[$key]) | Out-Null
    }
    $cmd.ExecuteNonQuery()
}

# Function to add a table to the database
function Add-Table {
    param(
        [System.Data.SqlClient.SqlConnection]$connection,
        [String]$tableName,
        [String[]]$feature
    )

    $createTableScript = @"
        IF OBJECT_ID('$tableName', 'U') IS NOT NULL
            DROP TABLE $tableName;
        CREATE TABLE $tableName (
            $($feature -join " VARCHAR(MAX),$([Environment]::NewLine)")
            VARCHAR(MAX)
        );
        ALTER TABLE $tableName
            ADD InsertDataTime VARCHAR(MAX);
"@
    Execute-DB-Command-With-Params $connection $createTableScript @{}
}

# Function to insert data into a table
function Insert-Data {
    param(
        [System.Data.SqlClient.SqlConnection]$connection,
        [String]$tableName,
        [String[]]$feature,
        [String[]]$insertData
    )

    $today = Get-Date -Format "yyyy-MM-dd_HH:mm:ss"
    $columns = $feature -join " VARCHAR(MAX),$([Environment]::NewLine)"

    $combinedScript = @"
        IF OBJECT_ID('$tableName', 'U') IS NULL
        BEGIN
            CREATE TABLE $tableName (
                $columns VARCHAR(MAX),
                InsertDataTime VARCHAR(MAX)
            );
        END

        INSERT INTO $tableName ($($feature -join ', '), InsertDataTime)
        VALUES ('$($insertData -join "', '")', '$($today)');
"@
    Execute-DB-Command-With-Params $connection $combinedScript @{}
}

# Create a new SQL connection
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Data Source=$serverName;Initial Catalog=$databaseName;User Id=$sqlUsername;Password=$sqlPassword;"
$connection.Open()

# Extract table name from CSV file name
$tableName = $csvFileName -replace '(?s)(.*)\.csv.*', '$1'

# Construct full file path
$csvFilePath = Join-Path $csvFilePath $csvFileName

# Import CSV data
$data = Import-Csv -Path $csvFilePath

# Extract column names from the first row of the CSV data
$feature = $data[0].PSObject.Properties.Name

# If specified, clear old data by adding a new table
if ($clearOldData) {
    Add-Table -connection $connection -tableName $tableName -feature $feature
}

# Insert each row of data into the database
foreach ($row in $data) {
    $insertData = $row.PSObject.Properties.Value
    Insert-Data -connection $connection -tableName $tableName -feature $feature -insertData $insertData
}

# Close the database connection
$connection.Close()

# Display completion message
Write-Host "Completed"