Solved

automatically export sql query

Posted on 2011-03-15
4
510 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 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

691 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