?
Solved

automatically export sql query

Posted on 2011-03-15
4
Medium Priority
?
512 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 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