Skip to content

Instantly share code, notes, and snippets.

Created September 11, 2017 16:41
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
Import Excel
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;
} 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment