VB.net DatagridView export to Fixed Length

Hello Experts,

I have a DataGridView that has been populated by SQL query. I want to export the data in the row in a fixed length format. (ie: 1 coloumn is 5 char, 2nd column is 7 chars, 3rd column is 2 chars, etc) Currently I have my code exporting to CSV, see below. I am lost on how to do fixed length....help?


        Try 'Export the results
            Dim filestartdate As String
            Dim fileenddate As String
            filestartdate = cal_StartDate.SelectionRange.Start.ToString("MM-dd-yyyy")
            fileenddate = cal_EndDate.SelectionRange.Start.ToString("MM-dd-yyyy")
            Dim exportpath As String
            Dim companyname As String
            Dim storenum As String
            companyname = dgv_SysInfo.Item(0, 0).Value.ToString
            storenum = dgv_SysInfo.Item(1, 0).Value.ToString
            exportpath = frm_Config.txt_ExportPath.Text
            Dim headers = (From header As DataGridViewColumn In dgv_Payroll.Columns.Cast(Of DataGridViewColumn)() _
          Select header.HeaderText).ToArray
            Dim rows = From row As DataGridViewRow In dgv_Payroll.Rows.Cast(Of DataGridViewRow)() _
                       Where Not row.IsNewRow _
                       Select Array.ConvertAll(row.Cells.Cast(Of DataGridViewCell).ToArray, Function(c) If(c.Value IsNot Nothing, c.Value.ToString, ""))
            Using sw As New IO.StreamWriter(exportpath & "\" & storenum & "-" & companyname & "-" & filestartdate & "   " & fileenddate & ".csv")
                sw.WriteLine(String.Join(",", headers))
                For Each r In rows
                    sw.WriteLine(String.Join(",", r))
                Next
            End Using
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message, "Error: Exception", MessageBoxButtons.OK, MessageBoxIcon.Stop)
            Exit Sub
        End Try

Open in new window

triphenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Danielle GoodrichEngineering Test TechnicianCommented:
Have you considered using the .PadLeft or .PadRight methods on your strings to make them a uniform length?
Fernando SotoRetiredCommented:
Hi triphen;

Something like the following should give you what you need.
==================================================
'' Test DataTable with Test Data
Dim dt As New DataTable 
Dim col As New DataColumn("Name", GetType(String))
dt.Columns.Add(col)
col = New DataColumn("Age", GetType(String))
dt.Columns.Add(col)
col = New DataColumn("Gender", GetType(String))
dt.Columns.Add(col)
'' Fill the DataTable
Dim dr As DataRow = dt.NewRow()
dr(0) = "Joseph"
dr(1) = "45"
dr(2) = "Male"
dt.Rows.Add(dr)
dr = dt.NewRow()
dr(0) = "Alice"
dr(1) = "30"
dr(2) = "Female"
dt.Rows.Add(dr)
dr = dt.NewRow()
dr(0) = "Jonathan"
dr(1) = "33"
dr(2) = "Male"
dt.Rows.Add(dr)
'' TestData Ends
==================================================

'' Start of actual code
'' The width array is the actual width for each column in the row
Dim width() As String = {"10", "2", "7"} 
'' Holds all the formatted string before writing to the file
Dim lines As New List(Of String)
'' Used so that the begining of each line does not get a comma
Dim firstTime As Boolean = True
'' Enumerate through the DataTable
For Each row As DataRow In dt.AsEnumerable()
    '' Initialize the variables used in the loop
    Dim line As String = String.Empty
    firstTime = True
    '' Format each row of the DataTable
    For i As Integer = 0 To dt.Columns.Count() - 1
        '' Only add a comma if it is not the begining of the line
        If Not firstTime Then 
            line += ","
        End If
        '' Width for this column is gotten from the width array
        line += String.Format("{0,-" + width(i)  + "}", row(i).Trim())
        '' We turn this on after processing the first column in the row
        firstTime = False
    Next
    '' Add the formatted line to the lines List
    lines.Add(line)
Next

'' Now you can write all the lines to the fileSystem

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.