Solved

Need MSSQL powershell Script

Posted on 2014-02-03
8
281 Views
Last Modified: 2014-02-12
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….
0
Comment
Question by:amstoots
  • 4
  • 3
8 Comments
 
LVL 32

Expert Comment

by:Big Monty
ID: 39830010
here's a script that'll first create the table definition based upon the excel file, and then upload it to your database:

http://newsqlblog.com/2011/08/05/dynamic-excel-posh/
0
 

Author Comment

by:amstoots
ID: 39832645
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)

Open in new window

0
 
LVL 32

Accepted Solution

by:
Big Monty earned 500 total points
ID: 39832659
Have a look here and try to find the best connection string that matches your environment:

http://www.connectionstrings.com/
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:amstoots
ID: 39848182
I've requested that this question be deleted for the following reason:

1
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39848183
what was wrong with the answers I provided?
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39853432
Both answers I provided were resources that did exactly what the OP was asking for
0
 

Author Closing Comment

by:amstoots
ID: 39853436
thank you
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now