Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Pad fields for fixed-width export

Posted on 2004-08-20
6
Medium Priority
?
278 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 1600 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 400 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
Technology Partners: 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

670 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