SQLCMD export questoni....?


I'm using a SQLCMD like this:

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

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

 [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)
 ,[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....????

LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I would use a codepage.  The switch is -f.  

MIKESoftware Solutions ConsultantAuthor Commented:
Can you expand on your answer...???

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

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.....???

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.........

no, i didn't know that.  i have always assumed that was 'comma separated/delimited values'
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

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

sqlcmd -S -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......

MIKESoftware Solutions ConsultantAuthor Commented:
Thanks for your help....
Are you saying -s "," isn't working?
Ok, good.  That's what I was expecting would work.  Very good.
Anthony PerkinsCommented:
Than you had better correct Wikipeadia as well:

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.
MIKESoftware Solutions ConsultantAuthor Commented:
Well...hey......I stand corrected.... hum...I'll call it COMMA separated values....THEN...

SOunds like a winner......MikeV
Anthony PerkinsCommented:
Of course if you are German this may be more to your liking:

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.