Solved

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

Posted on 2011-03-03
2
439 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 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