Extracting Data using a view into .csv format

Question

Post reply

Extracting Data using a view into .csv format

Jpotucek

SSCoach

Points: 18583
More actions
November 17, 2005 at 7:25 am

#90824

My question is basically two parts..
I have a view that produces the results I need to send to a Vendor in .csv format .. here is the code:
SELECT DISTINCT
                      dbo.Schedule_Reports.Report_Show_Date, dbo.Schedule_Reports.Report_Show_Time, dbo.Schedule_Reports.Report_Sort_Order,
                      dbo.Schedule_Storer.Storer_Show_End_Time, dbo.Schedule_Storer.Storer_Show_Name, dbo.Schedule_Storer.Storer_House_No,
                      dbo.Vw_Genre_Catgry_Shows.Category_Code, dbo.Vw_Genre_Catgry_Shows.Category_Desc, dbo.Vw_Genre_Catgry_Shows.Genre_Code,
                      dbo.Vw_Genre_Catgry_Shows.Genre_Desc, DATENAME(Weekday, dbo.Schedule_Reports.Report_Show_Date) AS WD,
                      dbo.Schedule_Storer.Storer_Episode_No, dbo.Schedule_Storer.Storer_Episode_Title, dbo.Schedule_Storer.Storer_Premiere_Flag,
                      dbo.Schedule_Storer.Storer_Live_SDD_Flag, dbo.Schedule_Storer.Storer_Ratings_Code, dbo.Schedule_Storer.Storer_CC_Flag
FROM         dbo.Schedule_Reports LEFT OUTER JOIN
                      dbo.Schedule_Storer ON dbo.Schedule_Reports.Report_Show_Date = dbo.Schedule_Storer.Storer_Show_Date AND
                      dbo.Schedule_Reports.Report_Show_Time = dbo.Schedule_Storer.Storer_Show_Start_Time LEFT OUTER JOIN
                      dbo.Vw_Genre_Catgry_Shows ON dbo.Schedule_Storer.Storer_Show_Name = dbo.Vw_Genre_Catgry_Shows.Show_Name
I just exec my view in using DTS and pump the output to a .csv file.. no problem.
But what they are asking for now is a row delimiter on the file that we ulitmately send them. Is there anyway in the SQL code or with DTS to add a 'character' at the end of each row in the .csv file output???    Or is this something that can only be done using VB or something like that ??????
any input would be appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

What's the delimeter?

You can always add a value at the end of your view

SELECT DISTINCT

dbo.Schedule_Reports.Report_Show_Date, dbo.Schedule_Reports.Report_Show_Time, dbo.Schedule_Reports.Report_Sort_Order,

dbo.Schedule_Storer.Storer_Show_End_Time, dbo.Schedule_Storer.Storer_Show_Name, dbo.Schedule_Storer.Storer_House_No,

dbo.Vw_Genre_Catgry_Shows.Category_Code, dbo.Vw_Genre_Catgry_Shows.Category_Desc, dbo.Vw_Genre_Catgry_Shows.Genre_Code,

dbo.Vw_Genre_Catgry_Shows.Genre_Desc, DATENAME(Weekday, dbo.Schedule_Reports.Report_Show_Date) AS WD,

dbo.Schedule_Storer.Storer_Episode_No, dbo.Schedule_Storer.Storer_Episode_Title, dbo.Schedule_Storer.Storer_Premiere_Flag,

dbo.Schedule_Storer.Storer_Live_SDD_Flag, dbo.Schedule_Storer.Storer_Ratings_Code, dbo.Schedule_Storer.Storer_CC_Flag,

'#' 'row_delimeter'

FROM dbo.Schedule_Reports LEFT OUTER JOIN

dbo.Schedule_Storer ON dbo.Schedule_Reports.Report_Show_Date = dbo.Schedule_Storer.Storer_Show_Date AND

dbo.Schedule_Reports.Report_Show_Time = dbo.Schedule_Storer.Storer_Show_Start_Time LEFT OUTER JOIN

dbo.Vw_Genre_Catgry_Shows ON dbo.Schedule_Storer.Storer_Show_Name = dbo.Vw_Genre_Catgry_Shows.Show_Name

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 2

By definition a CSV file has the comma Column delimiter and the CRLF (CHAR(13)+CHAR(10)) Row delimiter. Using anything else makes it no longer a CSV file.

Anyway the DTS Export to CSV would still append the CRLF as the row delimiter.

Andy