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 |
|
AlexP
Starting Member
34 Posts |
 Posted - 2005-08-08 : 10:38:36
|
| I am using a DTS to create a textfile on about 70,000 records with one line representing one order.Every once and a while I get a line that has like a line feed placed on it. I have checked the data and there is what looks like a space. So I RTRIM and LTRIM that data on the SELECT statement and that did NOT affect the output.Can anyone help please?!SAMPLE DATA:time=2005.08.02.01.01.56|site_language=en_US|datasource_name=TEST_en_US_062005|title=|firstname=James|minitial=|lastname=Mon|gender=f|email=56thgedh@verizon.net|dobmo=02|dobdd=09|dobyr=1979|addr1=25 Miller Ave.|addr2=|city=Hill|state=New Jersey|zip=07703|country=US |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-08-08 : 10:53:33
|
It's obviously not a space at the end of some of those offending lines.Do you have a text file editor that can display the hex values? - that way you can determine what is being passed on to you in the text file.These issues need to be sorted out by the process that extracts the data for you ie the procedure that creates the text file.Duane.  |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-08 : 11:03:09
|
select * From your table where addr1 like '%' + char(13) + '%' -- carriage returnor addr1 like '%' + char(10) + '%' -- line feedIf this is the culprit, you can use replace to clean it up a bit...Corey Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."  |
|
|
|
AlexP
Starting Member
34 Posts |
Posted - 2005-08-08 : 11:06:40
|
| yes thanks guys I used your ideas and the REPLACE nailed it. Thanks.By the way what text editor will show me the hex values? |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-08-08 : 11:29:21
|
| Yes - Ultraedit is what I use too.The text file that you are using - I take it is coming from somebody else? Not an extract that you performed?I have learnt over the years that if an extract (from somebody else) is given to you to load and you are fixing data for him prior to loading it into your system - other things start creeping in at a later stage too because you are not making the provider of the data aware of the fact that they are passing on garbage to you, which means that often from time to time you have to add other extra fiddles into your once neatly written peice of code.In cases like this - I send the file straight back and ask for it to be extracted properly for me.Thats just me though Duane. |
|
|
|
AlexP
Starting Member
34 Posts |
Posted - 2005-08-08 : 11:30:15
|
| No I am creating the textfile in DTS. |
|
|
|
|
|
|
**URGENT** Line Feed Problems