Link to home
Start Free TrialLog in
Avatar of Marcus Aurelius
Marcus AureliusFlag for United States of America

asked on

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
Avatar of chapmandew
chapmandew
Flag of United States of America image

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

http://technet.microsoft.com/en-us/library/ms162773.aspx
Avatar of Marcus Aurelius

ASKER

Can you expand on your answer...???

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

MIkeV
I can get the data to export...but... it exports with "TAB" delimeter,...I believe......how can I get COMMA DELIMETER.....???

MikeV
ASKER CERTIFIED SOLUTION
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
no, i didn't know that.  i have always assumed that was 'comma separated/delimited values'
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
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
Thanks for your help....
Are you saying -s "," isn't working?
Ok, good.  That's what I was expecting would work.  Very good.
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.
Well...hey......I stand corrected.... hum...I'll call it COMMA separated values....THEN...

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

:)