How to Get Export Output in "Real" CSV Format in SQL Server Management Studio

Export query result to .csv file in SQL Server 2008

1. Open SQL Server Management Studio
2. Go to Tools > Options > Query
   Results > SQL Server > Results To Text
3. On the far right, there is a drop
   down box called Output Format
4. Choose Comma Delimited and click OK

Here's a full screen version of that image, below

This will show your query results as comma-delimited text.

To save the results of a query to a file: Ctrl + Shift + F

How to export the result of an SQL query as a .csv file in SQL Management Studio (using code)

You can use the code below, but just make sure you already have a version of your csv file saved to your documents/desktop/etc.

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [yourfile.csv]')
SELECT Field1, Field2, Field3, Field 4, Field 5 FROM DatabaseName

Edit: If you are having an issue with Microsoft.ACE.OLEDB.12.0 not being registered check out below link.

https://blog.sqlauthority.com/2015/06/24/sql-server-fix-export-error-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/

Example:

    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=C:\Users\My Documents;HDR=YES;FMT=Delimited','SELECT * FROM [EmployeeInfo.csv]')
        SELECT
FirstName
,LastName
,DOB
 FROM Employees

SQL Server Management Studio 2012 - Export all tables of database as csv

The export wizard allows only one at a time. I used the powershell script to export all my tables into csv. Please try this if it helps you.

$server = "SERVERNAME\INSTANCE"
$database = "DATABASE_NAME"
$tablequery = "SELECT schemas.name as schemaName, tables.name as tableName from sys.tables inner join sys.schemas ON tables.schema_id = schemas.schema_id"

#Delcare Connection Variables
$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 = $tablequery
$command.Connection = $connection

#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()



# Loop through all tables and export a CSV of the Table Data
foreach ($Row in $DataSet.Tables[0].Rows)
{
    $queryData = "SELECT * FROM [$($Row[0])].[$($Row[1])]"

    #Specify the output location of your dump file
    $extractFile = "C:\mssql\export\$($Row[0])_$($Row[1]).csv"

    $command.CommandText = $queryData
    $command.Connection = $connection

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

    $DataSet.Tables[0]  | Export-Csv $extractFile -NoTypeInformation
}

Thanks

How to export data as CSV format from SQL Server using sqlcmd?

You can run something like this:

sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" 
       -o "MyData.csv" -h-1 -s"," -w 700

• -h-1 removes column name headers from the result
  
• -s"," sets the column seperator to ,
  
• -w 700 sets the row width to 700 chars (this will need to be as wide as the longest row or it will wrap to the next line)

---

Related Topics