| Author |
Topic |
|
lotand
Starting Member
8 Posts |
 Posted - 2007-06-28 : 09:56:15
|
| Hi! Can someone tell me how to create a semi-colon separeted file with bcp? I guess it's quite simple, but I have not used BCP before.I try this:master..xp_cmdshell 'bcp agresso.dbo.textfile out C:\Filelist.txt -T -c -Uuser -Ppassword -Sserver' This gives me tab-separeted file.I also wonder if there is any "known" problem to use BCP on a table that have all columns declared as varchar(256). The textfile looks wierd when I open it in Ultra Edit.Glad for your help!Lotta |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-28 : 10:46:48
|
| master..xp_cmdshell 'bcp agresso.dbo.textfile out C:\Filelist.txt -T -c -t; -Uuser -Ppassword -Sserver' -t overrides the field terminator.Have a loko at the parameters in bol.You are also using -T trusted connection and user name/password. You should have one or the other but not both.==========================================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. |
 |
|
|
lotand
Starting Member
8 Posts |
Posted - 2007-06-28 : 10:56:34
|
| Thanks! Now I just have to find the switch for ;-separated. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-28 : 12:35:54
|
Nigel already showed you that. it's this switch: "-t;"whatever char comes after -t will be the delimiter.
elsasoft.org |
|
|
|
lotand
Starting Member
8 Posts |
Posted - 2007-06-29 : 05:18:01
|
| ...correct! Im stupid. Sorry. But, if I now look in the semi-sep file there is same number of spaces that the table is "declared" (opps, hope you understannd). If my table has col1 char(25) and col2 char(25) and the contain iscol1 col2AA BBThe output will be AA ;BBI would love to have it AA;BBDoes anyone know? |
|
|
|
lotand
Starting Member
8 Posts |
Posted - 2007-06-29 : 05:21:19
|
| I just saw that the spaces is not shown in my message above... My output isAA<23spaces>;BB |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-29 : 09:56:15
|
well, you can import the data, and then clean it up at a post processing step.check out RTRIM and LTRIM in books online.
elsasoft.org |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-29 : 09:57:12
|
btw, this forum is meant for working scripts that might be of use to others, not for questions on how to get your scripts working. 
elsasoft.org |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-29 : 10:03:51
|
| moved._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
lotand
Starting Member
8 Posts |
Posted - 2007-06-29 : 11:11:52
|
| I can use (LTRIM,RTRIM) when I import, but how can I get ride of the spaces in the outfile? My tables I export is 4GB so the textfiles became very big wuth all this spaces. I FTP the textfile, so I want it to as small as possible. Zip can be an alternative, but I really want the file to be as small as possible from the bigging!(Thank's for moving my items to correct forum) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-29 : 11:53:42
|
| If you rtrim the columns you are exporting then they won't be space filled - that's what the delimitter is for.You table probably has char columns which a fixed width so will be space padded.OptionsChange the table to varchar and rtrim when populating itCreate a view which rtrims each column on the select and bcp that outUse a query for the bcp which rtrims each column (could be inline or an SP.==========================================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. |
|
|
|
|
BCP - Create semi-col separated