Export Data in a specific format

[Lawrence 007]

I have a transaction table that I need to export for our financial system to import. It has a lot of zero's and a lot of spaces in the requested file.

Is there a way to do that and if there is how?

Can someone please help me in explaining how to extract data to a txt file as well. Here is my SQL statement thus far:

SELECT AccountNumber, CustomerID, Sum(CheckTotal) As CKT, Sum(PSD.Quantity) as QTY, Sum(ItemPrice * PSD.Quantity) As ItPrice, Sum(FCharge) as FCharge, Sum(SCharge) as SCharge, Sum((ItemPrice * PSD.Quantity) + FrankingCharge + ServiceCharge) As Total From PSD INNER JOIN Customer ON PSD.CustomerId = Customer.ID INNER JOIN Item ON PSD.ItemId = Item.ID Where PSD.Datecreated Between '12/01/2006 01:00:00 AM' and '12/31/2006 11:00:00 AM' Group By AccountNumber

Thanks

[AricC]

What language are you using to do this?

[almaz]

If you don't need to incorporate the export/import functionality in application - use Data Transformation Services (DTS).

[Lawrence 007]

Guys,

I was away for the holiday, Sorry.

The language I am using is VB.

I am not familiar with DTS. Can you explain how that works please.

Thanks!!

[almaz]

See here: data transformation services

[navamnk]

create a view (based on your query + include some more where condition to eliminate zeros & spaces) and then directly insert it to the new table.

like,

insert into ...(finance. table)
select ....
from (new view)

[navamnk]

use OSQL command line utility to get all the result in a txt file.

open command prompt and type OSQL /? for more options

[Lawrence 007]

That sounds great, but as I am new to VB I might need some help on how to call that through vb. Any Ideas?