?
Solved

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

Posted on 2014-10-28
6
Medium Priority
?
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

801 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