April 25, 2011 at 9:49 pm
I am trying to export a table to flat file using Pipe Delimited(Doesnt matter if its other delimited also).
There is a column which has datatype varchar(512) when exported to flat file it comes into next line instead of single row such as
33439476|Box|W|BI|GSDN|C|0|4347|Watch Video to learn more.<ul class=bullet_
primary_sm><li>Works on High Definition or Standard Definition TVs</li><li>Watch HD and standard defin
ition programming</li><li>^M
Access to extensive Video on Demand library with HD and SD Content</li><li>Interactive Media Guide</li
>||N|N|N|
The above matter
"Watch Video to learn more.<ul class=bullet_primary_sm><li>Works on High Definition or Standard Definition TVs</li><li>Watch HD and standard definition programming</li><li>^M
Access to extensive Video on Demand library with HD and SD Content</li><li>Interactive Media Guide</li
>"
belongs to a single column but the text has come into next line.
There is an embedded “END OF LINE” comes in as part of this record which make is look like 2 rows for loading instead of one line.
Please help me how to do this i have tried to replace the empty space and tab space but nothing has worked ou tplease help how to get in single row....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 26, 2011 at 2:10 am
The first thing I'd do is use a hex editor to assure myself that I know exactly what the characters are which need to be replaced.
Then use REPLACE as part of the SELECT for the data you are exporting.
But I'm guessing that you've already tried that?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 26, 2011 at 8:46 am
Yes i did it didnt work ..any other suggestions would be helpful...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 26, 2011 at 9:04 am
Sri8143 (4/26/2011)
Yes i did it didnt work ..any other suggestions would be helpful...
This works:
DECLARE @CRTEXT VARCHAR(50)
SET @CRTEXT = 'Text ' + CHAR(13) + CHAR(10) + 'more text'
SELECT @CRTEXT, REPLACE(@CRTEXT, CHAR(13) + CHAR(10), '')
Now please be more descriptive about what you have tried. "It didn't work" isn't very helpful - tell us what you tried.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 26, 2011 at 11:04 am
Phil..Thanks for the post i tried replacing CHAR(9) tab and empty space but not line feed and carriage return ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply