Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Batch file to export SQL query?

Author  Topic 

chef423
Starting Member

15 Posts

Posted - 2014-09-24 : 15:03:31
SELECT c.cust_fullname AS Customer,
c.cust_membership_id AS Account#,
Sum(t.c_amount) AS ChargeTotal,
t.i_ticket_id AS Ticket#,
t.s_credit_tran_type AS AccountType
FROM Transactions AS t
INNER JOIN Customers AS c
ON t.s_ref_num = c.cust_id
WHERE s_credit_tran_type = 'House Account'
AND b_cancel = 0
AND t.[dt_when] > = dbo.dwf_beginofday_for_day(getdate())
AND t.[dt_when] < = dbo.dwf_endofday_for_day(getdate())
GROUP BY c.cust_fullname,
c.cust_membership_id,
t.i_ticket_id,
t.s_credit_tran_type

Here is my batch code:
SQLCMD -S.\dinerware -d BWG -i "C:\DWBACKUP\HouseAccount.sql" -o "C:\DWBACKUP\HouseAccount.xls"

BUT the formatting is not what I need...I need the output to look just like an Excel spreadsheet with the headers preserved.

I need to import this into Quickbooks..

Thanks to anyone who can help

Chris

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-24 : 15:20:14
Here you go: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

chef423
Starting Member

15 Posts

Posted - 2014-09-24 : 15:58:31
I need to write all that to put data, correctly formatted, into an Excel spreadsheet?

Thanks for the reply but there has to be an easier way...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-24 : 17:03:47
You could use an SSIS package. If it's a one-time or very infrequently needed, then the import/export wizard can do it too.

Bcp can do it, but the header makes it a little challenging. Still doable.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

BBarn
Starting Member

14 Posts

Posted - 2014-09-25 : 15:58:15
Why not build the query and use the Data Connection in Excel to extract the data directly into Excel?
Go to Top of Page
   

- Advertisement -