reading line by line from excel and more...

[Levu]

Dear forum readers and trolls.
Iv gotten into a rather nasty problem (atleast i think so)..

Im trying to make a program that opens 2x excel docs.
It then reads the excel docs. but the problem is, im supposed to crosscheck both of them. Meaning, In one of the excel docs, I got alot of numbers:
202
204
206
208
210 <-- etc etc, going on for some..
Then I will load the other excel doc, and the program is supposed to crosscheck with whats in the excel doc nr2 and excel doc nr1..
So when some number is NOT in the first excel doc but in the 2nd doc, its supposed to write it down, and store it.

Only thing iv managed is to load the excel doc into a datagridview. :S
Anyone that can help me understand or give me some code help that can do this?

Thanks in advance, Levu

 

[jimbaloo]

unless you are allowing a user to check the values visually you are adding overhead by placing the values into a datagridview. Instead try reading the excel docs into datasets and check the rows in the datasets.

 

[Levu]

thats what iv been trying (atleast).

    Dim myconnection As System.Data.OleDb.OleDbConnection

        Try
    'DataGridViewTextBoxCell { ColumnIndex=0, RowIndex=0 }
    Dim dtset As System.Data.DataSet
    Dim mycommand As System.Data.OleDb.OleDbDataAdapter
    Dim drexcel As OleDb.OleDbDataReader

            myconnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.jet.oledb.4.0; Data source='" & path1 & "'; extended properties=Excel 8.0")

    'mycommand = New System.Data.OleDb.OleDbDataAdapter("Select * From [data$A1]", myconnection)

            dtset = New System.Data.DataSet
            mycommand.Fill(dtset)

But how do i go though row by row?

 

[jimbaloo]

for each dr as datarow in dtset.tables(0).rows
'you are in the next row of the dataset
'if you have everything in a single dataset then compare columns
'if you have two dataset compare rows
next

 

[Levu]

for each dr as datarow in dtset.tables(0).rows
'you are in the next row of the dataset
'if you have everything in a single dataset then compare columns
'if you have two dataset compare rows
next

aaah, smart..
can you give me a code example on how to crossreference the with 2x datasets? thanks alot

 

[jimbaloo]

             Dim n As Integer = 0
            For Each dr In ds.Tables(0).Rows 'Show results in output window
                Dim row As DataRow
                row = ds2.Tables(0).Rows(n)
                If dr.Item(0) = row.Item(0) Then
                    n += 1
                End If
            Next

ds2 is the second dataset. you read each row from the first (only) table in that and compare it to the current row in the first dataset ds.
don't increment n inside the if. that would be bad

 

[Levu]

How do i do it if I got more colums at one of the excel docs?
Wont it compair all the values in eatch colum at the specific row?

I also get this error:
Object reference not set to an instance of an object.

So i tried do:
dim dr as datarow

dr= new datarow
But then I get error: Error 1 'System.Data.DataRow.Protected Sub New(builder As System.Data.DataRowBuilder)' is not accessible in this context because it is 'Protected'.


and another problem again:
I tried to do: msgbox(dtset2.tables(0).rows(1).tostring)
only thing i get back is: system.data.datarow

Im supposed to get the record at row 1 am i not? :S

Looks like this now:

            Dim str(10000) As String
            Dim k As Integer

            For Each dr In dtset.Tables(0).Rows
                Dim row As DataRow
                row = dtset2.Tables(0).Rows(n)
                If dr.Item(0) = row.Item(0) Then
                    n += 1
                Else
                    str(k) = row.Item(n)
                    k = k + 1
                End If

            Next

AND: at the same time, the codes in the 2nd excel doc, stand like this:2039938
So im just supposed to check with the 3 first numbers..
But since everytime i try convert datarow thingy over to string, it gets cranky..
So how can i do index of? or manipulate it? :S So much hassle

 

[Levu]

Nvm, iv got it all to work now

thanks for the help

 

[cjard]

Dear ... trolls.

Here? Nahh.. Me and jmcilhinney are about as sarcastic and nasty as we come

and the program is supposed to crosscheck with whats in the excel doc nr2 and excel doc nr1..

Upload both excel files to a database, and use a LEFT OUTER JOIN
If you have no proper database, attach the Microsoft Jet driver to the excel files, (see connectionstrings.com) and use them as the database