Exporting query result into excel file

Hi i am newbie . I want to transfer the query result into excel file throgh asp code. can any one help me out.

Also how can i automate my routing task that is data in excel to be updated in the table which is in SQL server. Say x.xls is the daily file maintaned by me and i want y table in sql server to be appended once this file is saved.Writing a macro in Excel could be one of the solutions!!!sql

Exporting Query Analyzer data with column headers...?

Hi,

Does anyone out there know how to do this? I've been banging head for
awhile now trying to answer this seemingly simple question.

tia,

Mike[posted and mailed, please reply in news]

Ixnay (mscgloss@.yahoo.com) writes:
> Does anyone out there know how to do this? I've been banging head for
> awhile now trying to answer this seemingly simple question.

Export to what?

There are two ways that you may want to investigate. Both are found under
Tools->Options->Results.

Here you can select output to file.

You can also for output to text select which delimiter to use. For instance,
you can select tab as delimiter. Then in the results tab press CTRL-A
followed by CTRL-C, and if Excel where you want the data, you go there
and press CTRL-V in cell A1.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I had the same problem. Then I discovered that if you make the query
in Enterprise Manager and copy THOSE results, you get the column
headers.

Go figure...

On 23 Jan 2004 12:44:45 -0800, mscgloss@.yahoo.com (Ixnay) wrote:

>Hi,
>Does anyone out there know how to do this? I've been banging head for
>awhile now trying to answer this seemingly simple question.
>tia,
>Mike|||Erland, just want to thank you -- output to text with the tab
delimiter works exactly as you stated. I much prefer writing SQL in
Query Analyzer (as opposed to Enterprise Manager), so this is going to
be my method from now on! :)

On Fri, 23 Jan 2004 22:56:19 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>[posted and mailed, please reply in news]
>Ixnay (mscgloss@.yahoo.com) writes:
>> Does anyone out there know how to do this? I've been banging head for
>> awhile now trying to answer this seemingly simple question.
>Export to what?
>There are two ways that you may want to investigate. Both are found under
>Tools->Options->Results.
>Here you can select output to file.
>You can also for output to text select which delimiter to use. For instance,
>you can select tab as delimiter. Then in the results tab press CTRL-A
>followed by CTRL-C, and if Excel where you want the data, you go there
>and press CTRL-V in cell A1.

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob Boles
This isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:

> I have a query that exports to an Excel spreadsheet. One of the columns in a
> long text field (defined as ntext (16)). When I run the query and export to
> Excel the text is truncated at 255 characters. How do I get all the text to
> export?
> Thanks,
> Bob Boles

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob BolesThis isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:
> I have a query that exports to an Excel spreadsheet. One of the columns in a
> long text field (defined as ntext (16)). When I run the query and export to
> Excel the text is truncated at 255 characters. How do I get all the text to
> export?
> Thanks,
> Bob Boles

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob BolesThis isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:

> I have a query that exports to an Excel spreadsheet. One of the columns in
a
> long text field (defined as ntext (16)). When I run the query and export t
o
> Excel the text is truncated at 255 characters. How do I get all the text t
o
> export?
> Thanks,
> Bob Boles

Exporting Datas to Excel File

I have a server where I have installed SQL Server.I am running a Visual
Basic Software in a Client computer.I want to execute a Sql Query which
exports all the datas of a sql server table to that client machine as a
MS-Excel file.I have done that query with MS-Access, but that's not
working with SQL Server.
Please reply meHi
The best way would be to use DTS for this if you are using SQL 2000 or SSIS
if you are using SQL 2005. You can use the Import/Export Wizard to create a
package and job that will do it for you if you don't want to start from
scratch. There is plenty of information on DTS/SSIS in books online, but
there are also additional information at http://www.sqldts.com
John
"retheeshnewage@.gmail.com" wrote:

> I have a server where I have installed SQL Server.I am running a Visual
> Basic Software in a Client computer.I want to execute a Sql Query which
> exports all the datas of a sql server table to that client machine as a
> MS-Excel file.I have done that query with MS-Access, but that's not
> working with SQL Server.
> Please reply me
>

Exporting Datas to Excel File

I have a server where I have installed SQL Server.I am running a Visual
Basic Software in a Client computer.I want to execute a Sql Query which
exports all the datas of a sql server table to that client machine as a
MS-Excel file.I have done that query with MS-Access, but that's not
working with SQL Server.
Please reply me
Hi
The best way would be to use DTS for this if you are using SQL 2000 or SSIS
if you are using SQL 2005. You can use the Import/Export Wizard to create a
package and job that will do it for you if you don't want to start from
scratch. There is plenty of information on DTS/SSIS in books online, but
there are also additional information at http://www.sqldts.com
John
"retheeshnewage@.gmail.com" wrote:

> I have a server where I have installed SQL Server.I am running a Visual
> Basic Software in a Client computer.I want to execute a Sql Query which
> exports all the datas of a sql server table to that client machine as a
> MS-Excel file.I have done that query with MS-Access, but that's not
> working with SQL Server.
> Please reply me
>

Exporting data with dynamic fields

Hi,

