Solved

Pad fields for fixed-width export

Posted on 2004-08-20
6
264 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
  • 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
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 …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

785 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