Marcus Aurelius
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(ho ur, 1, c.calltime), 108))
,[SEGStop] = min(CONVERT(VARCHAR(10), DATEADD(hour, 1, c.callendtime),101) +' '+
CONVERT(VARCHAR,DATEADD(ho ur, 1, c.callendtime), 108))
,[Agent_ID] = min(c.employeeid)
,min(c.ani)[Calling_Pty]
,[VDN] = 'HMTB'
,[Duration] =min(CAST(substring(conver t(varchar( 19),c.call endtime-c. calltime,1 08),4,2) AS INT)*60
+CAST(RIGHT(substring(conv ert(varcha r(19),c.ca llendtime- c.calltime ,108),7,2) ,2) AS INT))
,[Hold_Time] = min(CAST(substring(convert (varchar(1 9),smdr.ri ngduration ,108),1,2) AS INT)*60
+CAST(substring(convert(va rchar(19), smdr.ringd uration,10 8),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_smdr data_phone _system smdr
left outer join report_weboms.dbo.tblcall c
on c.ani = smdr.ani
where calldate = convert(varchar(18),GETDAT E(),101)
and c.ani like '97200%'
and convert(varchar(19),c.call endtime,12 0) 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
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(ho
,[SEGStop] = min(CONVERT(VARCHAR(10), DATEADD(hour, 1, c.callendtime),101) +' '+
CONVERT(VARCHAR,DATEADD(ho
,[Agent_ID] = min(c.employeeid)
,min(c.ani)[Calling_Pty]
,[VDN] = 'HMTB'
,[Duration] =min(CAST(substring(conver
+CAST(RIGHT(substring(conv
,[Hold_Time] = min(CAST(substring(convert
+CAST(substring(convert(va
,[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
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_smdr
left outer join report_weboms.dbo.tblcall c
on c.ani = smdr.ani
where calldate = convert(varchar(18),GETDAT
and c.ani like '97200%'
and convert(varchar(19),c.call
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
ASKER
Can you expand on your answer...???
HOW does a "code page" help me out....????
MIkeV
HOW does a "code page" help me out....????
MIkeV
ASKER
I can get the data to export...but... it exports with "TAB" delimeter,...I believe......how can I get COMMA DELIMETER.....???
MikeV
MikeV
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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'
ASKER
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
If you can think of anything else..please let me know...THANSK
MikeV
ASKER
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
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
ASKER
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.
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.
ASKER
Well...hey......I stand corrected.... hum...I'll call it COMMA separated values....THEN...
SOunds like a winner......MikeV
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)
:)
http://de.wikipedia.org/wiki/CSV_(Dateiformat)
:)
http://technet.microsoft.com/en-us/library/ms162773.aspx