Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Need MSSQL powershell Script

Posted on 2014-02-03
8
285 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 33

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 33

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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

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

1
0
 
LVL 33

Expert Comment

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Utilizing an array to gracefully append to a list of EmailAddresses
Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

840 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