Today, I had a new project dropped on my desk and was informed that by the end of the week, I need to have exported the entire payroll into a new format.

it exports to csv, which is then imported into access, and needs to be exported into a csv or excel.
Right now it looks like:

|Emp Number| |Deduction1| |Deduction2| |Deduction3|

and the format I need to turn it into:

|Emp Number| |Deduction1|
|Emp Number| |Deduction2|
|Emp Number| |Deduction3|

I've got everything loaded into datagrids in VB, but can't figure out how to get it from one format to the next. Anyone?

without the coding for the datagrid it might be difficult, but I'll try with a couple of assumptions. First I would assume that when you say datagrid, you actually mean the newer datagridview. Second I would assume that Emp Number is the first cell of the row and that the deductions are in consecutive cells in the same row. Third I would assume that your 'csv' is actually comma delimited. With these assumptions this code might help:

    Dim Output As New List(Of String)
    For Each row As DataGridViewRow In DataGridView1.Rows
        Dim empNumber As String = row.Cells(0).ToString
        For I = 1 To 3
            Output.Add(empNumber + "," + row.Cells(I).ToString + vbCrLf)
        Next
    Next
    System.IO.File.WriteAllLines("MyFile.csv", Output.ToArray)

If my assumptions are worng and this doesn't help, you'll probably have to supply the code that you are using to build your current file.

Edited 3 Years Ago by tinstaafl

Thank you, tinstaafl, I'm looking forward to using that code. your assumptions are all correct.
kRod, I loaded the original csv (comma seperated values) file into an access database, then tied that into the datagridview via the add data source.

You're very welcome. I just noticed one thing. The lines in the file might end up being double spaced, if so take out the + vbCrLf in Output.Add(empNumber + "," + row.Cells(I).ToString + vbCrLf).

Tinstaafl, I ran your code. The first time it surprised the hell out of me by crashing Visual Studio :) but thats because I was telling it to write to c:\myfile.csv. Changed that to my documents folder and it worked great.
Now, what I'm getting in the csv file is:

DataGridViewTextBoxCell { ColumnIndex=0, RowIndex=0 },DataGridViewTextBoxCell { ColumnIndex=1, RowIndex=0 }

Instead of the data. What'd I do wrong?

Still doing it. This is what I have:

Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DRDataSet.Earnings' table. You can move, or remove it, as needed.
        Me.EarningsTableAdapter.Fill(Me.DRDataSet.Earnings)
        Dim Output As New List(Of String)
        For Each row As DataGridViewRow In DataGridView1.Rows
            Dim empNumber As String = row.Cells(0).ToString
            For I = 1 To 3
                Output.Add(empNumber + "," + row.Cells(I).Value + vbCrLf)
            Next
        Next
        System.IO.File.WriteAllLines("C:\Users\fantus\Documents\test.txt", Output.ToArray)
    End Sub
End Class

Using your code, now that I've got everything slapped in there, I try to run it, but get an error.

Conversion from string "DataGridViewTextBoxCell { Column" to type 'Double' is not valid.

on

output.Add(empNumber + "," + intAccNum.Cells(i).Value + vbCrLf)

What am I doing wrong?

my code;

Public Class Form1
    Dim dbProvider As String = "PROVIDER=microsoft.ace.oledb.12.0;"
    Dim dbSource As String = "Data Source = C:\D&R\Data\DR.accdb"
    Dim conn As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim sql As String
    Dim query As String = "SELECT AcctNum,SDI,Medicare,State,Fed,TotWages FROM Earnings INNER JOIN Personel On Earnings.SS=Personel.SS"
    Dim command As New OleDb.OleDbCommand(query, conn)

    Private Sub connect()
        conn.ConnectionString = dbProvider & dbSource
        conn.Open()
        MsgBox(conn.State.ToString)
    End Sub

    Private Sub dgPopulate()
        connect()
        da.SelectCommand = command
        da.Fill(ds)
        Dim dt As New DataTable
        da.Fill(dt)

        Me.dgEmployees.DataSource = dt
        conn.Close()
    End Sub

    Private Sub export()
        Dim dgCount As Integer
        dgCount = dgEmployees.Rows.Count
        Dim output As New List(Of String)
        For Each intAccNum As DataGridViewRow In dgEmployees.Rows

            Dim empNumber As String = intAccNum.Cells(0).ToString
            For i = 1 To 5
                output.Add(empNumber + "," + intAccNum.Cells(i).Value + vbCrLf)
            Next
        Next
        System.IO.File.WriteAllLines("C:\users\fantus\documents\test.txt", output.ToArray)
    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        dgPopulate()
    End Sub

    Private Sub ExportToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles ExportToolStripMenuItem.Click
        export()
    End Sub
End Class

try changing this:Dim empNumber As String = intAccNum.Cells(0).ToString to Dim empNumber As String = intAccNum.Cells(0).Value

I changed the line of code to output.Add(empNumber & "," & inserts(i - 1) & CStr(intAccNum.Cells(i).Value))
Thanks for all your help!

This question has already been answered. Start a new discussion instead.