Dealing with Comma's in a Comma delimited file?

Question

Post reply

Dealing with Comma's in a Comma delimited file?

Michael Weaver-259735

Grasshopper

Points: 19
More actions
January 15, 2008 at 11:50 am

#182066

I am trying to provide a vendor with a file they can use as an import into their software. The file specifications they have given me state that the file must be comma delimited and that any field returned in the result set that has a comma in it must be enclosed with double quotation marks (for example, ",").
I have written a T-SQL query that returns the data I want and have chosen Results to File with the options Output Format of Comma Delimited. This all works great except that I don't see any way to enclose comma's in the result set in double quotation marks. Any ideas on how to get around this?
I was hoping to do this all in T-SQL so that I wouldn't have to rely on DTS or SSIS and thus it would work on either SQL 2k or 2005, however... I am now wondering if I am taking the right approach. The ideal end result would be to have this file generated after hours every evening in an automated fashion, and saved somewhere on their network in the proper format.
Any advice would be welcome. Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

I dont know if this will help but, Cant you use replace? Something like this?

declare @t char(20)

set @t = 'testing, for comma'

Select replace(@t,',','","')

-Roy

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Why no just alway always enclose columns that could contain quotes in double quotes?

select

MyCol,

New_Mycol = '"'+MyCol+'"'

from

(

--Test Data

select MyCol = 'xx''yy'union all

select MyCol = 'xx''''y''y'union all

select MyCol = 'xxyy'

) a

Results:

MyCol New_Mycol

------- ---------

xx'yy "xx'yy"

xx''y'y "xx''y'y"

xxyy "xxyy"

(3 row(s) affected)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

While you're at it - might as well use the same logic to look for any fields sporting double-quotes in their contents too. That one will cause some trouble as well.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

abrahamb SSC Veteran Points: 248 More actions · Answer 4

FYI - your approach works in both DTS and SSIS and it is far easier to setup using these utilities than using a query however, you can use a query as well and run it from a command line prompt using OSQL if you like.

The DTS/SSIS route is really easy - make sure you are at SP2 for SQL 2005 when using SSIS though

abrahamb SSC Veteran Points: 248 More actions · Answer 5

I forgot to add...if you use DTS/SSIS then you can save the job and just have it run on demand or at regular times...

Try going through the DTs import wizard or the SSIS one. They have options to "write to a text file", comma delimited and enclose text in quotes etc.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

When you export data via SSMS, you have the option to choose a text qualifier. I believe the default is the double quote. All this does is wrap all exported text fields in double quotes. This would resolve your issue with custom programming and the vendor's requirement for quotation.

Once you complete the export the first time, you can tell the wizard to save the package in SQL. Once the package is saved in SSMS, you can automate the export with a job.

Piece of cake. 😉

My blog: http://jahaines.blogspot.com

Adam Haines SSC-Insane Points: 23217 More actions · Answer 7

with custom programming

Should be *without*

My blog: http://jahaines.blogspot.com

Raghs SSC Veteran Points: 218 More actions · Answer 8

I prefer using the BCP utility which is fast and offers a lot of flexibility. You have the option of configuring the row and column delimiters.

For more information, please refer to books online:

http://technet.microsoft.com/en-us/library/ms162802.aspx

abrahamb SSC Veteran Points: 248 More actions · Answer 9

FYI - you can also execute any saved DTS or SSIS packages from the command line using DTSRUN.EXE

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 10

You could use: [font="Courier New"]'"' + REPLACE(UglyData, '"', '""') + '"'[/font]

but a there's a shorter way: [font="Courier New"]QUOTENAME(UglyData, '"')[/font]

For example:

[font="Courier New"]SELECT QUOTENAME(UglyData, '"')

FROM ( SELECT 'Here''s a comma, dude' AS UglyData

UNION ALL

SELECT 'How about a "quote"?' ) x

"Here's a comma, dude"

"How about a ""quote""?"[/font]