Solved

automatically export sql query

Posted on 2011-03-15
4
506 Views
Last Modified: 2012-06-21
I am attempting to automatically export an sql query to a comma delimited csv file with headers. What is the best, easiest or most efficient way to do complete this.

I have attempted using SQLCMD but it creates a separation line between the headers and the data which I can't have.
0
Comment
Question by:broberc6
  • 3
4 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35142446
Use MS Excel.  Go to the Data tab and import the data from your table or query.  Then just Save As csv.

Excel Data From Other Sources
I hope this helps.
0
 

Author Comment

by:broberc6
ID: 35142475
Sorry, I should have specified that I want to schedule this to run the first of the month, exporting it to a comma delimiited text file with csv extension. This way a user can use the file whenever they want and not relay on IT to create it.
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 125 total points
ID: 35142739
Ok. I don't think there is a way to remove the ----- line from SQLCMD when the headers are included.

As a workaround, you can try the following bat file.  It uses SQLCMD to create two files one with the headers (Product1.csv) and another one with the data (Product2.csv), then at the end merges both on Product.csv.

@echo off
sqlcmd -S .\SQLEXPRESS -d Northwind -E -Q "SET NOCOUNT ON; select top 1 * from Products" -o "Products1.csv" -s"," -w 700
sqlcmd -S .\SQLEXPRESS -d Northwind -E -Q "SET NOCOUNT ON; select * from Products" -o "Products2.csv" -s"," -w 700 -h-1

REM Get the header line
for /F "eol=- tokens=*" %%i in (Products1.csv) do (
	set headers=%%i
	GOTO :MERGE
)

:MERGE

REM Update the file to only contain the header line
echo %headers% >Products1.csv

REM Merge the headers and the data
copy /y /b Products1.csv+Products2.csv Products.csv

REM Clean up
del Products1.csv Products2.csv

Open in new window


I hope this helps.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35147885
Here is a different version running sqlcmd just once.

@echo off
sqlcmd -S .\SQLEXPRESS -d Northwind -E -Q "SET NOCOUNT ON; select * from Products" -o "Products1.csv" -s"," -w 700 -h2147483647

REM Get the header line
for /F "eol=- tokens=*" %%i in (Products1.csv) do (
	set headers=%%i
	GOTO :MERGE
)

:MERGE

REM Write header
echo %headers% >Products.csv

REM Merge the headers and the data
more +2 Products1.csv >>Products.csv

REM Clean up
del Products1.csv

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

808 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