How to Write Data to a Flat Data Output File Using Visual Basic. NET 2010

Posted on 2011-03-03
Last Modified: 2012-05-11
I was wondering if someone could help me write an example piece of code to output a set of data to a flat output file that has a carriage return for a new line and is comma delimited file. I have attached a copy of the code being used to output the data along with some sample SQL code, which is reading the data out. The data being written out is only the data that is pulled from the query. I do have the query working correctly, but I am having a little bit of trouble writing the file out to a "hard coded" named file for the output. Any help with an example of just doing one or two lines would be greatly appreciated.
Namespace SO

    <Serializable()> Public Class EDIAckOutboundFileProcess : Inherits ReadOnlyBase(Of EDIAckOutboundFileProcess)

        Private mErrorMessage As String = ""
        Private mSONumber As String

        Private Sub New()
            ' Require use of factory methods.
        End Sub

        Public ReadOnly Property ErrorMessage() As String
                Return mErrorMessage
            End Get
        End Property

        Public Shared Function GetProcess(ByVal SONumber As String) As EDIAckOutboundFileProcess
            Return DataPortal.Fetch(Of EDIAckOutboundFileProcess)(New Criteria(SONumber))
        End Function

        <Serializable()> Private Class Criteria
            Public ReadOnly SONumber As String
            Public Sub New(ByVal SONumber As String)
                Me.SONumber = SONumber
            End Sub
        End Class

        Private Overloads Sub DataPortal_Fetch(ByVal criteria As Criteria)
            mSONumber = criteria.SONumber
            Using cn As SqlConnection = OpenDBConnection()
                Using cmd As SqlCommand = cn.CreateCommand
                    cmd.CommandType = CommandType.Text
                    cmd.CommandText = "Select st.Customer, so.SONumber, so.DateOrdered, so.ShipMethod, " &
                        "edih.CustomerPO, edih.SenderID, edih.ReleaseNumber, edih.PODate, " &
                        "lm.StockingAttentionTo As SupplierName, lm.StockingAddressLine1 As SupplierAddressLine1, " &
                        "lm.StockingAddressLine2 As SupplierAddressLine2, lm.StockingCity As SupplierCity, " &
                        "lm.StockingStateOrProvince As SupplierStateOrProvince, lm.StockingPostalCode As SupplierPostalCode, " &
                        "lm.StockingCountry As SupplierCountry, lm.StockingAttentionTo As ShippedFromName, " &
                        "lm.StockingAddressLine1 As ShippedFromAddressLine1, lm.StockingAddressLine2 As ShippedFromAddressLine2, " &
                        "lm.StockingCity As ShippedFromCity, lm.StockingStateOrProvince As ShippedFromStateOrProvince, " &
                        "lm.StockingPostalCode As ShippedFromPostalCode, lm.StockingCountry As ShippedFromCountry, " &
                        "cust.AttentionTo As BilledToName, cust.AddressLine1 As BilledToAddressLine1, " &
                        "cust.AddressLine2 As BilledToAddressLine2, cust.City As BilledToCity, " &
                        "cust.StateOrProvince As BilledToStateOrProvince, cust.PostalCode As BilledToPostalCode, " &
                        "cust.Country As BilledToCountry, edih.ShipToName As ShippedToName, " &
                        "edih.ShipToAddress1 As ShippedToAddressLine1, edih.ShipToAddress2 AS ShippedToAddressLine2, " &
                        "edih.ShipToCity As ShippedToCity, edih.ShipToState As ShippedToStateOrProvince, " &
                        "edih.ShipToZipCode As ShippedToPostalCode, '' As ShippedToCountry, edih.SenderID As GSSenderID " &
                        "FROM tblsoSO so " &
                        "INNER JOIN tblsoEDIAckQuote edih ON edih.QuoteNumber = so.QuoteNumber " &
                        "INNER JOIN tblarCustomerShipTo st ON st.CustomerShipTo = so.CustomerShipTo " &
                        "INNER JOIN tblsaLocationMaster lm ON lm.Location = so.SellingLocation " &
                        "INNER JOIN tblarCustomer cust ON cust.Customer = st.Customer;"

                    With cmd.Parameters
                        .Add("@SONumber", SqlDbType.Char).Value = mSONumber
                    End With

                    Using dr As New SafeDataReader(cmd.ExecuteReader)
                        'Read the header result
                        If dr.Read Then
                            Dim header As New EDIAckHeader(dr)

                            'Output header results
                            'Nothing to Error
                        End If
                    End Using
                End Using
            End Using

        End Sub

        Protected Overrides Function GetIdValue() As Object
            Return mSONumber
        End Function

    End Class
End Namespace

Open in new window

Question by:thenthorn1010

Accepted Solution

rawinnlnx9 earned 500 total points
ID: 35031045
Below I'm showing you how and where. You have to parse the fields and format the output but that is trivial.

For full explanations read this:

I liked this one too:

Using dr As New SafeDataReader(cmd.ExecuteReader)
Dim oFile as System.IO.File
Dim oWrite as System.IO.StreamWriter
oWrite = oFile.CreateText(“C:\sample.txt”)

Open in new window

                    'Read the header result
                        If dr.Read Then
                            Dim header As New EDIAckHeader(dr)
                            oWrite.WriteLine(“Write a line to the file”)
                            oWrite.WriteLine()         ‘Write a blank line to the file

                            'Output header results
                            'Nothing to Error
                        End If
                    End Using

Open in new window

Formatting the output
The Write and WriteLine methods both support formatting of text during output. The ability to format the output has been significantly improved over previous versions of Visual Basic. There are several overloaded methods for producing formatted text. Let’s look at one of these methods:
oWrite.WriteLine(“{0,10}{1,10}{2,25}”, “Date”, “Time”, “Price”)
oWrite.WriteLine(“{0,10:dd MMMM}{0,10:hh:mm tt}{1,25:C}”, Now(), 13455.33)

Open in new window


Expert Comment

ID: 35031152
Another way to do it is to create a new ODBC connection to a Text file. Then you can use the System.Data.Odbc namespace objects to write the data. No formatting required.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question