Exporting out of SQL server -- comma in my column data

[Milagro]

Hi,

I've taken over an SQL server 2005 express database. The database has
about 100,000 records in it. My client has asked me to pull out a
subset of data and save it into a .csv file, so they can later import
it into Excel.
I wrote and ran my query, and it worked very well. I exported the
result set (Save as ...) into a file.
However, when trying to import it into Excel I see a big problem. One
of the columns has data in it that contains commas. So, of course Excel
isn't importing the data correctly because it's splitting on the comma
for new rows when it's actually not a row.
Short of modifying all my data in the database(which can't be the best
solution), is there a simple way either in Sql Server or Excel where I
can export/import this data correctly?

Thanks,

M

[Ed Murphy]

Milagro wrote:

CSV is evil. Try tab-delimited.

[Milagro]

Thanks, that worked very well. Now I see another issue. One of my text
fields has data in it that must have a character(s) that is the same as
the record separator character (LF/CR ??). This is data users entered
in on the web so they could have put anything in here (This is a
project I'm taking over so I had no control over what data was input)
So, my import into Excel is splitting these thinking it should be a
new record.
Anyway to change the record separator in SQL server 2005 for exports?

Thanks,
m

[Ed Murphy]

Milagro wrote:

> Thanks, that worked very well. Now I see another issue. One of my text
> fields has data in it that must have a character(s) that is the same as
> the record separator character (LF/CR ??). This is data users entered in
> on the web so they could have put anything in here (This is a project
> I'm taking over so I had no control over what data was input)
> So, my import into Excel is splitting these thinking it should be a new
> record.
> Anyway to change the record separator in SQL server 2005 for exports?

I think Excel will accept a field containing a line break if it's
quote-delimited. Try creating a view along the lines of

create view v_foo as
select field1,
'"' + replace(field2,'"','""') + '"' field2,
field3
from t_foo

and then exporting from that.