June 3, 2003 at 2:46 am
Hi All,
I have a Table in SQL server Database. I export a set records from the SQL Table to Excel using DTS using SQL query.
Can I Export those records to Excel with Formula fields ?
Eg: Col 1 -- Qty
Col 2 -- Price
Col 3 -- Amount
Can I have Col 2 & Col 3 with Formula inserted in the Excel output ?
Thanks for your help in advance.
Best Regards,
June 6, 2003 at 8:00 am
This was removed by the editor as SPAM
June 11, 2003 at 9:52 am
The data bulk load operation in DTS is designed to move rows of data. It does not have capibility to perform calculations on the data ( thank god it does not or performance would have been horrible). I think you have 2 choices here ; a) do your caluciatons on source side or b) Precreate your execel file and put in the formaulas for calculated columns. DTS moves the source columns and cacluation will done automatically within excel when the data come in. Haven't tried out the latter. Hope this helps.
June 12, 2003 at 1:40 am
Hello sunkash,
quote:
Can I Export those records to Excel with Formula fields ?Eg: Col 1 -- Qty
Col 2 -- Price
Col 3 -- Amount
Can I have Col 2 & Col 3 with Formula inserted in the Excel output ?
I haven't done this before and I'm not sure if Excel will recognize the formula but you can put together your output like
=A1+A2 (Excel also recognizes the + and - signs as the beginning of a formula)
What you need to do in this case is keep track of the cell numbering you are writing data to, eg. A1, A2, B1, B2......
I remember I tried to export Access varchar fields containing a DDE command to Excel. It didn't work at once. I had to place the cursor in each cell, hit F2 and ENTER and then it worked.
Good luck!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply