CSV Export - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CSV Export

Author

Topic

austink
Starting Member

7 Posts

Posted - 2010-05-12 : 09:31:32

Hi all - rookie DBA here and this my first post so I really hope you can help.

I have been asked to export a single table of CDR data every 10 minutes. My current challenges are:

1. To only extract the previous 10 minutes worth of data (and nothing older), and;

2. To name the extract the csv file with a specific name format - CDR_yyyymmdd_hhmm.csv

Your help would be massively welcomed.

Please let me know if you require my current stored procedure code.

Many thanks,
Kev

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 10:14:44

does it have a timestamp column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

austink
Starting Member

7 Posts

Posted - 2010-05-12 : 10:45:05

It does indeed sir, yes. I need to format the timestamp specifically to match the customer requirements.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 10:53:43

then its enough to check for timestamp values and pick everything which is greater than current time - 10 mins

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

austink
Starting Member

7 Posts

Posted - 2010-05-12 : 11:35:05

Excellent - thank you very much for your response.

Regarding my point 2 - I really need help how to extract each file with the specific name format of CDR_yyyymmdd_hhmm.csv

This is where I am struggling - the developer who advises me is unavailable :(

So far I have produced the stored procedure (if this helps you at all):

USE [CDRTest]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetCDRDataFormat]
AS
BEGIN

SET NOCOUNT ON;

select
'' as SourceKey,
convert(varchar, cast(substring(cdr.date,3,2) + '-' + left(cdr.date,2) + '-' + right(cdr.date,2) as datetime), 103) + ' ' +
convert(varchar, cast(left(cdr.time,2) + ':' + right(cdr.time,2) + ':00' as datetime), 108)
as ContactTimeStamp,
case
when cdr.condcode = '9' then '0' + right(cdr.callingnum,10)
when cdr.condcode = '7' then cdr.dialednum
else '0'
end as CTN,
'8892' as LocationID,
case
when cdr.condcode = '9' then cdr.dialednum
when cdr.condcode = '7' then cdr.clgnumintac
else '0'
end as PersonID,
case
when cdr.condcode = '9' then '7'
when cdr.condcode = '7' then '8'
else '0'
end as ProfileID,

case
when cdr.condcode = '9' then cdr.vdn
when cdr.condcode = '7' then '85000'
else '0'
end as ContactTypeID,
'' as SegmentID,
'' as DispositionID,
'' as ContactDuration,
'' as ContactTalkTime,
'' as Outcome,
'' as BaseID,
'' as SPID,
'' as IMEI,
'' as PricePlan,
'' as CommitEndDate,
'' as OverDueBill,
'' as TransactionValue
from
tbl_raw cdr
where
(
cdr.condcode = '9'
and
-- len(cdr.dialednum) > 5
-- and
-- (cast(cdr.dialednum as bigint) >= 50000) and (cast(cdr.dialednum as bigint) <= 50999)
PATINDEX('%56%', cdr.dialednum ) = 1
)
or
(
cdr.condcode = '7'
and
-- len(cdr.clgnumintac) > 5
-- and
-- (cast(cdr.clgnumintac as bigint) >= 50000) and (cast(cdr.clgnumintac as bigint) <= 50999)
PATINDEX('%56%', cdr.clgnumintac ) = 1
)
END

Your help would be greatly appreciated because I am not entirely confident where to go from here.

Many thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 11:36:43

CDR_yyyymmdd_hhmm.csv the date part will current datetime rite?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

austink
Starting Member

7 Posts

Posted - 2010-05-12 : 11:52:17

That's correct, yes.

Thank you for your time on this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 11:56:11

sorry but i cant see where you're giving file name in code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

austink
Starting Member

7 Posts

Posted - 2010-05-12 : 12:12:13

That's my problem sir. I don't know how to detail the output file name in the code. I am not sure where to begin! Perhaps I am over-complicating what is required but I am very new to this role and unfortunately have no-one to consult on the matter.

Is this something you could assist with?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:15:43

so you want above querys result to be put in a file with name including timestamp?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

austink
Starting Member

7 Posts

Posted - 2010-05-12 : 12:20:29

Yes, that sounds accurate Visakh. The file needs to generate every 10 minutes (with only the previous 10 minutes worth of data, nothing older) with the appropriate file name date and time stamp in the requested format (CDR_yyyymmdd_hhmm.csv).

:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:23:44

so what you need is to schedule a job in SQL agent with frequency of 10 mins and make logic like yourdatetimefield > DATEADD(minute,-10,GETDATE()) and use bcp query out to make file

see below example

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/bcp-export-data-to-text-file.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

austink
Starting Member

7 Posts

Posted - 2010-05-12 : 12:34:31

Perfect! Exactly what I required.

Thank you very much for your time and patience on this mate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources