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.
| Author |
Topic |
|
cjg
Starting Member
4 Posts |
 Posted - 2010-05-28 : 17:28:31
|
| I'm not sure where this should be posted. If this doesn't work here, please point me to the correct pace.I have created the following to perform an extract of data to a txt file. I would like to make the file tab delimited and remove any extra space in the fields. How would I go about doing that?Thanks!Christybcp "SELECT jmmain.BOOK_NUM,jmmain.agency, Convert(varchar(20), jmmain.bookdate, 101) as datebooked, jmmain.bkstatus,NMMAIN.last,NMMAIN.first,NMMAIN.middle,Convert(varchar(20), NMMAIN.dob, 101) as birthdate, NMMAIN.race,NMMAIN.ethnic,NMMAIN.sex,NMMAIN.hair,NMMAIN.eye,NMMAIN.height,NMMAIN.weight,NMMAIN.streetnbr,NMMAIN.street,NMMAIN.apt_flr,NMMAIN.city,NMMAIN.state,NMMAIN.zip,NMMAIN.hphone,NMMAIN.wphone,NMMAIN.mphone,NMMAIN.dl_state,NMMAIN.dr_lic,NMMAIN.ssn,NMMAIN.state_id,NMMAIN.fbi_nbr,jmmain.chrgdesc FROM (ILeadsrepl.dbo.jmmain INNER JOIN ILeadsrepl.dbo.NMMAIN ON jmmain.name_id = NMMAIN.name_id) WHERE jmmain.agency='FCCC' AND jmmain.bkstatus='A'" queryout C:\FCFiles\InCustody.txt -c -TChristy Geyer |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-28 : 17:39:14
|
| The default delimiter is tab, so you just specify -c without specifying -t, which is what you've posted anyway. To remove extra spaces in a field, you'll need to add that functionality to the query. You can RTRIM and LTRIM for that.I highly recommend saving your query into a view and then bcp'ing out the view instead of the queryout syntax with a command as long as yours. A view will greatly simplify this.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
|
|
Exporting to delimited text files