2

There are a lot of questions here asking "how to export from SQL Server to Excel", but every answer, together with stuff I know, is giving me problems (64bit system here).

We have a developer that monthly, runs a query 6 times (changing the year from 2010, 2011 etc.). Then, he copies and pastes the results into an Excel spreadsheet.

I'm having problems exporting this data from SQL Server 2014 to Excel.

This is the query:

SELECT 
R.numero,
R.ano,
R.data 'data abertura',
R.datahora_conclusao  'data conclusão',
R.datahora_emissao 'data emissão',
S.[status],
(ins.INSTITUTO +
            ' - ' + dir.DIRETORIA + 
            (CASE WHEN NOT nuc.NUCLEO IS NULL THEN  ' - ' + nuc.NUCLEO  ELSE '' END) +
            (CASE WHEN NOT equ.EQUIPE IS NULL THEN ' - ' + equ.EQUIPE ELSE '' END)) AS UNIDADE,
N.codigo 'codigo natureza exame',
Case E.cod_estadopreservacao when 1 then 'Preservado' when 2 then 'Não Preservado' else 'Laborátorio' end 'Local Exame',

u.nome 'Perito Designado'
FROM tbReps R 
LEFT Join tbExames E on E.cod_rep = R.cod_rep
LEFT JOIN tbUsuarios U ON U.cod_usuario = R.cod_perito_primeiro
LEFT JOIN tbUnidades               AS uni WITH(NOLOCK) ON R.cod_unidade = uni.cod_unidade
LEFT JOIN lstUnidadesInstitutos AS ins WITH(NOLOCK) ON uni.cod_instituto = ins.cod_instituto
LEFT JOIN lstUnidadesDiretorias AS dir WITH(NOLOCK) ON uni.cod_instituto = dir.cod_instituto AND uni.cod_diretoria = dir.cod_diretoria
LEFT JOIN lstUnidadesNucleos AS nuc WITH(NOLOCK) ON uni.cod_diretoria = nuc.cod_diretoria AND uni.cod_nucleo = nuc.cod_nucleo 
LEFT JOIN lstUnidadesEquipes AS equ WITH(NOLOCK) ON uni.cod_nucleo = equ.cod_nucleo and uni.cod_equipe = equ.cod_equipe
LEFT JOIN lstNaturezasExame N ON R.cod_naturezaexame = N.cod_naturezaexame
LEFT JOIN lstStatus S ON S.cod_status = R.cod_status
WHERE  year(r.data) = 2016 and r.data <= '2016-04-30 23:59:59' 
order by data

I'm trying to think of something to import this directly into an Excel file, separated by year. I thought it would be a good idea to create 6 tables (2010...2011...2012 ) and use the Task > Export process, and then delete them. But I think there's a better (and more correct) way to do this.

Which OLE.DB provider should I use? Is there a script to do this? Some procedure that I don't know?

I'm using with (nolock) everywhere for test purposes only.

6

PowerShell is the way to go:

# Original Author : Bill Fellows (http://billfellows.blogspot.com/2011/03/powershell-export-query-to-csv.html)
# Modified by     : Kin Shah (http://dba.stackexchange.com/users/8783/kin)
                    # Added the -NoTypeInformation to remove "#TYPE System.Data.DataRow" from the header !

# http://www.vistax64.com/powershell/190352-executing-sql-queries-powershell.html
$server = "servername\INSTANCE1"  #YourServer Name e.g. server1\instance1
$database = "your_db_name"    #your database name
$query = "select some columns from table inner join other table on table.id = othertable.id"

# powershell raw/verbatim strings are ugly
# Update this with the actual path where you want data dumped
# change here to the path that you want
$extractFile = @"
C:\SQLtoExcel.csv  
"@

# Use windows authentication !!
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# dump the data to a csv
# http://technet.microsoft.com/en-us/library/ee176825.aspx
# -NoTypeInformation will remove the first #TYPE System.Data.DataRow
$DataSet.Tables[0] | Export-Csv $extractFile  -NoTypeInformation

See also:

Multi-Tabbed Excel Through Powershell by Kevin Feasel

2

I'd probably take a short solution to this:

  1. Create a SQL Agent Job
  2. Create a step using sqlcmd to BCP out the query and save it as a .csv file for relevant years
  3. Send to developer

If he wants each year in a separate worksheet or something, that's something he can make a macro in Excel for - just giving a dump of the actual data should be sufficient enough from your area of responsibility.

You could also use SSIS, but if I'll assume you don't already have it all set up otherwise you'd have chosen that in the first place. Setting up the environments and catalogs just for this job would probably be overkill, so if its a one-off then the above should suffice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.