SQLCMD export questoni....?

Experts:

I'm using a SQLCMD like this:

sqlcmd -S 192.168.10.4 -i c:\ftp\sqlscripttest.sql -o c:\ftp\sqloutputdata.csv

The SQL Script inside the "sqlscripttest.sql" file looks like this:

select
 [SEGStart] =  min(CONVERT(VARCHAR(10), DATEADD(hour, 1, c.calltime),101) +' '+  CONVERT(VARCHAR,DATEADD(hour, 1, c.calltime), 108))
 ,[SEGStop] =  min(CONVERT(VARCHAR(10), DATEADD(hour, 1, c.callendtime),101) +' '+  
                   CONVERT(VARCHAR,DATEADD(hour, 1, c.callendtime), 108))
 ,[Agent_ID] = min(c.employeeid)
 ,min(c.ani)[Calling_Pty]
 ,[VDN] = 'HMTB'
 ,[Duration] =min(CAST(substring(convert(varchar(19),c.callendtime-c.calltime,108),4,2) AS INT)*60
                        +CAST(RIGHT(substring(convert(varchar(19),c.callendtime-c.calltime,108),7,2),2) AS INT))
 ,[Hold_Time] = min(CAST(substring(convert(varchar(19),smdr.ringduration,108),1,2) AS INT)*60
                        +CAST(substring(convert(varchar(19),smdr.ringduration,108),4,2) AS INT))
 ,[Disposition] =
min(case when calldispositionid in ('46') then 'F'        --WANT BETTER COMPUTER SPECS
           when calldispositionid in ('7') then 'N'       --HANG UPS
               when calldispositionid in ('37') then 'C'       --WAIT TOO LONG
           when calldispositionid in ('6') then 'Z'       --WRONG NUMBER
           when calldispositionid in ('45') then 'M'      --DOESN'T HAVE BANK INFO WITH THEM
           when calldispositionid in ('36') then 'B'      --WV/MD/IL
           when calldispositionid in ('5','49') then 'E'  --TOO EXPENSIVE
           when calldispositionid in ('44','34','3','2','47','8') then 'H' --OTHER
           when calldispositionid in ('52') then 'L'      --CREDIT HELP LEAD
           when calldispositionid in ('35') then 'G'      --NO CHECKING/SAVINGS ACCOUNT
           when calldispositionid in ('43') then 'J'      --CAN'T DRAFT ACCOUNT
           when calldispositionid in ('51') then 'K'      --COULDN'T PAY $99 IN NEXT 10 DAYS
           when calldispositionid in ('4') then 'T'      --TEST
           when calldispositionid in ('11') then 'U'      --TOOK MESSAGE
           when calldispositionid in ('42') then 'P'      --JUST GETTING INFORMATION
           when calldispositionid in ('50') then 'O'      --WILL ONLY PAY MONTHLY
           when calldispositionid in ('18') then 'I'      --TOO BUSY
           when calldispositionid in ('10') then 'D'      --NEED TO TALK TO SPOUSE, FRIEND
           when calldispositionid in ('41') then 'R'      --SHOPPING PRICES
           when calldispositionid in ('48') then 'Q'      --PRANK CALL
           when calldispositionid in ('9') then 'A'      --CUSTOMER SERVICE
           when calldispositionid in ('40') then 'S'      --SPANISH SPEAKER
           when calldispositionid in ('1') then 'Y'      --SALE
           when calldispositionid in ('24') then 'W'      --UNKNOWN
           when calldispositionid in ('32') then 'V'      --UNDER 18
           when calldispositionid in ('39') then 'X'      --WANTS TO THINK ABOUT IT
           else 'H' end)    
 ,[Revenue] = '0'
 ,[Invoice_Number] = '0'
from bi_reporting.dbo.view_smdrdata_phone_system smdr
  left outer join report_weboms.dbo.tblcall c
   on c.ani = smdr.ani
where calldate = convert(varchar(18),GETDATE(),101)
  and c.ani like '97200%'
  and convert(varchar(19),c.callendtime,120) is not null
group by c.ani
order by c.ani

I'm need for the file to exporting into a .CSV file..that uses COMMAS....as the delimeter.

How can I tell the SQL COMMAND to export using COMMA DELIMETER....and CARRIAGE RETURN...as the row terminator....????

Thanks
MikeV
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dbaSQLConnect With a Mentor Commented:
see here:  http://msdn2.microsoft.com/en-us/library/ms165702.aspx
I think you should simply use the -s flag (colseperator)  for the sqlcmd utility

-s "," is for comma
0
 
chapmandewCommented:
I would use a codepage.  The switch is -f.  

http://technet.microsoft.com/en-us/library/ms162773.aspx
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Can you expand on your answer...???

HOW does a "code page" help me out....????

MIkeV
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MIKESoftware Solutions ConsultantAuthor Commented:
I can get the data to export...but... it exports with "TAB" delimeter,...I believe......how can I get COMMA DELIMETER.....???

MikeV
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Hum...I'll try again..but it wasn't working before....it appears to be defaulting to a SPACE as the delimeter....???

you do know that .CSV stands for CHARACTER separated values. Some people assume it means COMMA separated values but it does not...I need to tell it to place a COMMA as the delimeter.........

Thanks
MikeV
0
 
dbaSQLCommented:
no, i didn't know that.  i have always assumed that was 'comma separated/delimited values'
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
I've read throught the documentation...and the default is a SPACE....I'm trying to find how to change the defaul from SPACE to COMMA...?

If you can think of anything else..please let me know...THANSK

MikeV
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Ok...I got it close to what I need by using this:

sqlcmd -S 192.168.10.4 -i c:\ftp\calldataimport.sql -o c:\ftp\WOWCO_HBC_HMTB.csv -s"," -W -h -1

the -s"," is what tells it to use COMMA's.

the -h -1  removes the HEADERS which is also what I needed......

Thanks...
MikeV
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Thanks for your help....
0
 
dbaSQLCommented:
Are you saying -s "," isn't working?
0
 
dbaSQLCommented:
Ok, good.  That's what I was expecting would work.  Very good.
0
 
Anthony PerkinsCommented:
Than you had better correct Wikipeadia as well:
http://en.wikipedia.org/wiki/Comma-separated_values

And yes I am aware that the decimal notation is a comma in most of the world, which forces them to use simi-colons.  But in the anglo world CSV = Comma Separated Values.
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Well...hey......I stand corrected.... hum...I'll call it COMMA separated values....THEN...

SOunds like a winner......MikeV
0
 
Anthony PerkinsCommented:
Of course if you are German this may be more to your liking:
http://de.wikipedia.org/wiki/CSV_(Dateiformat)

:)
0
All Courses

From novice to tech pro — start learning today.