Solved

MSSQL 2008 isql commnd line export to CSV specifying double quotes around alpha fields

Posted on 2014-10-28
6
280 Views
Last Modified: 2014-11-02
Hello. Like it sez...using ISQL to export to CSV for later import into Excel; works fine unless agents put commas in text fields.  I understand that somehow I can specify double quotes around alpha fields from the command line, but cannot locate info on how.  -s doesn't work, since double quotes are misinterpreted by the batch file that runs the query in Automate.  

So...is there a command line parameter that will specify double quotes around alpha fields to give me "," delimiting around them?  CUrrent command line:

isql.exe  -d tt20%logYear%_%logMonth% -h 0 -i %directory%\8230YCC.sql -S data_base77 -s "," -U sa -P ******* -n -w 1024 >> %directory%\asiexport.csv

THank you.
M
0
Comment
Question by:michaelheffernan
  • 4
  • 2
6 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 40409335
I would suggest use osql not older isql in a command line like:



exec master..xp_cmdshell N'sqlcmd -E -Q"select ''"'',accountid,''"'',''"'',CAST([status] as char(20)),''"'',''"'',CAST(domain      as char(100)),''"'',''"'',CAST(MRC as char(8)),''"'',''"'',CAST(scpoolid as char(50)),''"'',''"'',CAST(channel as char(50)),''"'',''"'',CAST([source] as char(50)),''"'',''"'',CAST(class as char(50)),''"'',''"'',CAST(hostingfamily as char(50)),''"'',''"'',CAST(sku as char(50)),''"'',''"'',CAST(hostingPlan as char(50)),''"'',''"'',CAST([date]      as char(10)), ''"'' from [dbo].[reporting_template]" -h-1 -s"," -W -o"c:\output.txt"'--, no_output


exec master..xp_cmdshell N'sqlcmd -E -Q"set nocount on; select * from DB_Name.dbo.TableName;" -s"," -W -o"c:\output.csv"'


or without headers like:

exec master..xp_cmdshell N'sqlcmd -E -Q"select * from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\output.txt"', no_output

more details about switches at:

http://msdn.microsoft.com/en-us/library/ms162806(v=sql.105).aspx
0
 

Author Comment

by:michaelheffernan
ID: 40409366
Yeah, I guess I can mod the SQL select statement directly, I was hoping for an easier way... let me look at doing that. TY
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40409433
You can also try use SSIS which may be indeed easier way that via I/Osql commands - sorry if I misinterpreted your question...would be something like:
1. Create a flat file connection manager and map the destination .csv file
2. Use a "Flat File Destination" task and export the data into this .csv file.
While configuring the flat file connection manager, when you click the browse button you need to select the Files of Type as *.csv (CSV files).

here is where you can find some details/examples:

http://decipherinfosys.wordpress.com/2008/07/23/ssis-exporting-data-to-a-text-file-using-a-package/
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:michaelheffernan
ID: 40409526
You have.  This is an automated daily process.  No GUIs need apply.  But I will eyeball the link. TY
0
 

Accepted Solution

by:
michaelheffernan earned 0 total points
ID: 40409720
My solution turned out to be placing +'"' and '"'+ before and after the comment fields in the two queries.  Keeping it simple...
0
 

Author Closing Comment

by:michaelheffernan
ID: 40417978
I didn't want a complex solution to a very simple problem.  I needed quick and dirty and I just figured it out.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question