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
 Exporting SQL table to text from in vs2010

Author  Topic 

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-04-06 : 20:11:26
I have a button on my winform that is exporting a sql table to a text file. It works fine but I have a few fields that are date (not date/time) but when it exports it is adding a time. Is there a way to just export the date that is in the table?

This is what it looks like after it is exported.
	11-06-08-01	3/14/2014 12:00:00 AM	6/8/2011 12:00:00 AM


This is the code I am running to export that SQL table.
Dim ds As New WebUpdateDataSet
Dim ta As New WebUpdateDataSetTableAdapters.DataTable1TableAdapter
ta.Fill(ds.DataTable1)

Dim dt As DataTable = ds.Tables("DataTable1")

Export("c:\ftpalloneea\awww.txt", dt)


Here is my export code.
  Public Sub Export(ByVal path As String, ByVal table As DataTable)
Dim output As New StreamWriter(path, False, UnicodeEncoding.Default)
Dim delim As String

delim = ""

' write out each data row
For Each row As DataRow In table.Rows
delim = ControlChars.Tab
For Each value As Object In row.ItemArray
output.Write(delim)
If TypeOf value Is String Then
output.Write(value)

Else
output.Write(value)
End If
delim = ControlChars.Tab
Next
output.WriteLine()
Next

output.Close()
End Sub


Thanks,
Stacy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-04-13 : 14:47:36
I think that may be because native data type is datetime which will always have associated time part defaulted to midnight. So CAST it first to date datatype if on SQL 2008 and over and then do the export. If earlier version only way is to convert it to varchar before the export.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-04-19 : 12:31:36
quote:
Originally posted by visakh16

I think that may be because native data type is datetime which will always have associated time part defaulted to midnight. So CAST it first to date datatype if on SQL 2008 and over and then do the export. If earlier version only way is to convert it to varchar before the export.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




I was able to get it to export just the date. This is my code.
    Public Sub Export(ByVal path As String, ByVal table As DataTable)
Dim output As New StreamWriter(path, False, UnicodeEncoding.Default)
Dim delim As String
delim = ""

' write out each data row
For Each row As DataRow In table.Rows
delim = ControlChars.Tab
For Each value As Object In row.ItemArray
output.Write(delim)
If TypeOf value Is String Then
output.Write(value)
Else
If TypeOf value Is Date Then
output.Write(CDate(value).ToShortDateString())
'Else
' output.Write(value)
End If
End If
delim = ControlChars.Tab
Next
output.WriteLine()
Next

output.Close()
End Sub


But I am getting a beginning tab on each row. So I thought I would try using SQL to export the table.
EXEC xp_cmdshell 'bcp "SELECT * FROM EA.dbo.webworld" queryout "C:\ftpalloneea\webtest.txt" -S "LAPTOP\SQLEX" -U ****  -P  ***** -c'


Which exports it without the beginning tab on each row, but now I am back to having the time exported with my date.

Is there a way using bcp to export date without time?

Thanks,
Stacy
Go to Top of Page
   

- Advertisement -