Solved

Pad fields for fixed-width export

Posted on 2004-08-20
6
268 Views
Last Modified: 2008-01-16
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
0
Comment
Question by:prairieits
[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
  • 3
  • 2
6 Comments
 
LVL 14

Accepted Solution

by:
ptakja earned 400 total points
ID: 11851764
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
 
LVL 4

Author Comment

by:prairieits
ID: 11851782
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
 
LVL 25

Assisted Solution

by:RonaldBiemans
RonaldBiemans earned 100 total points
ID: 11851954
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
Industry Leaders: 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!

 
LVL 4

Author Comment

by:prairieits
ID: 11852018
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11854841
In that case ptakjas idea is the way to go.
0
 
LVL 4

Author Comment

by:prairieits
ID: 11870433
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

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

Suggested Solutions

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …

735 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