I have one Query Builder application where user creates queries and generates output with required fields.Then user can export the same to excel (Using farPointSpread properties)
But if no. of records goes beyond 10K it goes too slow or even user machine hangs up. I want to give proper export facility. Can it be given using DTS?

I tried DTS (.vbs) file. but for random field selection it did not work.DTS package asks for new Transactions to be built for every change in Source table structure. It first creates the table in the destination databse and then exports the data.
If I try modify the VB code generated by the package to match with new Source table it does'nt work.

Can anyone tell me what should I do for export the data dynamically i.e. export the tables with differrent structres?

Thanx in advance.|||You should do a helluvalotta programming. DTS sucks, and I don't believe most people on this forum use it for much more than directly transfering defined recordsets.

Exported queries to excel an spreadsheet file

Is it possible to export the results of a query using SQL Server query
manager to an excel spreadsheet file.

Thanks in advance.Hi

This depends on what you are actully wanting to do!!
If you are using Query Analyser in grid mode then you can select the whole
result set and cut/paste them into excel. Utilities like BCP can create
comma separated files which excel will open, or possibly you could go
through the XML route. DTS can also be used to do this and may be the most
suitable if you wish to automate the process.

Excel files can be created as a linked server and data pumped into them.

John

"kjc" <ksitron@.elp.rr.com> wrote in message
news:QdJsd.75465$g21.19155@.fe1.texas.rr.com...
> Is it possible to export the results of a query using SQL Server query
> manager to an excel spreadsheet file.
>
> Thanks in advance.|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are using Query Analyser in grid mode then you can select the whole
> result set and cut/paste them into excel.

And if you want the column headers as well, go with Text mode, and under
Tools->Options change the delimiter to tab. Then run query, cut and paste.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Exported queries to excel an spreadsheet file

Is it possible to export the results of a query using SQL Server query
manager to an excel spreadsheet file.

Thanks in advance.kjc,

If you want to go the automated route, take a look at Gadami:
http://www.kripsoft.com. It emails sql output in excel attachments.
-Krip

Export XML data - stored procedure

I have an SQL query that can generate XML file. However, it does not seemed to work as a stored procedure. Basically, i want to be able to generate an XML file based on the data stored in a SQL table and be able to do this using script...
Also, if there is a script (or stored procedure) that will allow me to generate the XML file with the specification of an XML schema would even be better...

e.g Sample XML file required...
<Person>
<Name>Raymond</Name>
<NickName>The Legend</NickName>
</Person>
<Person>
<Name>Peter</Name>
<NickName>The King</NickName>
</Person>

sp_configure'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure'Web Assistant Procedures', 1;
GO
RECONFIGURE
GO
sp_makewebtask @.outputfile='C:\MyExportFile.xml',
@.query='SELECT * FROM MyTableName for XML AUTO, TYPE, ELEMENTS',
@.templatefile='C:\Template.tpl'

Try these two links for code samples including generating the XML file from a database table, this is assuming you are using SQL Server 2000. If you are using SQL Server 2005 all the code you need is in the BOL (books online) because XML is native to SQL Server 2005. Hope this helps.

http://forums.asp.net/1026295/ShowPost.aspx

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

Export Wizard disturb the order of data while exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.

Please give me the solutions.

If the only diffrence in the tables is the order of the rows I would not call it incorrect data. If you need the data to be shown in a especific order you need to use Order By clause in your query

|||i am using order by in my query . Data is correct in Access but order is not correct.|||

Dear All,

Thankyou for your replies.

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thankx again for your replies. However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

Export Wizard disturb the order of data while exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.

Please give me the solutions.

If the only diffrence in the tables is the order of the rows I would not call it incorrect data. If you need the data to be shown in a especific order you need to use Order By clause in your query

|||i am using order by in my query . Data is correct in Access but order is not correct.|||

Dear All,

Thankyou for your replies.

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thankx again for your replies. However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

Export Wizard disturb the order of data while exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.

Please give me the solutions.

If the only diffrence in the tables is the order of the rows I would not call it incorrect data. If you need the data to be shown in a especific order you need to use Order By clause in your query

|||i am using order by in my query . Data is correct in Access but order is not correct.|||

Dear All,

Thankyou for your replies.

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thankx again for your replies. However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

Export Wizard disturb order of data during exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect order of data.

Please give me the solutions.

Speaking logically, sets and tables don’t have order. Order can be specified in a SELECT. Order can be the result of a clustered index but typically there is no guarantee of order without an explicit ORDER BY.

This is generally true of all Relational Databases.

Did you try placing an index on the Access table?

|||

True, I believe this more a call from Access 2003 settings to see how data is displayed.

Also check the code in Access about getting this data display.

|||

Dear All,

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thank you all of you for your replies.

However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

Export Wizard disturb order of data during exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect order of data.

Please give me the solutions.

Speaking logically, sets and tables don’t have order. Order can be specified in a SELECT. Order can be the result of a clustered index but typically there is no guarantee of order without an explicit ORDER BY.

This is generally true of all Relational Databases.

Did you try placing an index on the Access table?

|||

True, I believe this more a call from Access 2003 settings to see how data is displayed.

