Pad fields for fixed-width export

I am writing a down and dirty vb.net app that simply does a query on a database and then write it to a text file.  Writing to the text file isn't a problem if it were comma delimited, but I need the fields to be fixed width with no commas.  Each db field needs to be a different length, so my question is how do I reference each database field while I am reading through the query results and writing the file?  For example, I need the output from the Cost field to be in the format 0000000.00.  But the description field should be 25 characters and padded with spaces instead of zeros.  I know how to do it for one field, but I don't know how to reference the fields as they are being read sequentially so that each field can be padded differently.

Using the Northwind db, I added a few fields to the categories table and the following is my code.  This code includes my commas right now, but I will be removing them once I can make the fields fixed length.

Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click
        Dim ds As System.Data.DataSet
        Dim mycommand As System.Data.OleDb.OleDbDataAdapter
        Dim myconnection As System.Data.OleDb.OleDbConnection

        myconnection = New System.Data.OleDb.OleDbConnection( _
            "provider=sqloledb; database=Northwind; " & _
            "Integrated Security=SSPI; server=DVLSERVER;")
        mycommand = New System.Data.OleDb.OleDbDataAdapter( _
            "select categoryid, categoryname, description, cost from categories", myconnection)

        ds = New System.Data.DataSet

        myconnection.Open()
        mycommand.Fill(ds)

        Dim sh As System.Text.StringBuilder = New System.Text.StringBuilder
        Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder

        Dim dc As DataColumn
        Dim cols() As Object
        For Each dc In ds.Tables(0).Columns
            sh.Append(dc.ColumnName)
            sh.Append(",")
        Next
        If sh.Length > 0 Then sh.Remove(sh.Length - 1, 1).Append(vbCrLf)
        Dim dr As DataRow
        For Each dr In ds.Tables(0).Rows
            cols = dr.ItemArray
            For Each a As Object In cols
                Dim b As String

                sb.Append(a.ToString)
                sb.Append(",")
            Next
            If sb.Length > 0 Then sb.Remove(sb.Length - 1, 1).Append(vbCrLf)
        Next
        Dim fw As System.IO.StreamWriter
        fw = New System.IO.StreamWriter("C:\MyFile.txt", True)

        fw.Write(sh.ToString)
        fw.Write(sb.ToString)

        fw.Close()
    End Sub
LVL 4
prairieitsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ptakjaConnect With a Mentor Commented:
Why not try something like this:

        For Each Row As DataRow In ds.Tables(0).Rows
            For Each col As DataColumn In Row.ItemArray
                Select Case col.ColumnName
                    Case "Description"
                         'Do your formatting for Description field here
                    Case "Cost"
                          'Do your formatting for Cost field here...etc...

                End Select

            Next

        Next
0
 
prairieitsAuthor Commented:
That's a great idea.  I just didn't know how to reference the fields on the fly.  I will plug it in and let you know my results.

Thanks Ptakja!

-Jerod
0
 
RonaldBiemansConnect With a Mentor Commented:
or try something like this

       For Each dr In ds.Tables(0).Rows
            For x As Integer = 0 To ds.Tables(0).Columns.Count - 1
                Select Case dr.Item(0).GetType.ToString
                    Case "System.String"
                        Dim yourstring As String = dr.Item(0).ToString.padleft(25,CChar(" "))
                    Case "System.Double"
                          'etc..
                    Case "System.Decimal"
                    Case "System.Int32"
                        Dim yourstring As String = dr.Item(0).ToString.PadLeft(8, CChar("0"))
                    Case "System.Int16"
                    Case "System.Int64"
                    Case Else
                        MsgBox("")
                End Select
            Next
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
prairieitsAuthor Commented:
Ronald,

That is a great idea too, except how would I handle 2 fields that both were system.string types with different padding requirements?  Say one has to be padded 10 characters and the other 15.

Thanks,
Jerod
0
 
RonaldBiemansCommented:
In that case ptakjas idea is the way to go.
0
 
prairieitsAuthor Commented:
Here's what I came up with.  Thanks for both of your answers!

-Jerod

   Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click
        Dim ds As System.Data.DataSet
        Dim mycommand As System.Data.OleDb.OleDbDataAdapter
        Dim myconnection As System.Data.OleDb.OleDbConnection
        Dim mydr As System.Data.OleDb.OleDbDataReader

        myconnection = New System.Data.OleDb.OleDbConnection( _
            "provider=sqloledb; database=Northwind; " & _
            "Integrated Security=SSPI; server=DVLSERVER;")
        mycommand = New System.Data.OleDb.OleDbDataAdapter( _
            "select productname, productid, unitprice from products, myconnection)

        ds = New System.Data.DataSet

        myconnection.Open()
        mycommand.Fill(ds)

        Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder

        Dim dc As DataColumn
        For Each row As DataRow In ds.Tables(0).Rows
            Dim myprodid As String = row.Item("ProductID").ToString.PadLeft(15, CChar(" "))
            sb.Append(myprodid)
            Dim myprodname As String = row.Item("ProductName").ToString.PadLeft(40, CChar(" "))
            sb.Append(myprodname)
            Dim myprodcost As String = row.Item("UnitPrice").ToString.PadLeft(15, CChar("0"))
            sb.Append(myprodcost)
            If sb.Length > 0 Then sb.Append(vbCrLf)
        Next
        Dim fw As System.IO.StreamWriter
        fw = New System.IO.StreamWriter("C:\detailfile.txt", False)
        fw.Write(sb.ToString)
        fw.Close()

        myconnection.Close()
        MsgBox("Your export has completed successfully.", MsgBoxStyle.OKOnly, "Export Complete")
    End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.