handling commas in text field/csv using osql

[jcaulder]

Hi,

I am trying to create a comma delimited text file using osql. My problem is that the data returned from the query often has 'commas' in the field data. So when the csv file is opened using something like Excel, it interprets the embedded commas in the data to be field separators which obviously causes a big problem with the data.

If I view the data in something like WordPad, it looks something like:

ID Item# ItemDesc
300, 500, Gloves,Large

and when opened in Excel, I get something like:

ID Item# ItemDesc
300 500 Gloves Large
^^new column created by comma

I think what needs to happen is that osql should place quotes around text data containing special characters(or qutoes around all text data for that matter). How can I accomplish this?

TIA

 

[Jamfool]

You need to use "

300, 500, "Gloves,Large"

thats a shift-2 not two singles

regards

 

[jcaulder]

But how do I get osql to put the quotes around the text field when it's generating the csv? Is there a switch to tell osql to put quotes around text data? It is not doing it by default. In my osql statement, I am using "-s ," to make it a comma delimited and am saving the output file as "-ofilename.csv".

What is preventing osql from putting quotes around the text data?

 

[JamesLean]

You can't do that with osql. I usually avoid using comma-separators for just this reason. I find tabs or the pipe (|) work much better.

--James

 

[jcaulder]

My osql looks like the following:

osql -S server -U uid -P pw -d dbname -s , -w1250 -h-1 -Qup_rpt_DepartExpDetail -oPMM_Data.csv

 

[jcaulder]

Unfortunately, I have no choice but to use commas because the data extract is being used by another application that requires this.

So I have no ability to do this using osql? That seems terrible given the fact that Excel cannot handle special characters in text data correctly without quotes. Where is the MS integration? Anybody know of another option? This is part of a vbs script so I hate to move away from osql but it looks like I may have to. Guess I can do it in a stored procedure and build the output file like I need it through concatenation of fields.

Thanks for the replies!

 

[JamesLean]

osql isn't really designed to export data like this. I would create a DTS package that did the export - you can specify the exact file format through this (including using double quotes for character data). Then just execute that either directly from VB or call a stored proc from SQL Server.

--James

 

[jcaulder]

I will research DTS as I'm not familiar with it.


As it is now, the data selection is done through a stored procedure and I do not want to recreate the logic behind it in multiple places(the sp is used for other purposes). Hopefully, DTS allows me to call the sp and format the data returned from it in the package.

Thanks again for the responses. Time to learn something new!

J