fixed width text document

[strat99]

Is there a way to export data that is returned in a datareader to a fixed width text document? The data in the reader is from a stored procedure that is being ran.

[CyberHawke]

yes, here is how to do it

' Imports statements
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' Declarations section, I hope most of these are self explanatory.
        Dim cn As New SqlConnection("packet size=4096;integrated security=SSPI;data source=(local);" & _
                                    "persist security info=False;initial catalog=northwind")
        Dim cmd As New SqlCommand("SELECT * FROM Customers", cn)
        Dim dr As SqlDataReader
        ' These are the known column widths for the table I am reading from.
        ' The columns are all varchar data
        Dim Widths() As Int32 = {0, 5, 40, 30, 30, 60, 15, 15, 10, 15, 24, 24}
        Dim Start As Int32
        Dim Indx As Int32
        Dim size As Int64 = 0
        Dim strBuff() As Byte
        ' File to write results to
        Dim fsFile As New StreamWriter("C:\Temp\Customers.txt")
 
        cn.Open()
        ' Open our reader
        dr = cmd.ExecuteReader
        While dr.Read
            ' need to reset the start value each pass
            Start = 0
            ' declare our stringbuilder with the length and maxlength values set to the sum of our column widths
            ' plus room for carriagereturn and linefeed terminators
            Dim sbRowData As New StringBuilder(270, 270)
            ' loop through our fields in the datareader
            For Indx = 0 To dr.FieldCount - 1
                ' set the starting point to insert each column in our stringbuilder
                Start += Widths(Indx)
                ' test for nulls, can't perform string functions on nulls
                If dr.IsDBNull(Indx) Then
                    ' got a null, pad an empty string
                    sbRowData.Insert(Start, "".PadRight(Widths(Indx + 1)))
                Else
                    sbRowData.Insert(Start, dr.GetString(Indx).PadRight(Widths(Indx + 1)))
                End If
            Next
            ' add our line terminators
            sbRowData.Insert(268, vbCrLf, 1)
            ' write our data to a fixedwidth text file.
            fsFile.Write(sbRowData.ToString)
        End While
        'cleanup
        fsFile.Flush()
        fsFile.Close()
        dr.Close()
        cn.Close()
    End Sub

This code works and produces a fixed width text file that contains all the rows in the Customers table of the Northwind database.

[strat99]

Thanks..... Worked like a champ...

[CyberHawke]

what does your name imply, strat (short for stratavarius)?