none
VB.NET - Export from SQL Server 2012 database to CSV

    Question

  • Hi Guys

    I have run into this problem and hope someone have either same issue or solution how to over come it.

    I am using sql server 2012 database with vb .net 3.5 application.

    My problem is when I try to export data from one of the table I get below error message

    here is the code

    sqlExport = "SELECT * INTO [Text;Database=" & System.IO.Path.GetDirectoryName(SaveFileDialog1.FileName) & ";HDR=Yes].[" & System.IO.Path.GetFileName(SaveFileDialog1.FileName) & "]FROM tbl_user"
    
    Dim SqlCommand As New System.Data.SqlClient.SqlCommand(sqlExport, SQLConn)

    I really appreciate any heads up.

    Thank you

    Sap


    • Edited by SapHelp Thursday, January 31, 2013 10:30 AM
    Wednesday, January 30, 2013 4:22 PM

Answers

  • Hi Andrew / The Thinker

    I have finally sorted out the problem. I am writing this to help the other users who may have the same issues.

    Import:

    Sql statment to use.

    sqlImport = "BULK INSERT " & sqlConn.Database & ".dbo.tbl_User FROM '" & System.IO.Path.GetDirectoryName(OpenFileDialog1.FileName) & OpenFileDialog1.SafeFileName & "' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',',  ROWTERMINATOR = '\n')"

    I have used the FIRSTROW to be 2 to allow for header to be ignored. It works for FIELDTERMINATOR = ","

    I have seen this post suggesting it won't work, however it works. Only diff is they are using "|" as FIELDTERMINATOR.

    Export:

    Exporting was bit tricky however, hail to the Google search and bit of improvisation below is the final code.

    Put below code into your click event

    Dim dt As DataTable = Me.DataSet_Test.tbl_User
    
    CreateCSVFile(dt, System.IO.Path.GetDirectoryName(SaveFileDialog1.FileName) & System.IO.Path.GetFileName(SaveFileDialog1.FileName))

    and below is the CreateCSVFile Function

    Public Sub CreateCSVFile(dt As DataTable, strFilePath As String)
    
            ' Create the CSV file to which datatable will be exported.
            Dim sw As New StreamWriter(strFilePath, False)
    
            ' First we will write the headers.
            Dim iColCount As Integer = dt.Columns.Count
            For i As Integer = 0 To iColCount - 1
                sw.Write(dt.Columns(i))
                If i < iColCount - 1 Then
                    sw.Write(",")
                End If
            Next
            sw.Write(sw.NewLine)
    
            ' Now write all the rows.
            For Each dr As DataRow In dt.Rows
                For i As Integer = 0 To iColCount - 1
                    If Not Convert.IsDBNull(dr(i)) Then
                        sw.Write(dr(i).ToString())
                    End If
                    If i < iColCount - 1 Then
                        sw.Write(",")
                    End If
                Next
                sw.Write(sw.NewLine)
            Next
            sw.Close()
        End Sub

    above code has been converted to vb from this C# example

    Enjoy !!

    Sap

    • Marked as answer by SapHelp Thursday, January 31, 2013 3:21 PM
    Thursday, January 31, 2013 1:12 PM
  • The below code works for me:

            Dim ConnectionString As String
    
            ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & "C:\Documents and Settings\...\My Documents\My Database\Text" & ";" & _
                "Extended Properties=""Text;HDR=YES;"""
    
            Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            TextConnection.Open()
    
            Dim TextCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Customers#txt] FROM [Customers] IN '' [ODBC;Driver={SQL Server Native Client 10.0};Server=.\SQLExpress;Database=Northwind;Trusted_Connection=yes;]", TextConnection)
            TextCommand.CommandType = CommandType.Text
            TextCommand.ExecuteNonQuery()
            TextCommand.Dispose()
            TextConnection.Close


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by SapHelp Thursday, January 31, 2013 3:20 PM
    Thursday, January 31, 2013 2:30 PM
  • First a slimmed down version which can be called as a regular function or as a language extension to a DataTable.

    Module Module1
        ''' <summary>
        ''' Write Datatable data to a delimited text file
        ''' </summary>
        ''' <param name="dt"></param>
        ''' <param name="FileName">Path and file name to save data too</param>
        ''' <param name="Delimitor">Character to delimit columns</param>
        ''' <returns>Indicates success or failure</returns>
        ''' <remarks></remarks>
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function ToCsv(ByVal dt As DataTable, ByVal FileName As String, ByVal Delimitor As String) As Boolean
            Dim Result As Boolean = True
            Dim sb As New System.Text.StringBuilder
            Try
                ' Do header row, you may not want quotes around column names
                sb.AppendLine(String.Join(",", (From T In dt.Columns.Cast(Of DataColumn)() Select Chr(34) & T.ColumnName & Chr(34)).ToArray))
                ' This works on Null values
                For Each row As DataRow In dt.Rows
                    sb.AppendLine(String.Join(Delimitor, row.ItemArray))
                Next
                ' Write data to disk
                IO.File.WriteAllText(FileName, sb.ToString)
            Catch ex As Exception
                Result = False
            End Try
            Return Result
        End Function
        ''' <summary>
        ''' Write Datatable data to a delimited text file using a comma to delimit columns
        ''' </summary>
        ''' <param name="dt"></param>
        ''' <param name="FileName">Path and file name to save data too</param>
        ''' <returns>Indicates success or failure</returns>
        ''' <remarks></remarks>
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function ToCsv(ByVal dt As DataTable, ByVal FileName As String) As Boolean
            Return dt.ToCsv(FileName, ",")
        End Function
    End Module

    Usage where dt is a DataTable with data

    If dt.ToCsv(FileName) Then
        MessageBox.Show("Finished")
    Else
        MessageBox.Show("Failed")
    End If

    Example output done where some fields have null values

    "Identifier","FirstName","MiddleName","LastName"
    1,Kevin,Sean,Gallagher
    2,Mary,Jane,Jones
    3,,,
    4,Karen,,Smith


    kevininstructor

    • Marked as answer by SapHelp Thursday, January 31, 2013 3:20 PM
    Thursday, January 31, 2013 2:45 PM

All replies

  • Your select command looks wrong you normally have the information after * in your projects config file (its in xml format). This information is not in the sql statement which is only for selecting information from tables in an SQL database. That information usually is passed to your sql connection variable you have in the above code. The select statement requires a table name.

    For reference here are some proper sql connection strings I found on this website (I used the ones on this website myself when I ran into a similar issue):

    http://www.connectionstrings.com/sql-server-2012

    use sql server management studio to help develop the query for you (I use it to test my queries on test sql servers before I mess any data up at work) and if it does not execute in sql management studio then it might be incorrect.


    Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth. - "Sherlock holmes" "speak softly and carry a big stick" - theodore roosevelt. Fear leads to anger, anger leads to hate, hate leads to suffering - Yoda. Blog - http://www.computerprofessions.co.nr



    • Edited by The Thinker Wednesday, January 30, 2013 5:59 PM
    Wednesday, January 30, 2013 5:53 PM
  • Do you have a reference for that SQL statement? I can find nothing remotely like it for SQL Server, MySql or Oracle.

    --
    Andrew

    Wednesday, January 30, 2013 7:11 PM
  • Hi The Thinker

    Thank you very much for your reply.

    Below is my connection string. It works fine.

     

    I cannot see any problem with it.

    Also I tried below sql statement in SSMS and it works perfectly, when i run it it does create a new table with data from tbl_user.

    SELECT * INTO tbl_export FROM tbl_user 

    Only thing I am doing in my app is, asking user to select where they want to save and what filename using saveFileDialog control for csv export.

    Below is converted sql statement after user has selected where they want to save and with what filename.

    It works fine for my access database backend and I am upscaling it for sql server now.

    Any idea ??

    Thursday, January 31, 2013 10:08 AM
  • Hi Andrew

    Thank you very much for your reply.

    Below is the sql statment after user has selected where they want export and with what filename.

    Here all the data from tbl_user will be exported to test.csv on c: drive.

    Any heads up ?

    Kind regards

    Sap

    Thursday, January 31, 2013 10:20 AM
  • It works fine for my access database backend and I am upscaling it for sql server now.

    It appears that that syntax is Access-specific and you will have to find a different method to use with SQL Server. I suggest that you do a web search for "SQL Server 2012 export csv".

    Any further questions are likely to belong in an SQL Sever forum, not this VB.NET forum.

    HTH,

    Andrew

    Thursday, January 31, 2013 11:32 AM
  • Hi Andrew

    Thanks for your reply.

    I have made bit of a headway and got my import working fine. Below is the code how it work, just in case anyone face similar problem. I am going to tackle the export now.

    sqlImport = "BULK INSERT " & sqlConn.Database & ".dbo.tbl_User FROM '" & System.IO.Path.GetDirectoryName(OpenFileDialog1.FileName) & OpenFileDialog1.SafeFileName & "' WITH (FIELDTERMINATOR = ',',  ROWTERMINATOR = '\n')"

    Another option to consider would be OPENRAWSET as well. Let's see how it goes.

    I will copy the question into sql server forum as well.

    Appreciate your time to look it.

    Sap

    Thursday, January 31, 2013 11:44 AM
  • Hi Andrew / The Thinker

    I have finally sorted out the problem. I am writing this to help the other users who may have the same issues.

    Import:

    Sql statment to use.

    sqlImport = "BULK INSERT " & sqlConn.Database & ".dbo.tbl_User FROM '" & System.IO.Path.GetDirectoryName(OpenFileDialog1.FileName) & OpenFileDialog1.SafeFileName & "' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',',  ROWTERMINATOR = '\n')"

    I have used the FIRSTROW to be 2 to allow for header to be ignored. It works for FIELDTERMINATOR = ","

    I have seen this post suggesting it won't work, however it works. Only diff is they are using "|" as FIELDTERMINATOR.

    Export:

    Exporting was bit tricky however, hail to the Google search and bit of improvisation below is the final code.

    Put below code into your click event

    Dim dt As DataTable = Me.DataSet_Test.tbl_User
    
    CreateCSVFile(dt, System.IO.Path.GetDirectoryName(SaveFileDialog1.FileName) & System.IO.Path.GetFileName(SaveFileDialog1.FileName))

    and below is the CreateCSVFile Function

    Public Sub CreateCSVFile(dt As DataTable, strFilePath As String)
    
            ' Create the CSV file to which datatable will be exported.
            Dim sw As New StreamWriter(strFilePath, False)
    
            ' First we will write the headers.
            Dim iColCount As Integer = dt.Columns.Count
            For i As Integer = 0 To iColCount - 1
                sw.Write(dt.Columns(i))
                If i < iColCount - 1 Then
                    sw.Write(",")
                End If
            Next
            sw.Write(sw.NewLine)
    
            ' Now write all the rows.
            For Each dr As DataRow In dt.Rows
                For i As Integer = 0 To iColCount - 1
                    If Not Convert.IsDBNull(dr(i)) Then
                        sw.Write(dr(i).ToString())
                    End If
                    If i < iColCount - 1 Then
                        sw.Write(",")
                    End If
                Next
                sw.Write(sw.NewLine)
            Next
            sw.Close()
        End Sub

    above code has been converted to vb from this C# example

    Enjoy !!

    Sap

    • Marked as answer by SapHelp Thursday, January 31, 2013 3:21 PM
    Thursday, January 31, 2013 1:12 PM
  • The below code works for me:

            Dim ConnectionString As String
    
            ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & "C:\Documents and Settings\...\My Documents\My Database\Text" & ";" & _
                "Extended Properties=""Text;HDR=YES;"""
    
            Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            TextConnection.Open()
    
            Dim TextCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Customers#txt] FROM [Customers] IN '' [ODBC;Driver={SQL Server Native Client 10.0};Server=.\SQLExpress;Database=Northwind;Trusted_Connection=yes;]", TextConnection)
            TextCommand.CommandType = CommandType.Text
            TextCommand.ExecuteNonQuery()
            TextCommand.Dispose()
            TextConnection.Close


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by SapHelp Thursday, January 31, 2013 3:20 PM
    Thursday, January 31, 2013 2:30 PM
  • First a slimmed down version which can be called as a regular function or as a language extension to a DataTable.

    Module Module1
        ''' <summary>
        ''' Write Datatable data to a delimited text file
        ''' </summary>
        ''' <param name="dt"></param>
        ''' <param name="FileName">Path and file name to save data too</param>
        ''' <param name="Delimitor">Character to delimit columns</param>
        ''' <returns>Indicates success or failure</returns>
        ''' <remarks></remarks>
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function ToCsv(ByVal dt As DataTable, ByVal FileName As String, ByVal Delimitor As String) As Boolean
            Dim Result As Boolean = True
            Dim sb As New System.Text.StringBuilder
            Try
                ' Do header row, you may not want quotes around column names
                sb.AppendLine(String.Join(",", (From T In dt.Columns.Cast(Of DataColumn)() Select Chr(34) & T.ColumnName & Chr(34)).ToArray))
                ' This works on Null values
                For Each row As DataRow In dt.Rows
                    sb.AppendLine(String.Join(Delimitor, row.ItemArray))
                Next
                ' Write data to disk
                IO.File.WriteAllText(FileName, sb.ToString)
            Catch ex As Exception
                Result = False
            End Try
            Return Result
        End Function
        ''' <summary>
        ''' Write Datatable data to a delimited text file using a comma to delimit columns
        ''' </summary>
        ''' <param name="dt"></param>
        ''' <param name="FileName">Path and file name to save data too</param>
        ''' <returns>Indicates success or failure</returns>
        ''' <remarks></remarks>
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function ToCsv(ByVal dt As DataTable, ByVal FileName As String) As Boolean
            Return dt.ToCsv(FileName, ",")
        End Function
    End Module

    Usage where dt is a DataTable with data

    If dt.ToCsv(FileName) Then
        MessageBox.Show("Finished")
    Else
        MessageBox.Show("Failed")
    End If

    Example output done where some fields have null values

    "Identifier","FirstName","MiddleName","LastName"
    1,Kevin,Sean,Gallagher
    2,Mary,Jane,Jones
    3,,,
    4,Karen,,Smith


    kevininstructor

    • Marked as answer by SapHelp Thursday, January 31, 2013 3:20 PM
    Thursday, January 31, 2013 2:45 PM
  • Hi Kevin,

    Thank you very much for your take.

    It always amazes me the beauty of logic.

    I really like your logic.

    Thanks once again for all the effort to post your solution.

    Kind regards

    Sap

    Thursday, January 31, 2013 3:15 PM
  • Afternoon Paul,

    Hope you are well. Really pleased to here from you.

    I was soo looking forward for you to look into this yesterday. I was using your code before which you really kindly helped me a while back for my access database.

    I noticed you are using ODBC to communicate with SQL server. Would you advise me to go for the same path in case, I will have numous end users for this app and subsequently all will need ODBC setup.

    If you were in my shoes what you would do?

    Thank you

    Kind regards

    Sap

    Thursday, January 31, 2013 3:20 PM
  • There is nothing wrong with using ODBC in this instance. It is fully supported. I would check the machines of other users to see if they have the driver installed. If this is an issue then you may want to stick with using the native .NET Provider and the BULK INSERT method. Keep in mind that the SqlBulkCopy Class is available, althought the coding is a bit more involved.

    Otherwise, I would simply recommend using the method that is most flexible for you. The example I posted is fairly direct, requires very little code and will generate a CSV file with column headers. If you want to use a different column delimiter or formatting then a schema.ini file would be required.

    Also, keep in mind that the Jet OLEDB Provider is limited to 32-bit. If at some point you switch to 64-bit Windows and Office 2007 or higher, then the ACE OLEDB Provider would be required.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, January 31, 2013 3:44 PM
  • Thank you Paul.

    Really appreciate it.

    Can you pleaes describe "Keep in Mind that SqlBulkCopy Class is available"

    Do I need to take any special measures for it.

    Kind regards

    Sap

    Thursday, January 31, 2013 4:04 PM
  • Actually scratch that suggestion. That method is for importing data into SQL Server.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, January 31, 2013 4:13 PM
  • No Probs Paul.

    Thank you.

    Do you know what's the best way to search a datagridview?

    Kind regards

    Sap

    Thursday, January 31, 2013 4:14 PM
  • I would recommend posting a new question. That way many more forum members will see it and be able to respond.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, January 31, 2013 4:50 PM
  • Hi Andrew / The Thinker

    I have finally sorted out the problem. I am writing this to help the other users who may have the same issues.

    Import:

    Sql statment to use.

    sqlImport = "BULK INSERT " & sqlConn.Database & ".dbo.tbl_User FROM '" & System.IO.Path.GetDirectoryName(OpenFileDialog1.FileName) & OpenFileDialog1.SafeFileName & "' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',',  ROWTERMINATOR = '\n')"

    I have used the FIRSTROW to be 2 to allow for header to be ignored. It works for FIELDTERMINATOR = ","

    I have seen this post suggesting it won't work, however it works. Only diff is they are using "|" as FIELDTERMINATOR.

    Export:

    Exporting was bit tricky however, hail to the Google search and bit of improvisation below is the final code.

    Put below code into your click event

    Dim dt As DataTable = Me.DataSet_Test.tbl_User
    
    CreateCSVFile(dt, System.IO.Path.GetDirectoryName(SaveFileDialog1.FileName) & System.IO.Path.GetFileName(SaveFileDialog1.FileName))

    and below is the CreateCSVFile Function

    Public Sub CreateCSVFile(dt As DataTable, strFilePath As String)
    
            ' Create the CSV file to which datatable will be exported.
            Dim sw As New StreamWriter(strFilePath, False)
    
            ' First we will write the headers.
            Dim iColCount As Integer = dt.Columns.Count
            For i As Integer = 0 To iColCount - 1
                sw.Write(dt.Columns(i))
                If i < iColCount - 1 Then
                    sw.Write(",")
                End If
            Next
            sw.Write(sw.NewLine)
    
            ' Now write all the rows.
            For Each dr As DataRow In dt.Rows
                For i As Integer = 0 To iColCount - 1
                    If Not Convert.IsDBNull(dr(i)) Then
                        sw.Write(dr(i).ToString())
                    End If
                    If i < iColCount - 1 Then
                        sw.Write(",")
                    End If
                Next
                sw.Write(sw.NewLine)
            Next
            sw.Close()
        End Sub

    above code has been converted to vb from this C# example

    Enjoy !!

    Sap


    Iam glad you got it sorted. I had one project where I had to export data from a SQL server table at my work to csv format for two days (one for each day) and then comapre those csv files and send an email when they are different (mcafee though can be annoying when it blocks emails thinking my program is malware despite adding exceptions to it).

    Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth. - "Sherlock holmes" "speak softly and carry a big stick" - theodore roosevelt. Fear leads to anger, anger leads to hate, hate leads to suffering - Yoda. Blog - http://www.computerprofessions.co.nr

    Thursday, January 31, 2013 5:35 PM