VB and Access and Excel

[404notfound]

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?

[tinstaafl]

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.

[kRod]

How did you load the DataGridView? Where did that data come from?

[404notfound]

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.

[tinstaafl]

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).

[404notfound]

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?

[tinstaafl]

oops, a little type. instead of row.Cells(I).ToString use row.Cells(I).Value

[404notfound]

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

[tinstaafl]

You need to change this one too, row.Cells(0).ToString

[404notfound]

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

[tinstaafl]

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

[404notfound]

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