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
Solved

Pad fields for fixed-width export

Posted on 2004-08-20
6
267 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

790 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