Mike
asked on
Need MSSQL powershell Script
Greeting Experts,
Does anybody have a PowerShell script to upload 3 different excel.csv documents in to MSSQL 2012 express database (existing database) …or a sql statement to load the all 3 files at the same time….
Does anybody have a PowerShell script to upload 3 different excel.csv documents in to MSSQL 2012 express database (existing database) …or a sql statement to load the all 3 files at the same time….
ASKER
Is there a way to use this script using the 'sa" user account instead of my own credentials...
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO.SqlDataType') | Out-Null
# You can very easily convert this into a function / shell script by using parameters or $args[i] for these variables
$serverName = 'testserver';
$query = 'select * from [Sheet1$]';
$databaseName = "ExcelImport" ;
$tableName = "ExelSheet" ;
$filepath = "c:\workbook.xlsx";
# ACE works well for Excel 2007 or greater. You need to use JET 4.0 for Excel 2003. Use ACE whenever possible.
$connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
# Instantiate some objects which will be needed
$serverSMO = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName)
$db = $serverSMO.Databases[$databaseName];
$newTable = New-Object Microsoft.SqlServer.Management.Smo.Table ;
$newTable.Parent = $db ;
$newTable.Name = $tableName ;
$conn = New-Object System.Data.OleDb.OleDbConnection($connectionString)
$conn.open()
$cmd = New-Object System.Data.OleDb.OleDbCommand($query,$conn)
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter($cmd)
$dataTable = New-Object System.Data.DataTable
$dataAdapter.fill($dataTable)
$conn.close()
# Drop the table if it exists
if($db.Tables.Contains($tableName).Equals($true))
{
($db.Tables[$tableName]).Drop()
}
# Iterate the columns in the DataTable object and add dynamically named columns to the SqlServer Table object.
foreach($col in $dataTable.Columns)
{
$sqlDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Varchar
$dataType = New-Object Microsoft.SqlServer.Management.Smo.DataType($sqlDataType);
$dataType.MaximumLength = 8000;
$newColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($newTable,$col.ColumnName,$dataType);
$newColumn.DataType = $dataType;
$newTable.Columns.Add($newColumn);
}
$newTable.Create();
$connectionString = "Data Source=$serverName;Integrated Security=true;Initial Catalog=$databaseName;"
$bc = New-Object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bc.DestinationTableName = "$tableName"
$bc.WriteToServer($dataTable)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've requested that this question be deleted for the following reason:
1
1
what was wrong with the answers I provided?
Both answers I provided were resources that did exactly what the OP was asking for
ASKER
thank you
http://newsqlblog.com/2011/08/05/dynamic-excel-posh/