Import Excel

Import-excel.ps1

function Import-Excel ($FolderPath, $XlsxFile, $Server, $Database, $Table) {
    # Create an Excel workbook...
    $Excel = New-Object -ComObject Excel.Application;
    $Workbook = $Excel.WorkBooks.Open((Join-Path -Path (Convert-Path -Path $FolderPath) -ChildPath $XlsxFile));
    $WorkSheet = $Workbook.WorkSheets.Item(1); # Thankfully only 1 sheet so this doesn't need to change...
    $StartRow = 2; # ...ignore headers...

    # Insert into a System.Data.DataTable...
    $DataTable = New-Object -TypeName System.Data.DataTable;
    $null = $DataTable.Columns.Add('DiscId', 'System.Int32');
    $DataTable.Columns['DiscId'].AutoIncrement = $true;
    $null = $DataTable.Columns.Add('Person', 'System.String');
    $null = $DataTable.Columns.Add('Job', 'System.String');
    $null = $DataTable.Columns.Add('Notes', 'System.String');

    # Load the DataTable...
    do {
        $Person = $WorkSheet.Cells.Item($StartRow, 1).Value();
        $Job = $WorkSheet.Cells.Item($StartRow, 2).Value();
        $Notes = $WorkSheet.Cells.Item($StartRow, 3).Value();

        $Row = $DataTable.NewRow();
        $Row.Person = $Person;
        $Row.Job = $Job;
        $Row.Notes = $Notes;
    
        $DataTable.Rows.Add($Row);
        $StartRow++;    
    } while ($WorkSheet.Cells.Item($StartRow, 1).Value() -ne $null); #...until a gap in values...
    $Excel.Quit(); # ...then exit...

    # Bulk load it...
    $BulkCopy = New-Object -TypeName Data.SqlClient.SqlBulkCopy -ArgumentList $ServerConnection;
    $BulkCopy.DestinationTableName = $Table;
    $BulkCopy.WriteToServer($DataTable);
};