Export SQL Statement to Custom Delimited CSV

[dukeofawesome]

Hi Everyone,

This is my first post so be kind and bare in mind I am new to VB.Net coming from an Access/VBA background. This is my first attempt at a VB.Net app so just think of me as a complete NEWBIE.

On to my question - What I am trying to achieve is pass an SQL statement to a function or sub that will then process the dataset/recordset to a custom delimited CSV file. The delimiter required is ||| or triple pipe.

I am connecting to a SQL Server 2000 DB using a UDL file and everything works as expected with the connection. I am now stuck with what to do to get the export functionality. I have scoured the internet and am yet to find anything other than export to XML which is not what I need.

Any guidance from you Gurus would be much appreciated.

Regards

Duke

 

[TechGnome]

1) Loop through each row in the datatable
2) For each row...
3) Loop through each item (column) in the dataRow
4) Write out the field data, followed by a pipe
-next col
-next row

-tg

 

[dukeofawesome]

1) Loop through each row in the datatable
2) For each row...
3) Loop through each item (column) in the dataRow
4) Write out the field data, followed by a pipe
-next col
-next row

-tg

Thanks for your reply. You wouldn't have a code sample that I could have a look at would you?

 

[TechGnome]

Sure don't.... other wise I would have posted it...

-tg

 

[dukeofawesome]

OK, I think I'm getting there. I've almost got it apart from getting the field names which I need. Does anyone know how I could get the field names exported as well?

Here is what I've got so far:

    Public Sub DumpDataTable(ByVal fileName As String)
        'Connect to the data source.
        Dim objConn As New System.Data.OleDb.OleDbConnection( _
        "File Name=" & System.AppDomain.CurrentDomain.BaseDirectory() & "conn.udl")

        objConn.Open()

        'Execute a command to retrieve all records from the table.
        Dim objCmd As New System.Data.OleDb.OleDbCommand( _
            "Select * From tblProductsMainTable", objConn)
        Dim objReader As System.Data.OleDb.OleDbDataReader
        objReader = objCmd.ExecuteReader()

        'Read the records in the dataset and write select fields to the
        'output file.
        FileOpen(1, fileName, OpenMode.Output)
        Dim i As Integer, s As String, d As String
        s = ""

        While objReader.Read()
            'Loop through first 6 fields and concatenate
            'each field, separated by a tab, into s variable.
            s = ""
            For i = 0 To 38
                If Not objReader.IsDBNull(i) Then

                    d = objReader.GetDataTypeName(i)
                    'd = objReader.GetType()

                    Select Case objReader.GetDataTypeName(i)
                        Case "DBTYPE_I4"
                            s = s & objReader.GetInt32(i).ToString
                        Case "DBTYPE_WVARCHAR"
                            s = s & Chr(34) & objReader.GetString(i).ToString & Chr(34)
                        Case "DBTYPE_R8"
                            s = s & objReader.GetDouble(i).ToString  '.GetString(i).ToString
                        Case "DBTYPE_CY"
                            s = s & objReader.GetDecimal(i).ToString  '.GetString(i).ToString
                        Case "DBTYPE_BOOL"
                            s = s & objReader.GetBoolean(i).ToString  '.GetString(i).ToString
                        Case "DBTYPE_UI1"
                            s = s & objReader.GetValue(i).ToString  '.GetString(i).ToString
                        Case Else
                            s = s & objReader.GetValue(i).ToString
                    End Select

                End If
                s = s & "|" 'Microsoft.VisualBasic.ControlChars.Tab
            Next
            PrintLine(1, s)
        End While
        FileClose(1)

        'Close the reader and the connection.
        objReader.Close()
        objConn.Close()
    End Sub

 

[dukeofawesome]

OK, I think this is the final solution. One last question that I hope someone knows the answer to is if you open a DataAdaptor and Dataset do you have to close them once you're finished? I tried but it says ".Close" is not a member of this function.

Here is the code I ended up with for future reference and hopefully someone gets some benefit out of it:

