Issue on export ot csv - 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

General SQL Server Forums

New to SQL Server Programming

Issue on export ot csv

Author

Topic

DWMelton
Starting Member

5 Posts

Posted - 2012-07-13 : 09:16:13

I have a a query that selects and export to csv-

select ....
fh_code_client.item_name AS "Cause of Loss",
cast(fh_claim_accident.facts as NVARCHAR(240)) AS "Description Of Loss",
fh_claim_accident.accident_state AS "Accident State",
fh_user.user_name AS "Adjuster",
from ...

The issue is the cast(fh_claim_accident.facts as NVARCHAR(240)) field will break on the line into 2 becasue in the UI the user hit return and puts in sec statement meaning tha the user inputs the data and hits return this is causing my export to get off by those line - is there a work around ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-13 : 09:54:33

csv can be tricky with possible imbedded quotes, commas, and carriage return-line feeds. You can deal with it with whatever reads the csv (preferred). Or you can hack it in your select statement by replacing the characters with something else like a <tab>.

replace(<columnValue>, char(10)+char(13), char(9)).

This assumes your "return" is a carriage return + line feed. You may have to adjust that to whatever the return characters are in your case. You can use something like winVI to see what the codes actually are. Or in Sql use unicode() or ascii() to see what they are.

Be One with the Optimizer
TG

DWMelton
Starting Member

5 Posts

Posted - 2012-07-13 : 11:51:00

select

--cast(fh_claim_accident.facts as VARCHAR(240)) as casttest

convert( varchar(240), fh_claim_accident.facts ) as converttest

from fh_claim_accident where claim_id = '000200000000187'

cast test
Received John Doe at Dorman Organization via fax.
IV rear ended CV#1 into CV# 2, minor injuries.

(1 row(s) affected)

convert test
Received JOhn Doe at Dorman Organization via fax.
IV rear ended CV#1 into CV# 2, minor injuries.

(1 row(s) affected)

Need this to be one line because when I export it out it wants to split the line here causing issue with export. Need the results to be as follows

Received John Doe at Dorman Organization via fax.IV rear ended CV#1 into CV# 2, minor injuries.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-13 : 12:04:22

I guess you didn't understand my first post. Try each of these. Any of them get rid of your "line split"?

replace(fh_claim_accident.facts, char(10), ' ')

replace(fh_claim_accident.facts, char(13), ' ')

replace(fh_claim_accident.facts, char(10)+char(13), ' ')

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources