exporting table to text file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

exporting table to text file

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-15 : 13:17:23

i have a table with following fields

customername
products
amountpaid
amountdue.

I need the data of this table to be exported to textfile
in such manner that customeranme position is col 1 while the products data is positioned at col 71 and so on according to my wish in the text file
Please suggest either dynamic query or through dts export.

thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 13:30:45

you might need to write a custom script task in your dts to define this customised transformation. or use bcp out with format file to specify your customisation option.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-15 : 14:06:09

master..xp_cmdshell 'bcp "select left(customername+space(71),71)+left(products+space(x),x)+...from mydb.tbl " queryout c:\myfile.txt -c -Smyserver -T'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-16 : 18:10:12

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL

this is what thats showing up when i am using ur query. please explain

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-16 : 20:13:06

Are you sure you typed it in correctly?
Try this one and build it up from there

declare @sql varchar(4000)
select @sql = 'bcp "select left(name+space(71),71)+left(name+space(20),20) from master..sysobjects " queryout c:\myfile.txt -c -S' + @@servername + ' -T'
select @sql
exec master..xp_cmdshell @sql

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-17 : 09:52:16

bcp "select left(name+space(71),71)+left(name+space(20),20) from master..sysobjects " queryout c:\myfile.txt -c -SDATAWAREHOUSE -T

NULL
Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
NULL
1311 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
NULL

this what i get in the output window of the query anlyzer when i run the above sql statement . Where would the file be saved . If in c drive i am not able to locate it. please explain.

Thanks

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-17 : 14:19:03

Should be at c:\myfile.txt, per your command string.

Terry

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 14:20:50

It'll be c:\myfile.txt on the server.
If you want it on your machine run the bcp command in a command window or .bat file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-17 : 14:21:43

its not there .

Can help me withat that query.

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 14:29:33

try

master..xp_cmdshell 'type c:\myfile.txt'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-17 : 14:58:56

hi

i could find out that file in the c drive of the server. thanks.

but my query does not work. Can you please have a look.
declare @sql varchar(4000)
select @sql = 'bcp "select left(cr+space(71),71)+left(branch +space(10),10)+left( ldate+space(10),10)+left(loan +space(10),10)
+left( product+space(15),15)+ left(numofl+space(10),10)+left(numofE+space(10),10)
+left(amtofI+space(15),15)+left(amtofE+space(15),15)
from testdatabase..ascii " queryout c:\myfile.txt -c -S' + @@servername + ' -T'
select @sql
exec master..xp_cmdshell @sql.

i described the error in the previous post.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-17 : 16:35:06

hey it worked but i am unable to view anything on the text file
though in the out put it says it copied the 2 records.

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 17:55:54

The error is just that there's a problem with the syntax.
You should be able to spot it from the string displayed by the select @sql.
I take it you don't have any carriage returns in the string? It's a dos command so has to be on a single line.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-05-16 : 12:58:11

thanks a lot nr

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-06-26 : 13:19:19

Hi

I have another question.

In the bcp command query out , i want the textfile to be named with the date month and year for ex. 06012008.txt for the month of june

if ran on july 1st the text file should be named as 07012008.txt.

and so on..

How can this be done.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 13:23:25

quote:
Originally posted by akpaga

Hi

I have another question.

In the bcp command query out , i want the textfile to be named with the date month and year for ex. 06012008.txt for the month of june

if ran on july 1st the text file should be named as 07012008.txt.

and so on..

How can this be done.

Thanks

use dynamic sql. build a sql string by appending the xp_cmdshell bcp out command to a variable which holds the date value and use EXEC(@Sql) to execute it.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-06-26 : 13:29:46

sample code please visakh16.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 13:34:45

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-06-26 : 13:52:41

thanks visakh16