Exporting SQL Server 2005 data in CSV files

Question

Exporting SQL Server 2005 data in CSV files

HBK_4700

SSCrazy Eights

Points: 8932
More actions
June 23, 2010 at 8:38 am

#141677

Hi,
We are using SQL Server 2005 and we need to export the data(250 tables) into CSV files.
But client needs the CSV files in Unix format, is there any feature available in Sql or do we have any third party tools? Please advice

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 1

I think the Unix format just uses an <LF> as a row terminator instead of the Windows <CRLF>. You should be able to select <LF> as your row terminator by using the "Ragged Right" file option in SSIS.

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 2

Thank you,

I have used SQL IMPORT/EXPORT and successfully exported data into CSV file (Unix format).

But I have a issue with data/time format. Some of the fields only display Hour and Minute, instead of HH:MM:SS.

I need the following format

MM/DD/YYYY HH:MM:SS AM/PM

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 3

You can add a Script Component in your Data Flow to standardize your date format. Select a "Transformation" type. Select all of the input fields. Make the datetime field read-write. The following code will execute for each incoming row to cast the incoming text date as a Date type and apply the format you want:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'

' Add your code here

'

Dim dte As DateTime

Dim DateOut As String

dte = CDate(Row.PROCDATE)

Row.PROCDATE = Format(dte, "MM/dd/yyyy hh:mm:ss tt")

End Sub

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 4

Ed Zann (6/24/2010)
You can add a Script Component in your Data Flow to standardize your date format. Select a "Transformation" type. Select all of the input fields. Make the datetime field read-write. The following code will execute for each incoming row to cast the incoming text date as a Date type and apply the format you want:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Dim dte As DateTime
Dim DateOut As String
dte = CDate(Row.PROCDATE)
Row.PROCDATE = Format(dte, "MM/dd/yyyy hh:mm:ss tt")
End Sub

after selecting datetime field readwrite, can you explain the steps in indetail. Thank you