| Author |
Topic |
|
mlmeyer
Starting Member
26 Posts |
 Posted - 2005-01-20 : 14:43:19
|
| I am trying to run a query in SQL Query Analyzer that simply outputs to a CSV file. My problem though is that some of the fields contain a ',' which throws off the CSV file. For example a LastName of 'Smith, Jr' causes havoc.Is there a way to remove all of the commas from the fields or is there another easy way to fix this problem? Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-20 : 14:48:08
|
| You'll need to put quotes around your character data.Tara |
 |
|
|
mlmeyer
Starting Member
26 Posts |
Posted - 2005-01-20 : 14:58:02
|
| Well your solution sounds easy but how do I put quotes around the character data? I asuume this can be automated? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-20 : 14:59:21
|
| Well, you haven't told us what code you are using to output to a CSV file. Once we see that, then we'd be able to help.Tara |
|
|
|
mlmeyer
Starting Member
26 Posts |
Posted - 2005-01-20 : 15:03:11
|
| It is just a stored procedure that runs and saves the CSV file to a network drive. We then post the CSV file to an FTP site so it can be uploaded to another external database. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-20 : 15:04:43
|
| How can we tell you the solution if we haven't seen the code? Are you using DTS, bcp, or something else? Without additional information, we can't provide a solution.Tara |
|
|
|
mlmeyer
Starting Member
26 Posts |
Posted - 2005-01-20 : 15:12:37
|
| I see what you are saying but I guess I would like to find a solution that fixes the problem on the front end. For example, I often run adhoc queries directly from SQL Analyzer and just have it outputted to a CSV file on my PC. I then just manually upload that file from my PC to a third party vendor. So often times this process does not go through DTS. So is there a way to just remove the commas. I know there are other solutions like I can create a custom delimiter but unfortunately the third part vendor can only accept comma delimited files.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-20 : 15:20:30
|
| If it's pure T-SQL, then you can use REPLACE function. This won't change the data in the database, but rather for displaying purposes. For bcp, you can use the q switch. For DTS, on the properties of the destination, select the option for quoted identifiers.Tara |
|
|
|
mlmeyer
Starting Member
26 Posts |
Posted - 2005-01-20 : 15:34:45
|
| Great Thanks! I just needed to know there is a REPLACE function. I have this 1300 page SQL book and that wasn't listed but found it in the online books. |
|
|
|
|
Output to CSV File Problem