?
Solved

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

Posted on 2011-03-03
2
Medium Priority
?
449 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 2000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

770 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