 |
|
|
|
Next: SQLDMO Backup
|
| Author |
Message |
External
Since: Jul 09, 2007
Posts: 4
|
(Msg. 1) Posted: Mon Jul 09, 2007 7:03 am
Post subject: Export SQL data to text file
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
|
| Hi, What is the easiest/best way to export data from a SQL table to a
text file (the text file may need to be a comma seperated or a tab
seperated file)? This needs to execute automatically overnight.
thanks for any help folks,
Des.
|
>> Stay informed about: Export SQL data to text file |
|
| Back to top |
|
 | |
External
Since: Jul 15, 2007
Posts: 26
|
(Msg. 2) Posted: Mon Jul 09, 2007 7:09 am
Post subject: Re: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Jul 9, 10:03 am, " " wrote:
> Hi, What is the easiest/best way to export data from a SQL table to a
> text file (the text file may need to be a comma seperated or a tab
> seperated file)? This needs to execute automatically overnight.
>
> thanks for any help folks,
> Des.
I would go with BCP
Here is an example form BOL
bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname"
queryout Authors.txt -c -Sservername -Usa -Ppassword
the default is tab delimited, use the -t flag (field terminator) for
comma delimited files
You can also use DTS or SSIS
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx |
|
| Back to top |
|
| |
External
Since: Jul 09, 2007
Posts: 4
|
(Msg. 3) Posted: Mon Jul 09, 2007 7:12 am
Post subject: Re: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jul 15, 2007
Posts: 26
|
(Msg. 4) Posted: Mon Jul 09, 2007 7:18 am
Post subject: Re: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jul 09, 2007
Posts: 4
|
(Msg. 5) Posted: Mon Jul 09, 2007 7:23 am
Post subject: Re: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On 9 Jul, 15:18, SQL Menace wrote:
> On Jul 9, 10:12 am, " " wrote:
>
> > Thanks for the very quick response - Can you explain the Queryout
> > Option you mentioned?
> > (I have never seen this before)
> > thanks,
> > Des.
>
> It is described in BOL
>
> in | out | queryout | format
>
> Specifies the direction of the bulk copy. in copies from a file into
> the database table or view. out copies from the database table or view
> to a file. queryout must be specified only when bulk copying data from
> a query. format creates a format file based on the option specified (-
> n, -c, -w, -6, or -N) and the table or view delimiters. If format is
> used, the -f option must be specified as well.
>
> read more here:http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx
>
> Denis The SQL Menacehttp://sqlservercode.blogspot.comhttp://sqlblog.com/blogs/denis_...o/defau
Brilliant- Cheers - One last thing for now - Are you able to execute
the bcp utility from within a stored procedure (as opposed to having
to run it from DOS)? Thanks, Des. >> Stay informed about: Export SQL data to text file |
|
| Back to top |
|
| |
External
Since: Jul 15, 2007
Posts: 26
|
(Msg. 6) Posted: Mon Jul 09, 2007 7:41 am
Post subject: Re: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Jul 9, 10:23 am, " " wrote:
> On 9 Jul, 15:18, SQL Menace wrote:
>
>
>
>
>
> > On Jul 9, 10:12 am, " " wrote:
>
> > > Thanks for the very quick response - Can you explain the Queryout
> > > Option you mentioned?
> > > (I have never seen this before)
> > > thanks,
> > > Des.
>
> > It is described in BOL
>
> > in | out | queryout | format
>
> > Specifies the direction of the bulk copy. in copies from a file into
> > the database table or view. out copies from the database table or view
> > to a file. queryout must be specified only when bulk copying data from
> > a query. format creates a format file based on the option specified (-
> > n, -c, -w, -6, or -N) and the table or view delimiters. If format is
> > used, the -f option must be specified as well.
>
> > read more here:http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx
>
> > Denis The SQL Menacehttp://sqlservercode.blogspot.comhttp://sqlblog.com/blogs/denis_gobo/...
>
> Brilliant- Cheers - One last thing for now - Are you able to execute
> the bcp utility from within a stored procedure (as opposed to having
> to run it from DOS)? Thanks, Des.- Hide quoted text -
>
> - Show quoted text -
Sure us xp_cmdshell
master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors
ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -
Ppassword '
use the -T switch for a trusted connection
master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors
ORDER BY au_lname" queryout Authors.txt -c -T -S(local)'
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx >> Stay informed about: Export SQL data to text file |
|
| Back to top |
|
| |
External
Since: Aug 24, 2003
Posts: 739
|
(Msg. 7) Posted: Mon Jul 09, 2007 10:58 am
Post subject: Re: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Hi
1) DTS
2)OPENDATASOURCE command
3)Linked Server
wrote in message
> Hi, What is the easiest/best way to export data from a SQL table to a
> text file (the text file may need to be a comma seperated or a tab
> seperated file)? This needs to execute automatically overnight.
>
> thanks for any help folks,
> Des.
> >> Stay informed about: Export SQL data to text file |
|
| Back to top |
|
| |
External
Since: Jan 29, 2004
Posts: 891
|
(Msg. 8) Posted: Mon Jul 09, 2007 4:20 pm
Post subject: Re: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jan 29, 2004
Posts: 891
|
(Msg. 9) Posted: Mon Jul 09, 2007 4:30 pm
Post subject: Re: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
> Are you able to execute
> the bcp utility from within a stored procedure (as opposed to having
> to run it from DOS)?
Not directly, since BCP is an exe file and not a TSQL command. But one way is through xp_cmdshell.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
wrote in message
> On 9 Jul, 15:18, SQL Menace wrote:
>> On Jul 9, 10:12 am, " " wrote:
>>
>> > Thanks for the very quick response - Can you explain the Queryout
>> > Option you mentioned?
>> > (I have never seen this before)
>> > thanks,
>> > Des.
>>
>> It is described in BOL
>>
>> in | out | queryout | format
>>
>> Specifies the direction of the bulk copy. in copies from a file into
>> the database table or view. out copies from the database table or view
>> to a file. queryout must be specified only when bulk copying data from
>> a query. format creates a format file based on the option specified (-
>> n, -c, -w, -6, or -N) and the table or view delimiters. If format is
>> used, the -f option must be specified as well.
>>
>> read more here:http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx
>>
>> Denis The SQL
>> Menacehttp://sqlservercode.blogspot.comhttp://sqlblog.com/blogs/denis_...o/defau
>
> Brilliant- Cheers - One last thing for now - Are you able to execute
> the bcp utility from within a stored procedure (as opposed to having
> to run it from DOS)? Thanks, Des.
> >> Stay informed about: Export SQL data to text file |
|
| Back to top |
|
| |
External
Since: Jul 09, 2007
Posts: 4
|
(Msg. 10) Posted: Fri Jul 13, 2007 8:22 am
Post subject: Re: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On 9 Jul, 15:41, SQL Menace wrote:
> On Jul 9, 10:23 am, " " wrote:
>
>
>
>
>
> > On 9 Jul, 15:18, SQL Menace wrote:
>
> > > On Jul 9, 10:12 am, " " wrote:
>
> > > > Thanks for the very quick response - Can you explain the Queryout
> > > > Option you mentioned?
> > > > (I have never seen this before)
> > > > thanks,
> > > > Des.
>
> > > It is described in BOL
>
> > > in | out | queryout | format
>
> > > Specifies the direction of the bulk copy. in copies from a file into
> > > the database table or view. out copies from the database table or view
> > > to a file. queryout must be specified only when bulk copying data from
> > > a query. format creates a format file based on the option specified (-
> > > n, -c, -w, -6, or -N) and the table or view delimiters. If format is
> > > used, the -f option must be specified as well.
>
> > > read more here:http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx
>
> > > Denis The SQL Menacehttp://sqlservercode.blogspot.comhttp://sqlblog.com/blogs/denis_gobo/...
>
> > Brilliant- Cheers - One last thing for now - Are you able to execute
> > the bcp utility from within a stored procedure (as opposed to having
> > to run it from DOS)? Thanks, Des.- Hide quoted text -
>
> > - Show quoted text -
>
> Sure us xp_cmdshell
>
> master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors
> ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -
> Ppassword '
>
> use the -T switch for a trusted connection
>
> master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors
> ORDER BY au_lname" queryout Authors.txt -c -T -S(local)'
>
> Denis The SQL Menacehttp://sqlservercode.blogspot.comhttp://sqlblog.com/blogs/denis_...o/defau Hide quoted text -
>
> - Show quoted text -
Thanks Denis for your help! >> Stay informed about: Export SQL data to text file |
|
| Back to top |
|
| |
External
Since: Aug 14, 2004
Posts: 11
|
(Msg. 11) Posted: Sun Sep 16, 2007 7:22 am
Post subject: RE: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Try www.sqlscripter.com to export data to a text file.
It's free.
" " wrote:
> Hi, What is the easiest/best way to export data from a SQL table to a
> text file (the text file may need to be a comma seperated or a tab
> seperated file)? This needs to execute automatically overnight.
>
> thanks for any help folks,
> Des.
>
> >> Stay informed about: Export SQL data to text file |
|
| Back to top |
|
| |
External
Since: Feb 10, 2005
Posts: 12
|
(Msg. 12) Posted: Fri Nov 02, 2007 5:34 am
Post subject: Re: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Hi,
this was really helpful just what I was looking for thank you very much. I
managed to get the code into a stored procedure, which selected all records
from a table and output to a text file, this works fine when I run it.
however it doesn't create the text file when my user does it. they have
permissions to write to the directory.
is there some setting I need to give them to allow for them to create the
text file?
"SQL Menace" wrote:
> On Jul 9, 10:23 am, " " wrote:
> > On 9 Jul, 15:18, SQL Menace wrote:
> >
> >
> >
> >
> >
> > > On Jul 9, 10:12 am, " " wrote:
> >
> > > > Thanks for the very quick response - Can you explain the Queryout
> > > > Option you mentioned?
> > > > (I have never seen this before)
> > > > thanks,
> > > > Des.
> >
> > > It is described in BOL
> >
> > > in | out | queryout | format
> >
> > > Specifies the direction of the bulk copy. in copies from a file into
> > > the database table or view. out copies from the database table or view
> > > to a file. queryout must be specified only when bulk copying data from
> > > a query. format creates a format file based on the option specified (-
> > > n, -c, -w, -6, or -N) and the table or view delimiters. If format is
> > > used, the -f option must be specified as well.
> >
> > > read more here:http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx
> >
> > > Denis The SQL Menacehttp://sqlservercode.blogspot.comhttp://sqlblog.com/blogs/denis_gobo/...
> >
> > Brilliant- Cheers - One last thing for now - Are you able to execute
> > the bcp utility from within a stored procedure (as opposed to having
> > to run it from DOS)? Thanks, Des.- Hide quoted text -
> >
> > - Show quoted text -
>
> Sure us xp_cmdshell
>
> master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors
> ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -
> Ppassword '
>
> use the -T switch for a trusted connection
>
> master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors
> ORDER BY au_lname" queryout Authors.txt -c -T -S(local)'
>
>
> Denis The SQL Menace
> http://sqlservercode.blogspot.com
> http://sqlblog.com/blogs/denis_gobo/default.aspx
>
>
> >> Stay informed about: Export SQL data to text file |
|
| Back to top |
|
| |
External
Since: Feb 17, 2011
Posts: 1
|
(Msg. 13) Posted: Thu Feb 17, 2011 4:25 am
Post subject: Re: Export SQL data to text file [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
I think one article can help you. It introduces a samll tool and how to use the tool to export data.
You can view the article on http://www.codeproject.com/KB/cs/Excel_PDF_Word_ExportWiz.aspx
> On Monday, July 09, 2007 10:03 AM desmc wrote:
> Hi, What is the easiest/best way to export data from a SQL table to a
> text file (the text file may need to be a comma seperated or a tab
> seperated file)? This needs to execute automatically overnight.
>
> thanks for any help folks,
> Des.
>> On Monday, July 09, 2007 10:07 AM Uri Dimant wrote:
>> Hi
>> 1) DTS
>> 2)OPENDATASOURCE command
>> 3)Linked Server
>>> On Monday, July 09, 2007 10:09 AM SQL Menace wrote:
>>> On Jul 9, 10:03 am, " " wrote:
>>>
>>> I would go with BCP
>>>
>>> Here is an example form BOL
>>>
>>> bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname"
>>> queryout Authors.txt -c -Sservername -Usa -Ppassword
>>>
>>>
>>> the default is tab delimited, use the -t flag (field terminator) for
>>> comma delimited files
>>>
>>> You can also use DTS or SSIS
>>>
>>> Denis The SQL Menace
>>> http://sqlservercode.blogspot.com
>>> http://sqlblog.com/blogs/denis_gobo/default.aspx
>>>> On Monday, July 09, 2007 10:12 AM desmc wrote:
>>>> Thanks for the very quick response - Can you explain the Queryout
>>>> Option you mentioned?
>>>> (I have never seen this before)
>>>> thanks,
>>>> Des.
>>>>> On Monday, July 09, 2007 10:18 AM SQL Menace wrote:
>>>>> On Jul 9, 10:12 am, " " wrote:
>>>>>
>>>>> It is described in BOL
>>>>>
>>>>> in | out | queryout | format
>>>>>
>>>>> Specifies the direction of the bulk copy. in copies from a file into
>>>>> the database table or view. out copies from the database table or view
>>>>> to a file. queryout must be specified only when bulk copying data from
>>>>> a query. format creates a format file based on the option specified (-
>>>>> n, -c, -w, -6, or -N) and the table or view delimiters. If format is
>>>>> used, the -f option must be specified as well.
>>>>>
>>>>> read more here: http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx
>>>>>
>>>>>
>>>>> Denis The SQL Menace
>>>>> http://sqlservercode.blogspot.com
>>>>> http://sqlblog.com/blogs/denis_gobo/default.aspx
>>>>>> On Monday, July 09, 2007 10:20 AM Tibor Karaszi wrote:
>>>>>> You have three options for "direction": in, out and queryout. For the last one, you specify a SELECT
>>>>>> statement instead of a table name as a source.
>>>>>>
>>>>>> --
>>>>>> Tibor Karaszi, SQL Server MVP
>>>>>> http://www.karaszi.com/sqlserver/default.asp
>>>>>> http://sqlblog.com/blogs/tibor_karaszi
>>>>>>
>>>>>>
>>>>>> wrote in message
>>>>>>> On Monday, July 09, 2007 10:23 AM desmc wrote:
>>>>>>> Brilliant- Cheers - One last thing for now - Are you able to execute
>>>>>>> the bcp utility from within a stored procedure (as opposed to having
>>>>>>> to run it from DOS)? Thanks, Des.
>>>>>>>> On Monday, July 09, 2007 10:30 AM Tibor Karaszi wrote:
>>>>>>>> Not directly, since BCP is an exe file and not a TSQL command. But one way is through xp_cmdshell.
>>>>>>>>
>>>>>>>> --
>>>>>>>> Tibor Karaszi, SQL Server MVP
>>>>>>>> http://www.karaszi.com/sqlserver/default.asp
>>>>>>>> http://sqlblog.com/blogs/tibor_karaszi
>>>>>>>>
>>>>>>>>
>>>>>>>> wrote in message
>>>>>>>>> On Monday, July 09, 2007 10:41 AM SQL Menace wrote:
>>>>>>>>> On Jul 9, 10:23 am, " " wrote:
>>>>>>>>>
>>>>>>>>> Sure us xp_cmdshell
>>>>>>>>>
>>>>>>>>> master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors
>>>>>>>>> ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -
>>>>>>>>> Ppassword '
>>>>>>>>>
>>>>>>>>> use the -T switch for a trusted connection
>>>>>>>>>
>>>>>>>>> master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors
>>>>>>>>> ORDER BY au_lname" queryout Authors.txt -c -T -S(local)'
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Denis The SQL Menace
>>>>>>>>> http://sqlservercode.blogspot.com
>>>>>>>>> http://sqlblog.com/blogs/denis_gobo/default.aspx
>>>>>>>>>> On Friday, July 13, 2007 11:22 AM desmc wrote:
>>>>>>>>>> Thanks Denis for your help!
>>>>>>>>>>> On Sunday, September 16, 2007 10:22 AM Thoma wrote:
>>>>>>>>>>> Try www.sqlscripter.com to export data to a text file.
>>>>>>>>>>> it is free.
>>>>>>>>>>>> On Thursday, September 04, 2008 10:11 AM Rodney Wilkins wrote:
>>>>>>>>>>>> *** Sent via Developersdex http://www.developersdex.com ***
>>>>>>>>>>>> Submitted via EggHeadCafe
>>>>>>>>>>>> ASP.NET Drawing a chart using OWC11 - Office Web Components
>>>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/601e9bc2-40ed-405e-b1b0-f4...46b6698 >> Stay informed about: Export SQL data to text file |
|
| Back to top |
|
| |
| Related Topics: | writing data to text file - Hello there How can I create new text file by sql and write data to it?
Right-justify a column on export to text - Hello, I have a column (AccountNo per the below create script) that displays properly with leading zeroes to fill a 22-character column while in SQL. However, when I use a DTS export to a standard text file with no transformation, it left-justifies. ...
Data Export to Excel without using DTS or SISS - To all: I need to create a Stored Procedure that will take a query and export it to Excel. I also need the Excel file to have a timestamp making each file unique upon creation. I have attempted to use the bcp utility, however you cannot export to Exce...
import hebrew from text file - i have a text file mixed with hebrew text an numbers now when i import it by bulk insert i get: hebrew words writen from left to right and correct numbers. so i have to apply a function for reversing the words with out reversing the numbers is their....
Numbers becoming text when exported into XL file - I'm porting some data from SQL Server 2005, in an SSIS package, to an XL spreadsheet. The numbers keep showing up as text in the spreadsheet, which requires me to create a second worksheet which refers to the text numbers with a VALUE() function, in.... |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
