Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

automatically export sql query

Posted on 2011-03-15
4
Medium Priority
?
513 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

610 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