Dynamically Importing Data from Excel Using PowerShell

Since I spend a lot of time with ETL (Extraction, Transform, Load) processes, one of the most common tasks I face is importing data from an Excel spreadsheet into SQL Server. For many reasons, I’ve never been particularly happy with bcp, BULK INSERT or SSIS for this task. Recently I started experimenting with PowerShell for ETL and have been happy with the results.

Using PowerShell, I came across a simple way to accomplish a task for which I’ve never found a satisfactory solution: dynamically creating a table based on the columns in an Excel spreadsheet. Utilizing the powerful SMO assemblies, and the .NET DataTable data structure, the following script reads the structure of an Excel spreadsheet, generates a table in SQL Server and imports the data into the table.

[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)

Resources

ACE – Microsoft Access Database Engine : This is required for using the ACE driver if you do not have Office installed.

Advertisements

8 Responses to Dynamically Importing Data from Excel Using PowerShell

  1. Tim Radney says:

    Nice post. Welcome to blogging. It can become addicting.

  2. Sean DeYoung says:

    I think this is really really good. Only I am having some problems with things being registered. I will work on fixing that because I like this script very much

  3. Anonymous says:

    Script works, but unfortunately I have an encoding issue (Greek characters). Any ideas how to solve that?

  4. rowdy says:

    Thank you very much!

  5. So wonderful script. It helped me a lot.
    Thank you very much!

  6. mbourgon says:

    Howdy. Just came across this, and it definitely solves a need I have (hell, that most of us have – dealing with Excel in SQL Server sucks). Time to go test it out – hopefully it works with the newer versions of Excel. The question I have for you is: 3 years later, how’s it working for you? Is there a newer version, or changes you made to make it better? Thanks!~

    • jboulineau says:

      I haven’t touched it in a while. Shortly after I posted the code I moved to a position in which I’m no longer responsible for ETL, so I haven’t had the need to work on it. I’m glad you still find it useful!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: