Exporting Data/1st three lines NOT CSV..

I have a specific format that I need to export data to. The first three lines of the document MUST be in the form of:

ascii
,
klg, Eastern Daylight Time,1,1
PineGrove,0,2005/10/01,00:00,1,1.75,192
PineGrove,0,2005/10/01,00:05,1,1.75,192
Pinegrove,0,2005/10/01,00:10,1,1.75,192

If I set this up in DTS and do an export, it puts commas after ascii - which I cannot have.

I've also tried using two data sources and exporting twice (hoping to append), however, one just overwrites the other.

Anyone have any ideas?? :o

Thanks in advance,
KristaIs this from a table?

Read the sticky at the top|||A couple of ideas...

You can create two text files, one with the headers. Then use the dos copy command to make one file.

copy file1+file2 file3

Another option is to create a temporary staging table. It can have one large varchar column that contains the data. Then export from this table. This option requires a little work.

Bill|||A couple of ideas...

You can create two text files, one with the headers. Then use the dos copy command to make one file.

copy file1+file2 file3 [B][I]

Bill

YOU'RE AWESOME!! I WAS TRYING TO REMEMBER HOW TO DO THIS EARLIER TODAY!! THANKS SO MUCH!!!!!!! :D|||You're welcome.

Check your PM.|||What's PM?|||Private Messagesql

Export to Specific Filename

I have a Requisition Report that generates the details for a specific
Requisition ID. This is done by passing a paramater to the report. The
end-user downlads the report as a PDF and saves it to his PC. The problem is
that the default name when he tries to save is the name of the report
(ReqForm.pdf) and he has to manually append the Requisition ID to the
filename (ReqForm-5611.pdf)
I was wondering if there was a way to incorporate the paramater that is
passed to the report into the filename when he goes to save it.
ThanksI don't think so because the report has already been rendered. Have user
open it instead of save and let them name it is one option. The other might
be to render the report as a PDF in your application and maybe you can set
the file name there in a parameter (though I have never done this). HTH.
David
"tigermike" <tigermike@.discussions.microsoft.com> wrote in message
news:DA345A17-0D0C-40FC-9DB7-20B75E4E934F@.microsoft.com...
>I have a Requisition Report that generates the details for a specific
> Requisition ID. This is done by passing a paramater to the report. The
> end-user downlads the report as a PDF and saves it to his PC. The problem
> is
> that the default name when he tries to save is the name of the report
> (ReqForm.pdf) and he has to manually append the Requisition ID to the
> filename (ReqForm-5611.pdf)
> I was wondering if there was a way to incorporate the paramater that is
> passed to the report into the filename when he goes to save it.
> Thanks|||Thanks.
How would I render it in my own application? Can you send me some links?
thanks|||You might start here:
http://msdn2.microsoft.com/en-us/library/ms153586.aspx
"tigermike" <tigermike@.discussions.microsoft.com> wrote in message
news:03DA91F7-DA1B-4E05-B9C7-E13344EA5D69@.microsoft.com...
> Thanks.
> How would I render it in my own application? Can you send me some links?
> thanks

Export to excel using template

Is it possibile to export a report to excel with specific template?
I can set an excel template per each report or for every reports?
thx
MarcoNot that i know of. This is similar to CSS requests on HTML reports.
Currently, each report is its own entity, so when it comes to preserving
common look and feel, we can use a template *report* as a starting point, or
I guess create a custom rendering extension. The latter is not high on my
to-do list.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Marco Rizzi" <MarcoRizzi@.discussions.microsoft.com> wrote in message
news:34BB6E92-4B75-4279-AC3A-354C77510E6D@.microsoft.com...
> Is it possibile to export a report to excel with specific template?
> I can set an excel template per each report or for every reports?
> thx
> Marco|||Thx Jeff
Where can I find a sample of a custom rendering extension of excel?
Marco Rizzi
--
Program Manager
OmegaWeb S.r.l.
"Jeff A. Stucker" wrote:
> Not that i know of. This is similar to CSS requests on HTML reports.
> Currently, each report is its own entity, so when it comes to preserving
> common look and feel, we can use a template *report* as a starting point, or
> I guess create a custom rendering extension. The latter is not high on my
> to-do list.
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Marco Rizzi" <MarcoRizzi@.discussions.microsoft.com> wrote in message
> news:34BB6E92-4B75-4279-AC3A-354C77510E6D@.microsoft.com...
> > Is it possibile to export a report to excel with specific template?
> > I can set an excel template per each report or for every reports?
> >
> > thx
> > Marco
>
>|||I'm not so sure you want to go that path (custom rendering extension).
There's not any documentation available yet, and those in the know say it
takes at least two months with some serious handholding from Microsoft.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_security_87oi.asp
In the meantime you'll probably be better off taking the output from another
extension (CSV? XML?) and massaging that into Excel. Or you can create a
report with a simple table layout that looks just like you want it to look
in Excel.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Marco Rizzi" <MarcoRizzi@.discussions.microsoft.com> wrote in message
news:E60CF0B2-AC00-468B-8842-9FC08D95DEBD@.microsoft.com...
> Thx Jeff
> Where can I find a sample of a custom rendering extension of excel?
> Marco Rizzi
> --
> Program Manager
> OmegaWeb S.r.l.
> "Jeff A. Stucker" wrote:
>> Not that i know of. This is similar to CSS requests on HTML reports.
>> Currently, each report is its own entity, so when it comes to preserving
>> common look and feel, we can use a template *report* as a starting point,
>> or
>> I guess create a custom rendering extension. The latter is not high on
>> my
>> to-do list.
>> Cheers,
>> --
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Marco Rizzi" <MarcoRizzi@.discussions.microsoft.com> wrote in message
>> news:34BB6E92-4B75-4279-AC3A-354C77510E6D@.microsoft.com...
>> > Is it possibile to export a report to excel with specific template?
>> > I can set an excel template per each report or for every reports?
>> >
>> > thx
>> > Marco
>>

Export to Excel from SQL Server 2000

I was wondering if anyone knew how I could export data from SQL Server to a specific Excel worksheet with an Excel file, such as "Sheet2" or "Sheet3" for example, instead of dropping the data automatically in to Sheet1.
Thank you!
DaveI was wondering if anyone knew how I could export data from SQL Server to a specific Excel worksheet with an Excel file, such as "Sheet2" or "Sheet3" for example, instead of dropping the data automatically in to Sheet1.

Thank you!

Dave

Use Import/Export wizard and specify Sheet2$ as the destination.|||Look into setting up an ODBC connection...

You can create the proper view (or sproc) in SQL Server (and even pass parameters) and setup Excel to pull the data from SQL Server when right-click the external data source and choose to Refresh Data.

I've used it for yours to distribute real-time reporting solutions. Just created the worksheet, set everybody up with the ODBC and they can refresh as often as they like. I use it to provide up to the minute performance on manufacturing facilities. A real eye-opener for my clients.

Let me know if you want more specific information. Best of luck,

Alex

export table to excel

how should I go about exporting a couple of columns of data from a specific database table to an excel spreadsheet stored locally on a client machine (the one the script is being executed from). I am using SQL server 2000 SP3.

I'm fairly new to using SQL server, and not experienced at all with Transact-SQL.

using enterprise manager from client manager... there is export task wizard... you can easily achieve this.....

mandip

|||while i can do this from my pc, there are some clients that will need to be doing the same thing who will not have enerprise manager on their computer...|||

If these other clients have MS Access, you can link the SQL Server table to Access, and they can use the Export feature within Access. (Right-click on the table name, choose "Export...", etc.) You could also write an Access query if you wish to control the columns that will be exported, as well as the column titles and sort order.

Dan

|||they may or may not have access, this would also require me show them how to do this (show users who are not reliably familiar with access or mysql). is it possibe to create an application or script that could do this?|||

I'm sure that you could write a .NET web application. Such web applications written by my coworkers typically call stored procedures that contain the desired SELECT statement. As such, the stored procedure returns a table. If multiple SELECT statements are in the stored procedure, it returns multiple tables.

You may need additional software to convert the SELECT output to an Excel spreadsheet. Perhaps you can do that with SSIS. You might consider asking that in the SSIS forum.

What software can you expect users to have on their PCs?

|||they will be using xp machines with .netframework and the basic microsoft office supplies (word, excel, outlook, maybe access), generally office 2003|||

You may wish to explore using BCP.exe (perhaps installing the BCP utility on the users computers. Then you could create a batch file for the users to run.

With BCP running locally, you can easily output to a local file.

Otherwise, you could create a Stored Procedure that uses SLQCmd.exe, BCP, and XCopy to create file on the server, and then move that file to the local computer. However, this option will be more problematic due to network security concerns.

|||

Arnie,

Is BCP a "free" utility, or are there licensing considerations concerning placing BCP on all the users' computers?

Dan

|||

BCP can be used on any properly licensed SQL Server, and it can be used by anyone having access to that server and either a CAL, or the server accessed is covered with a processor license.

As far as I am aware, it is freely distributable to licensed users in your organization. However, for the definitive answer related to licensing questions, call the licensing folks:

Licensing –Microsoft, Contact
(800) 426-9400

From: http://www.microsoft.com/sql/howtobuy/faq.mspx

Do I need a separate license to run the SQL Server tools and analysis services? A.

No, a separate license is not required. However, any device that has SQL Server tools or technologies installed must have a valid SQL Server license.

|||

Wraithzshadow,

1) Create an ODBC on the local PC pointing to the SQL server.

2) Open Excel spread sheet

3) Select Data > import external data > new database query

4) Select the ODBC created in step one, Click OK

5) Select Table or view from the list, Click Add then Click Close

6) Select the columns needed from the table

7) Click the return data icon (door with arrow)

The data is displayed in the spread sheet. The good thing about this is you can update the data by right clicking into any cell containing data and selecting refresh data or by setting the query to refresh on open.