Solved

automatically export sql query

Posted on 2011-03-15
4
502 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now