Also check the code in Access about getting this data display.

|||

Dear All,

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thank you all of you for your replies.

However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

Export Wizard disturb order of data during exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect order of data.

Please give me the solutions.

Speaking logically, sets and tables don’t have order. Order can be specified in a SELECT. Order can be the result of a clustered index but typically there is no guarantee of order without an explicit ORDER BY.

This is generally true of all Relational Databases.

Did you try placing an index on the Access table?

|||

True, I believe this more a call from Access 2003 settings to see how data is displayed.

Also check the code in Access about getting this data display.

|||

Dear All,

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thank you all of you for your replies.

However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

export to unicode textfile with tsql

Hello,
is it possible to write the he result of query to a unicode textfile instead
of an ansi textfile. i need this because i want to generate udl files on the
fly.
any help appriciated.
GerbenHi
You don't say how the file is created but...
osql has a -u parameter that creates a unicode file.
John
"gerben" wrote:

> Hello,
> is it possible to write the he result of query to a unicode textfile inste
ad
> of an ansi textfile. i need this because i want to generate udl files on t
he
> fly.
> any help appriciated.
> Gerben

export to flat file - text qualifier problem

I'm exporting using a query to a flat .txt file. The problem I'm encountering is when I export the data and then open the .txt file into excel some columns cause line breaks to the next row. The columns that are breaking to a new row are varchar fields where the user has entered text into the field with double quotes ".

When I export, I'm using row delimiter {CR}{LF} column delimiter Comma and text qualifier Double Quote (")

Is there a way to prevent this from happening when I export and open the flat file into Excel?

I tried using replace, but I was getting a syntax error in my query. Here is the query without using replace:

SELECT e.session_date, l.lab_no, i.first_name + ' ' + i.last_name AS Teacher,
tt.name, d.district_name, s.school_name, t.title, a.q1 AS Question1, a.q2 AS Question2,
a.q3 AS Question3, a.q4 AS Question4, a.q5 AS Question5, a.q6 AS Question6, a.q7 AS Question7,
a.q8 AS Question8, a.q9 AS Question9, a.q10 AS Question10
FROM evaluation e
LEFT OUTER JOIN training t ON t.id = e.training
LEFT OUTER JOIN lab l ON l.id = e.lab_no
LEFT OUTER JOIN instructor i ON i.id = e.instructor
LEFT OUTER JOIN trainee tt ON tt.id = e.trainee
LEFT OUTER JOIN district d ON d.id = e.district
LEFT OUTER JOIN school s ON s.id = e.school
LEFT OUTER JOIN answers a ON a.id = e.answers
WHERE session_date >= '20070401' AND session_date < '20070501'

I would need to use the replace on columns a.q7, a.q8, a.q9, and a.q10

I tried using another delimiter...pipes (|) and that didn't work? Maybe I was attempting it incorrectly?

Thanks in advance for any help.I got Your problem
suppose your column is

a7 = AA"DS
a8 = SD"AD
a9 = WR"TY
a10 = DGHR

now you have given text qualifier as double quote -> "

so while preparing data for export your fields will be treated as

a7 = "AA"DS"
a8 = "SD"AD"
a9 = "WR"TY"
a10 = "DGHR"

since they are text

did you get my point now... AA will be treated as one piece of text instead of AA"DS.....since there is a start " and an end " which defines a text i.e AA

try using a different symbol for text qualifier, a symbol which does not exist in a7,a8,a9,a10 and that should do your job....

i know I have not explained it that clearly but i hope you are getting what i am trying to convey to you......|||Hi Nick,

thanks for replying to my post. yes, I think that is exactly the problem and I understand what you are saying. How do I use a different text qualifier though? The only ones available are a comma, double quote or none?

Thanks for your help.|||the thing is why do you need a text qualifier......if eventually you are going to import the data into another table or the same table you don't need to specify the text qualifier..... SQL server will directly import the data without a hitch.....has worked for me...try it out if that is the case....

Also if you import directly without the text qualifier to excel it should work fine.....try it out and let us know what happens

Export to Exel

I am newbie in exporting data.

i am using visual studio.net 2003 and using vb.net to create a webapplication.

My problem is how do i query my data from MS-SQL and export to microsoft Exel??

Can some one gave me the sample code?

Thanks

I had success to export my data to exel,

but why must i save the file before i open it??

Can anyone help me solve this problem??

My coding is Below :

Dim

rAsNew Columnar_Landed

r.SetDatabaseLogon("username", "password", "192.xxx.xxx.xxx,xx", "DTZ-VMS")

r.RecordSelectionFormula = query

Dim oStreamAsNew MemoryStream

oStream = r.ExportToStream(CrystalDecisions.Shared.ExportFormatType.ExcelRecord)

HttpContext.Current.Response.Clear()

HttpContext.Current.Response.Buffer =

True

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"

HttpContext.Current.Response.BinaryWrite(oStream.ToArray())

HttpContext.Current.Response.End()

|||

Please search the forum. You will find a lot of material on it. Search by "Gridview to excel". one important link is:

http://forums.asp.net/thread/1573815.aspx

Please mark this post as resolved if you r done