title: SQL extract data to Excel using Powershell
body: <p>I want to extract data from SQL server to a new excel file using powershell . For small data set my code works but some tables has more than 100.000 rows and this will take ages. The reason why I don't use the utility in SQl server is because I want to extract mutilple tables.
  Is there a way to optimize my script to export big tables to excel? or is there another way to do this?</p>
  
  <p>I'm using the following script</p>
  
  <pre><code>## ---------- Working with SQL Server ---------- ##
  
  ## - Get SQL Server Table data:
  $SQLServer = 'server';
  $Database = 'database';
  $SqlQuery = @'   Select top 10 *   from database.dbo.table   '@;
  
  ## - Connect to SQL Server using non-SMO class 'System.Data':
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
  $SqlConnection.ConnectionString = `
  "Server = $SQLServer; Database = $Database; Integrated Security = True";
  
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
  $SqlCmd.CommandText = $SqlQuery;
  $SqlCmd.Connection = $SqlConnection;
  
  ## - Extract and build the SQL data object '$DataSetTable':
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
  $SqlAdapter.SelectCommand = $SqlCmd;
  $DataSet = New-Object System.Data.DataSet;
  $SqlAdapter.Fill($DataSet);
  $DataSetTable = $DataSet.Tables["Table"];
  
  
  ## ---------- Working with Excel ---------- ##
  
  ## - Create an Excel Application instance:
  $xlsObj = New-Object -ComObject Excel.Application;
  
  ## - Create new Workbook and Sheet (Visible = 1 / 0 not visible)
  $xlsObj.Visible = 0;
  $xlsWb = $xlsobj.Workbooks.Add();
  $xlsSh = $xlsWb.Worksheets.item(1);
  
  ## - Build the Excel column heading:
  [Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;
  
  ## - Build column header:
  [Int] $RowHeader = 1;
  foreach ($ColH in $getColumnNames)
  {
  $xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
  $xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;
  $RowHeader++;
  };
  
  ## - Adding the data start in row 2 column 1:
  [Int] $rowData = 2;
  [Int] $colData = 1;
  
  foreach ($rec in $DataSetTable.Rows)
  {
  foreach ($Coln in $getColumnNames)
  {
  ## - Next line convert cell to be text only:
  $xlsSh.Cells.NumberFormat = "@";
  
  ## - Populating columns:
  $xlsSh.Cells.Item($rowData, $colData) = `
  $rec.$($Coln.ColumnName).ToString();
  $ColData++;
  };
  $rowData++; $ColData = 1;
  };
  
  ## - Adjusting columns in the Excel sheet:
  $xlsRng = $xlsSH.usedRange;
  $xlsRng.EntireColumn.AutoFit();
  
  ## ---------- Saving file and Terminating Excel Application ---------- ##
  
  ## - Saving Excel file - if the file exist do delete then save
  $xlsFile = `
  "C:\path\file.xls";
  
  if (Test-Path $xlsFile)
  {
  Remove-Item $xlsFile
  $xlsObj.ActiveWorkbook.SaveAs($xlsFile);
  }
  else
  {
  $xlsObj.ActiveWorkbook.SaveAs($xlsFile);
  };
  
  ## Quit Excel and Terminate Excel Application process:
  $xlsObj.Quit(); (Get-Process Excel*) | foreach ($_) { $_.kill() };
  
  ## - End of Script - ##
  </code></pre>