Solved

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

Posted on 2011-03-03
2
443 Views
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
            Get
                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
                        Else
                            'Nothing to do...party-No Error
                            Return
                        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

0
Comment
Question by:thenthorn1010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 9

Accepted Solution

by:
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: http://www.techrepublic.com/article/read-and-write-text-files-with-visual-basic-net/1045309

I liked this one too: http://www.startvbdotnet.com/files/default.aspx


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”)
OpenText

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
                        Else
                            'Nothing to do...party-No Error
                            Return
                        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)
oWrite.Close()

Open in new window

0
 
LVL 1

Expert Comment

by:jeff77tor
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

726 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