Public Sub DumpDataTable(ByVal fileName As String)
        'Connect to the data source.
        Dim objConn As New System.Data.OleDb.OleDbConnection( _
        "File Name=" & System.AppDomain.CurrentDomain.BaseDirectory() & "conn.udl")

        objConn.Open()

        'Execute a command to retrieve all records from the table.
        Dim objCmd As New System.Data.OleDb.OleDbCommand( _
            "Select * From tblProductsMainTable", objConn)
        Dim objReader As System.Data.OleDb.OleDbDataReader
        objReader = objCmd.ExecuteReader()

        'Read the records in the dataset and write select fields to the
        'output file.
        FileOpen(1, fileName, OpenMode.Output)
        Dim i As Integer, s As String, d As String, hs As String
        hs = GetHeaders()
        PrintLine(1, hs)

        While objReader.Read()
            'Loop through fields and concatenate
            'each field, separated by a tab, into s variable.
            s = ""
            For i = 0 To 38
                If Not objReader.IsDBNull(i) Then

                    d = objReader.GetDataTypeName(i)
                    'd = objReader.GetType()

                    Select Case objReader.GetDataTypeName(i)
                        Case "DBTYPE_I4"
                            s = s & objReader.GetInt32(i).ToString
                        Case "DBTYPE_WVARCHAR"
                            s = s & Chr(34) & Replace(objReader.GetString(i).ToString, Chr(13), "<br>") & Chr(34)
                        Case "DBTYPE_R8"
                            s = s & objReader.GetDouble(i).ToString  '.GetString(i).ToString
                        Case "DBTYPE_CY"
                            s = s & objReader.GetDecimal(i).ToString  '.GetString(i).ToString
                        Case "DBTYPE_BOOL"
                            s = s & objReader.GetBoolean(i).ToString  '.GetString(i).ToString
                        Case "DBTYPE_UI1"
                            s = s & objReader.GetValue(i).ToString  '.GetString(i).ToString
                        Case Else
                            s = s & Chr(34) & objReader.GetValue(i).ToString & Chr(34)
                    End Select

                    'If i = 0 Then 'field 1 is EmployeeId
                    's = s & objReader.GetInt32(i).ToString
                    'ElseIf i = 5 Then 'field 6 is BirthDate
                    's = s & objReader.GetDateTime(i)
                    'Else 'field is a text field
                    's = s & objReader.GetString(i).ToString
                    'End If
                Else
                    s = s & "|"
                End If
                s = s & "|" 'Microsoft.VisualBasic.ControlChars.VerticalTab
            Next
            'PrintLine(1, hs)
            PrintLine(1, s)
        End While
        FileClose(1)

        'Close the reader and the connection.
        objReader.Close()
        objConn.Close()
    End Sub
    Public Function GetHeaders() As String
        Dim cn As New System.Data.OleDb.OleDbConnection( _
                 "File Name=" & System.AppDomain.CurrentDomain.BaseDirectory() & "conn.udl")
        Dim cmd As OleDb.OleDbCommand = cn.CreateCommand
        cmd.CommandText = "Select * from tblProductsMainTable"
        cmd.CommandType = CommandType.Text
        'Dim da As New OleDb.OleDbDataAdapter(cn)
        Dim da As New OleDb.OleDbDataAdapter(cmd)
        Dim ds As New DataSet
        Dim s As String

        s = ""

        da.Fill(ds)

        For Each c As DataColumn In ds.Tables(0).Columns
            'Debug.WriteLine(c.ColumnName)
            s = s & Chr(34) & c.ColumnName & Chr(34) & "|"
        Next

        GetHeaders = s


    End Function

 

[TechGnome]

Close your reader and close your connection.... the datadaptor isn't openable, so there's no way to close it. If you are done with it, call the .Dispose method and set it to nothing.

That's it.

-tg

 

[dukeofawesome]

Close your reader and close your connection.... the datadaptor isn't openable, so there's no way to close it. If you are done with it, call the .Dispose method and set it to nothing.

That's it.

-tg

Thanks TechGnome. I have been using DAO for many years and you had to clean up after yourself there. This is my first attempt at ADO and Datasets so it takes a little getting used to...

Many thanks for all your efforts.