Solved

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

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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

810 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