[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

reading excel file using OLEDB

Posted on 2006-06-06
46
Medium Priority
?
510 Views
Last Modified: 2008-01-09
Hi,

I neeed to write a program to read an excel file column amd if any of the excel file row item item does not match
with the my sql server database column then i need move nomatching rows into another worksheet in the same excel file .

I will elucidate in detail below

My sql server table sales got two fields            AcctNo  nvarchar(50)
                                                    Time      Date/Time

My Excel file first two columns headers are          ReferenceNo      AcctNo
 
I need to check each rows in SerialNo column that should match with my database field AcctNo

If some of the keys are no matching, then i need to move those nomatching from AcctNo column into another worksheet called
nomatch.


i have designed the form in such a way that user can select the excel file using open file dialog box and since i
got few weeks experience in programing , i am totally stuck and pls help me to rewrite my code in  a professional way to meet the above requirement. I am very new to datarow and datatable concept also. Hope somebody can help me on this issue

 Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        OpenExcel.ShowDialog()

    End Sub

    Private Sub openexcel_FileOk(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenExcel.FileOk
        Dim i As Integer
        Dim filename As String
        filename = OpenExcel.FileName
        Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & filename & ";Extended Properties=""Excel 8.0;HDR=NO"""
        Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
        ExcelConnection.Open()
        Dim sql As String = "select * from [Sheet1$] "
        Dim da As New System.Data.OleDb.OleDbDataAdapter(sql, ExcelConnection)
        Dim ds As New DataSet("Workbooks")
        Dim dtvw As New DataTable
        da.Fill(ds, "Sheet1")
        dtvw = ds.Tables("Sheet1")
        If ds.Tables(0).Rows.Count = 0 Then
            MsgBox(" No Account no in excel file", MsgBoxStyle.OKOnly, "ExceL Empty")
            Exit Sub
            Dim SQLCon As SqlConnection = New SqlConnection("Server = ServerIP; User ID= sa ; pwd=sales; Database=sales")
            Dim SQLTRAN As SqlTransaction
            Dim SQLCmd As SqlCommand = New SqlCommand
            SQLCon.Open()
            SQLTRAN = SQLCon.BeginTransaction("TRAN")
            SQLCmd.Connection = SQLCon
            SQLCmd.Transaction = SQLTRAN

            SQLCmd.CommandText = " If not Exist(select * from sales Where SerialNo = '" & ds.Tables.Rows(0)  & "'") --How do i get the datarow

            ...............................     Move the no matching to another worlsheet.

             SQLCmd.ExecuteScalar()
 

         



0
Comment
Question by:nyee84
  • 30
  • 15
46 Comments
 

Author Comment

by:nyee84
ID: 16840944
Somebody can help???
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16841125
OK I just tried out a small code for you
But this is a little different approach
I am using Excel 10.0 Object Library, to use it you need to Add Reference to "Microsoft Excel 10.0 Object Library" from the Add Reference --> COM Tab

I have assumed here a Database Table with Two Columns "SerialNo" and "AcctNo"
And the Excel has Two Columns as well "SerialNo" and "AcctNo" I am matching the Cols "SerialNo" in both
Here it is

Dim DT As New DataTable
Dim ConnectionString As String = "Your SQL Connection String"
Dim DA AS New SQLDataAdapter("Select * From Sales", ConnectionString)
DA.Fill (DT)  ' Fill the DataTable from SQL so we need not Query the DB again and agian

Dim XL As New Excel.Application
Dim CurrWS As Excel.WorkSheet
Dim NewWS as Excel.WorkSheet
XL.Workbooks.Open("C:\Salesdata.xls") ' Give path of your Excel File
CurrWS=XL.ActiveSheet 'By Default the first Sheet, otherwise specify the sheet name
NewWS=XL.Worksheets.Add ' Add a new sheet for the moved rows
Dim Serial as String
For i As Integer = CurrWS.UsedRange.Rows.Count To 1 Step -1
     Serial=CurrWS.Cells(i,"A").Value
     If DT.Select("SerialNo='" & Serial & "'").Length <= 0) Then
            'If there is no Matching Row in the DB for this Serial number move the row to the new Sheet
           CurrWS.Cells(i,"A").EntireRow.Copy() 'Copy the Row
           NewWS.Cells(65000,1).End(Excel.XLDirection.xlUp).Offset(1,0).Select()  'Go to the Last Row
           NewWS.Paste()  'Paste it
           CurrWS.Cells(i,"A").EntireRow.Delete() ' Now delete row in original sheet
      End If
Next
XL.Quit
XL=Nothing

That's it...
Now if you open your Excel File you'd see a new Sheet added to it containing the Non Matching Rows
0
 
LVL 12

Expert Comment

by:vb_jonas
ID: 16841183
If your table is huge then you might want to query for each row:

            sqlCommand.CommandText = "SELECT COUNT(SerialNo) AS RowCount FROM SALES WHERE SerialNo = " & SerialNo
            if sqlCommand.ExecuteScalar() = 0 then
                'Add Row to Sheet2
            End If
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:nyee84
ID: 16841477
Thanks for your help, but still some discrepancies and some minor changes are there..

1) Issue no 1
I hve created an excel sheet as shown below, One of the header is SerialNo

SerialNo
123456           ----Matching
245666          -----NoMatching

After i run the program i found out that

the header SerailNo also moved to the new worksheet which we are creating for no matching during run time,

Sheet2            Sheet1

245666        123456
SerialNo

2) My live excel file fomat shown below

      AccountNo    SerialNo    
      44141414     4H53555
      25452552     3S25252
      41414145     2525525
      25252525     4E552YR

For ex: If am moving(no matching) serialNo  3S25252, 2525525, the corresponding account no still remains there
are we able to delete the coresponding AccountNo  25452552, 41414145. How do we change the program logic.
What will happen middle some rows been moved into another worksheet, does this process will do cell up...

Pls help ...

Thaks in advance....







0
 

Author Comment

by:nyee84
ID: 16841771
Any updates??
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16841830
ok I didn't take care of the Header
you can filter out the header by changing the Loop

change the line
For i As Integer = CurrWS.UsedRange.Rows.Count To 1 Step -1

To
For i As Integer = CurrWS.UsedRange.Rows.Count To 2 Step -1  '<-- upto Row 2

so that now we do not do anything for the Row 1
0
 

Author Comment

by:nyee84
ID: 16841901
The first issue already been resolved by changing the loop as what you have mentioned above
Any advice on the 2nd issue.  

Thanks

0
 

Author Comment

by:nyee84
ID: 16843891
Hi Arif,

Kindly provide me the code to tuckle the 2nd issue which i mentioned above..

Thanks

0
 

Author Comment

by:nyee84
ID: 16849166
Anxiously waiting for you reply!!
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16849356
Hi
There should not be issues like you have mentioned, the whole row should be moved not just the column
You have said that the Account Number Column still remains there, It should not happen, have you tried the code ??
When we say  
CurrWS.Cells(i,"A").EntireRow.Delete()
the whole row gets deleted so the account number should not be there....
Also if rows from middle are moved the rows would Shift up so this should also not be the issue

However, I am comparing on the first column
Serial=CurrWS.Cells(i,"A").Value
your SerialNo field is second column so instaed of A use B
Serial=CurrWS.Cells(i,"B").Value

So if your Excel is like this

      AccountNo    SerialNo    
      44141414     4H53555
      25452552     3S25252     <--- No Match
      41414145     2525525     <--- No Match
      25252525     4E552YR

and as shown the two rows have no matching row in the database, then the Excel after the code would be like this

      AccountNo    SerialNo    
      44141414     4H53555
      25252525     4E552YR

and a New Sheet would have

      25452552     3S25252    
      41414145     2525525    
0
 

Author Comment

by:nyee84
ID: 16849798
Sorry i have tried only with one column which was serialNo and i presume that the the complete row wont remove,
I have tested just now and it is working file now .what you were saying in you above comment is abosolutely right... Lack of programming experience leh.. Let ,me ask you 2 minors qn and then i can close this post.

Currently i put a button in my form and when the user click on the button, the user should be able to select the excel file using open diglog box and i will pass in that filename in  your code,

1)How do we change the worksheet name to "nomatch" instead creating new workseet, means program will create a worksheet called no match and all the no match rows will move into "nomatch" worksheet.
2) Let say if i use the same excel with the rows values i used before, again,( "nomatch" worksheet is already been created and all the nomatching keys moved), are we able to avioid the creation of nomatch again and  copy the rows which is already exist in no matching worksheet.

2)The excel object is still in the memory even after program finish procesiing, how do we kill the process. I always neeed to end task the excel.exe object...

3) Finally how should make use of jonas query in sqlc ommand
sqlCommand.CommandText = "SELECT COUNT(SerialNo) AS RowCount FROM SALES WHERE SerialNo = " & SerialNo
            if sqlCommand.ExecuteScalar() = 0 then
                'Add Row to Sheet2
            End If

   Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click

        OpenExcel.ShowDialog()
        Dim filename As String
        filename = OpenExcel.FileName
        Dim DT As New DataTable
        Dim ConnectionString As SqlConnection = New SqlConnection("Server=192.168.3.51;User id=sa;pwd=gensales;Database=sales")
        Dim DA As New SqlDataAdapter("Select * From salessource", ConnectionString)
        DA.Fill(DT)   ' Fill the DataTable from SQL so we need not Query the DB again and agian

        Dim XL As New Excel.Application
        Dim CurrWS As Excel.Worksheet
        Dim NewWS As Excel.Worksheet
        XL.Workbooks.Open(filename) ' Give path of your Excel File
        CurrWS = XL.ActiveSheet 'By Default the first Sheet, otherwise specify the sheet name
        NewWS = XL.Worksheets.Add ' Add a new sheet for the moved rows
        Dim Serial As String
        For i As Integer = CurrWS.UsedRange.Rows.Count To 2 Step -1
            Serial = CurrWS.Cells(i, "B").Value
            If DT.Select("SerialNo='" & Serial & "'").Length <= 0 Then
                'If there is no Matching Row in the DB for this Serial number move the row to the new Sheet
                CurrWS.Cells(i, "B").EntireRow.Copy() 'Copy the Row
                NewWS.Cells(65000, 1).End(Excel.XlDirection.xlUp).Offset(1, 0).Select() 'Go to the Last Row
                NewWS.Paste()  'Paste it
                CurrWS.Cells(i, "B").EntireRow.Delete() ' Now delete row in original sheet
            End If
        Next
        XL.Quit()
        XL = Nothing
        MsgBox("Completed")
    End Sub

0
 

Author Comment

by:nyee84
ID: 16850068
Hi Arif,

The current logic will crete  new sheets , if i use same file to append(how do we avoid that process),need a default worksheet "nomacth", if the nomatching rows were already in this worksheet, then pro wont be moving nomatching rows again to the "nomatch worskeet"

Any help on this issue would be greatly appreciated..
0
 

Author Comment

by:nyee84
ID: 16850202
Experts, Pls help
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16850226
Sorry nyee84 for the delay
all these can be done
would get back to you in some time
0
 

Author Comment

by:nyee84
ID: 16850247
Will be able to provide the solution bytoday?  This is very urgent as i need to complete this program bytoday itself

Anway thanks for your help
0
 

Author Comment

by:nyee84
ID: 16850583
Hi ,

Pls proivide me the guidence to resolve above issues and then i can close this post quickly!

Need a solution asap as i need to finish this program by today..

Thanks
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16850633
OK Here's the new one
Now here you would be opening the same sheet again and again, first time there would not be any Sheet called "NOMATCH" so it will create one, next time the sheet would be there in the same excel file so it would just move the rows to this sheet. Now for this your file initially would have at least one sheet that will have the data, there ought to be a name for this sheet as well, here I would assume this Sheet is called "SERIAL"


Private Function MoveNoMatch(FileName As String) 'Now a function that takes file path for the XL FIle

Dim DT As New DataTable
Dim ConnectionString As String = "Your SQL Connection String"
Dim DA AS New SQLDataAdapter("Select * From Sales", ConnectionString)
DA.Fill (DT)  ' Fill the DataTable from SQL so we need not Query the DB again and agian

Dim XL As New Excel.Application
Dim CurrWS As Excel.WorkSheet
Dim NewWS as Excel.WorkSheet
XL.Workbooks.Open(FileName )  '<--Changed
' CurrWS=XL.ActiveSheet 'Now since the same file is used again and again the Active Sheet might not be the same so I am assuming a Sheet called SERIAL is having the data to match
CurrWS=XL.Sheets("SERIAL")
For each ws As Worksheet in XL.Worksheets
    If ws.Name="NOMATCH" Then
       NewWS=ws  'If sheet already there get a reference
       Exit For
    End If
Next

if NewWS Is Nothing Then
   NewWS=XL.Worksheets.Add ' Add a new sheet if it did not exist earlier
   NewWS.Name="NOMATCH"
End If

Dim Serial as String
For i As Integer = CurrWS.UsedRange.Rows.Count To 2 Step -1
     Serial=CurrWS.Cells(i,"A").Value
     If DT.Select("SerialNo='" & Serial & "'").Length <= 0) Then
            'If there is no Matching Row in the DB for this Serial number move the row to the new Sheet
           CurrWS.Cells(i,"A").EntireRow.Copy() 'Copy the Row
           NewWS.Cells(NewWS.UsedRange.Rows.Count + 2, "A").EntireRow.Select()  'Go to the Last Row
           NewWS.Paste()  'Paste it
           CurrWS.Cells(i,"A").EntireRow.Delete() ' Now delete row in original sheet
      End If
Next
XL.ActiveWorkbook.Save()
XL.ActiveWorkbook.Close()
XL.Application.Quit
XL=Nothing

End Sub

hopefully this should do
0
 

Author Comment

by:nyee84
ID: 16850728
Arif,

One dout, what will happpen if the NOMATCH worksheet already some no matching serialno, and i use the same excel to append some more serialno in the existing "SERIAL" worksheet,  Does program will create duplicate matching rows in "NOMATCH" worksheet if i use the above code?

SerialNo

313133   ---Already in NOMATCH worksheet
414414 ---- Already in NOMATCH worsheet
141414 ---- New Nomatch row
646466-----New Nomacth row

What will be in the existing NOMATCH worksheet once after i exceute the program
only  141414 ---- New Nomatch row
646466-----New Nomacth row will move in , Isnt it. so all the requirements satisfied...

Pls comment  



0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16850935
Well in case the "Serial" Sheet has duplicate rows then there will be Duplicate in "NoMatch: sheet as well
lets check out an eg.

First Attempt:  
At start the Excel contains Sheet Serial, but no Sheet NoMatch

Serial Sheet:

      AccountNo    SerialNo    
      A1               S1
      A2               S2    <--- No Match
      A3               S3    <--- No Match
      A4               S4
      A5               S5

After running the program the Excel Sheet would contain 2 both Sheets Serial as well as NoMatch

Serial Sheet: (S3 and S4 are removed)

      AccountNo    SerialNo    
      A1               S1
      A4               S4
      A5               S5

NoMatch Sheet: (S3 and S4 rows are moved here)

      A2               S2    
      A3               S3    

Now Attempt 2:
Suppose we add 2 new rows to the Serial Sheet,

Serial Sheet:

      AccountNo    SerialNo    
      A1               S1
      A3               S3    <--- No Match, New Row Added
      A4               S4
      A5               S5
      A6               S6    <-- No Match, New Row Added

NoMatch Sheet:

      A2               S2    
      A3               S3    

Now if you see the Serial sheet the new row added is A6 S6 and A3 S3, Now A3 S3 was there before also and it was moved to NoMatch, it is there in NoMatch sheet, this time again there will not be any match so it will again be moved to NoMoatch
So after running the program for second time

Serial Sheet:

      AccountNo    SerialNo    
      A1               S1
      A4               S4
      A5               S5

NoMatch Sheet:

      A2               S2    
      A3               S3    
      A6               S6
      A3               S3

So you see, S3 will again be moved, ideally if a row has already been moved it might not come again in the Serial sheet but if you want we can add the logic to just discard this S3 row if it is already there in NoMatch sheet

0
 

Author Comment

by:nyee84
ID: 16850984
Got an error message, once i trying to move a no matching row which already in nomacth worksheet.

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in microsoft.visualbasic.dll

Additional information: Select method of Range class failed

 NewWS.Cells(NewWS.UsedRange.Rows.Count + 2, "B").EntireRow.Select()  ---Giving error

Kindly advice..
0
 

Author Comment

by:nyee84
ID: 16851267
Hi Arif,

SERIAL Sheet

ACCOUNTNO            SERIALNO            
   
 A1                          4S2242424              --->>>>No match agian
 A2                          424242424

NOMATCH sheet

A1                          4S2242424              ------>Already in NOMATCH

Got the error, mean if a row is already in NOMATCH, when i  pro try to move from serial sheet, it will thow the above error.

I beleieve by deleting the existing row programically may solve the program ,not sure how to resolve it, Pls advice
0
 

Author Comment

by:nyee84
ID: 16851325
So you see, S3 will again be moved, ideally if a row has already been moved it might not come again in the Serial sheet but if you want we can add the logic to just discard this S3 row if it is already there in NoMatch sheet

pls add that logic also..
0
 

Author Comment

by:nyee84
ID: 16851570
SERIAL Sheet

ACCOUNTNO            SERIALNO            
   
 A1                          4S2242424              --->>>>No match agian
 A2                          424242424

NOMATCH sheet


Space
Space
Space
Space
A1                          4S2242424              ------>Already in NOMATCH   - When i insert some space it is working...

so code   NewWS.Cells(NewWS.UsedRange.Rows.Count + 2, "B").EntireRow.Select()    ---causing problem  
0
 
LVL 19

Accepted Solution

by:
arif_eqbal earned 2000 total points
ID: 16851705
"Select method of Range class failed"
this error is quite a problem, I am not getting it here, so one thing is its not deterministic also same code will give this error at times and at times it won't

I got this error once and I corrected it using the "Activate" method before it and activating the Sheet before selecting so it should help you as well

Anyway, so now the new logic

Everything remains Same... Inside the Loop and inside the If block there would be changes here is that part

     If DT.Select("SerialNo='" & Serial & "'").Length <= 0) Then
           CurrWS.Cells(i,"A").EntireRow.Copy()

           'Check if This row is already in the NoMatch Sheet or Not
           If NewWS.Cells.Find(What:=Serial, SearchOrder:=XLSearchOrder.xlByRows, SearchDirection:=XLSearchDirection.xlNext, SearchFormat:=False) Is Nothing Then

                NewWS.Activate()  'To avoid the Error you are getting
                NewWS.Rows(NewWS.UsedRange.Rows.Count + 2).EntireRow.Select()  
                NewWS.Paste()  'Paste it

           End If

           CurrWS.Cells(i,"A").EntireRow.Delete() ' Now delete row in original sheet
      End If


0
 

Author Comment

by:nyee84
ID: 16851931
Hi,

Showing  "SearchFormat" is not a parameter for public override function Find[].......As Excel.Range ??????


 If NewWS.Cells.Find(What:=Serial, SearchOrder:=Excel.XLSearchOrder.xlByRows, Excel.SearchDirection:=XLSearchDirection.xlNext, SearchFormat:=False) Is Nothing Then

Thks
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16852197
ok just remove that parameter

 If NewWS.Cells.Find(What:=Serial, SearchOrder:=Excel.XLSearchOrder.xlByRows, Excel.SearchDirection:=XLSearchDirection.xlNext) Is Nothing Then

0
 

Author Comment

by:nyee84
ID: 16858416
Hi Arif,


Everthing seems to be fine except the existing rows(not duplicate) in NOMATCH worksheet is getting replced if a new no matching row is added to the NOMATCH worksheet.

The new logic is working perfectly: Existing row in NOMATCH  will remains intact if new row is exist in the NOMATCH worksheet..

Finally my database table got millions of records so when filling the datatable process is making the program to get freez for a while and lasly how do i incorporate  
            sqlCommand.CommandText = "SELECT COUNT(SerialNo) AS RowCount FROM SALES WHERE SerialNo = " & SerialNo
            if sqlCommand.ExecuteScalar() = 0 then
                'Add Row to Sheet2
            End If

So 99% is completed, will accept your solution soon
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16858841
>> existing rows(not duplicate) in NOMATCH worksheet is getting replced if a new no matching row is added to the NOMATCH worksheet

It should not happen its not happening here, when you say
                NewWS.Rows(NewWS.UsedRange.Rows.Count + 2).EntireRow.Select()  
                NewWS.Paste()  'Paste it
It should go to the First empty row UsedRange.Rows.Count would give you the number of rows already there in the NOMATCH sheet, say it is 10 then we go and add at 12 ( +2) because first row is empty supposedly for header.
So it should not overwrite.

As for the SQLCommand Query, I avoided it because it would require a round trip to the database for each record in the Excel, that will be much annoying, if you want to use that then instead of loading the dataTable and seraching there you need to call this database query every time
instead of       If DT.Select("SerialNo='" & Serial & "'").Length <= 0) Then
But I would not suggest that, specially in case you have millions of record in your DB,
Becuase now the app would be freezing only once at the start of the processing, but if you use DB Query in a loop it would be going to DB seraching among the million record every time so still slower.

Now in such a scenario the best way is to use Stored Procedures
In case you have time you might try out another logic that will (hopefully) be much quicker
Have an SP that takes the Serial Numbers array, searches the datatbase for these Serials and returns those serial numbers that are not found in the DB.
Now our Front end Reads the Serial Sheet creates an array of SerialNo and passes to the SP after the SP returns those SerialNo which are not in the DB it goes to the Excel Moves these serials to the NoMatch page
So this way we avoid even fetching the million record to the DataTable
0
 

Author Comment

by:nyee84
ID: 16858874
existing rows(not duplicate) in NOMATCH worksheet is getting replced if a new no matching row is added to the NOMATCH worksheet?? Why is it so?

0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16858883
That's what is bothering me, the logic says it should not and in fact its not being replaced here at my sample app
Try debugging the App, put a break point and see what is the value of
NewWS.UsedRange.Rows.Count
in the line
NewWS.Rows(NewWS.UsedRange.Rows.Count + 2).EntireRow.Select()  

then check your NoMatch sheet in the Excel, whether Count + 2 points to an empty row or not

0
 

Author Comment

by:nyee84
ID: 16859197
I will check and revert...
0
 

Author Comment

by:nyee84
ID: 16859308
Hi  Arif ,

My  Excel file format as follows

PIDKEY WORKSHEET

1 ACCOUNTNO   SerialNo
2 00000000001   4D98956
3 00000000002   4C95995 ---------------No Matching row


NOMATCH WORKSHEET

1
2
3 00000000004    4F46647 -------------Existing Nomatching row


After i run the program my woksheet values shown below

SERIAL WORKSHEET

1 ACCOUNTNO   SerialNo
2 00000000001   4D98956

NOMATCH WORKSHEET

1
2
3 00000000002   4C95995    ->>>>> Existing no matching row been replaced...  

When i put a break point NewWS.UsedRange.Rows.Count is showing as one and NewWS.UsedRange.Rows.Count + 2
is pointing to the third row.

I dont know what went wrong, anyway my NOMATCJH does nt have header, which is ok, no issue..
But i still dont understand why my exisiting row is being replaced with my new one.

Here is the code which we are working now

 Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        Dim OpenExcel As New OpenFileDialog
        With OpenExcel
            .CheckFileExists = True
            .DefaultExt = "xls"
            .InitialDirectory = "c:\"
            .Multiselect = False
        End With

        Dim dgrResult As DialogResult
        dgrResult = OpenExcel.ShowDialog()
        Dim filename As String
        If dgrResult <> DialogResult.Cancel Then
            filename = OpenExcel.FileName
            Call MoveNoMatch(filename, ProgressBar1)
        End If
        MsgBox("Completed")

    End Sub
    Private Function MoveNoMatch(ByVal FileName As String, ByVal progressbar As ProgressBar) 'Now a function that takes file path for the XL FIle
        Dim pdinc As Integer
        progressbar.Value = 0
        Dim DT As New DataTable
        Dim ConnectionString As SqlConnection = New SqlConnection("Server=xxxxxx;User id=sa;pwd=yyyyy;Database=zzzzz")
        Dim DA As New SqlDataAdapter("Select SERIALNO From pppppp", ConnectionString)
        DA.Fill(DT)   ' Fill the DataTable from SQL so we need not Query the DB again and agian
        Dim XL As New Excel.Application
        Dim CurrWS As Excel.Worksheet
        Dim NewWS As Excel.Worksheet
        XL.Workbooks.Open(FileName)   '<--Changed
        ' CurrWS=XL.ActiveSheet 'Now since the same file is used again and again the Active Sheet might not be the same so I am assuming a Sheet called SERIAL is having the data to match
        CurrWS = XL.Sheets("SERIAL")
        For Each ws As Excel.Worksheet In XL.Worksheets
            If ws.Name = "NOMATCH" Then
                NewWS = ws 'If sheet already there get a reference
                Exit For
            End If
        Next

        If NewWS Is Nothing Then
            NewWS = XL.Worksheets.Add ' Add a new sheet if it did not exist earlier
            NewWS.Name = "NOMATCH"
        End If

        Dim Serial As String

        pdinc = 10 / CurrWS.UsedRange.Rows.Count

        For i As Integer = CurrWS.UsedRange.Rows.Count To 2 Step -1
            Serial = CurrWS.Cells(i, "B").Value
            If DT.Select("SerialNo='" & Serial & "'").Length <= 0 Then
                ProgressBar1.Value = ProgressBar1.Value + pdinc
                CurrWS.Cells(i, "B").EntireRow.Copy()

                'Check if This row is already in the NoMatch Sheet or Not
                If NewWS.Cells.Find(What:=Serial, SearchOrder:=Excel.XlSearchOrder.xlByRows, SearchDirection:=Excel.XlSearchDirection.xlNext) Is Nothing Then

                    NewWS.Activate()  'To avoid the Error you are getting
                    NewWS.Rows(NewWS.UsedRange.Rows.Count + 2).EntireRow.Select()
                    NewWS.Paste()  'Paste it

                End If

                CurrWS.Cells(i, "B").EntireRow.Delete() ' Now delete row in original sheet
            End If
        Next
        XL.ActiveWorkbook.Save()
        XL.ActiveWorkbook.Close()
        XL.Application.Quit()
        XL = Nothing
        progressbar.Value = 100
    End Function

kindly advice



0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16859436
That's right
Why is your NoMatch Sheet like this

NOMATCH WORKSHEET

1
2
3 00000000004    4F46647 -------------Existing Nomatching row

Why do you have 2 Rows at the Top empty ?
I assumed you'd have only One Row empty
So anyway if you are going to have two rows at the Top empty then Change the Code
          NewWS.Rows(NewWS.UsedRange.Rows.Count + 2).EntireRow.Select()
To
          NewWS.Rows(NewWS.UsedRange.Rows.Count + 3).EntireRow.Select()  '<-- + 3 now ie 2 rows would be empty put in the 3rd one

0
 

Author Comment

by:nyee84
ID: 16859460
I think the logic wrong

for ex: I got one no matching row in SERIAL worksheet, once i run the program

NOMATCH will be crated and new row will be in 3rd row.

Next time i add one more nomatch row in SERIAL program will check how many used rows in NOMATCH, will return as i and you will the add 2 more, so it will to 3rd row (no empty row) , but actually 3 rd row in is already occupied, so it will overwrite.

How to resolve this issue..
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16859475
>> how many used rows in NOMATCH, will return as i and you will the add 2 more, so it will to 3rd row

That's right, I did that assuming you will have only one blank row at the top but you have 2 so instead of 2 add 3

NewWS.Rows(NewWS.UsedRange.Rows.Count + 3).EntireRow.Select()
0
 

Author Comment

by:nyee84
ID: 16859603
Some issues here ..

i hv put one no matching key in SERIAL sheet & i run the program

pro created a new worksheet named NEWMATCH, so nothig in NOMATCH worksheet

and in go through the code and i put break point in NewWS.UsedRange.Rows.Count, so surprised showing
as 1   and  NewWS.UsedRange.Rows.Count + 3  is showing 4 eventhough no rows in NOMATCH worksheet.

Pls help????????????
0
 

Author Comment

by:nyee84
ID: 16859624
I dont understand,,    The program is creating a new woksheet called NOMATCH , after that  NewWS.UsedRange.Rows.Count showing as 1
0
 

Author Comment

by:nyee84
ID: 16859670
Any space ,,  What will be root cause???
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16859717
Ok Lets do away with this empty Row thing,
We will programatically Add the Header to the NoMatch page and make sure that there are no Rows empty in between
So that all these issues would not arise at all

Modify this code:

        If NewWS Is Nothing Then
            NewWS = XL.Worksheets.Add ' Add a new sheet if it did not exist earlier
            NewWS.Name = "NOMATCH"
        End If

To

        If NewWS Is Nothing Then
            NewWS = XL.Worksheets.Add ' Add a new sheet if it did not exist earlier
            NewWS.Name = "NOMATCH"
            NewWs.Cells(1,1)="ACCOUNT NO"
            NewWs.Cells(1,2)="SERIAL NO"
        End If

Then Change The Line
       NewWS.Rows(NewWS.UsedRange.Rows.Count + 2).EntireRow.Select()
To
       NewWS.Rows(NewWS.UsedRange.Rows.Count + 1).EntireRow.Select()

0
 

Author Comment

by:nyee84
ID: 16859824
Everything seems to be fine now , just give me some time to double check and will accept your solution asap.

I am really sorry for making this post as a lengthy QN and Ans ....

Once again Thanks for you great help....... especially for your quick reply.....

Creating a storeprocedures i am interested however i dont want to ask any more questions in the same post which may be against the EE rules.. . and also will be troublesome for you also..

If i need to ask you about the SP, do i need to create another post.

Is it possible to delete one solution, once i accept the expert comment..

Thanks.................................................................................................................................................................
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16859886
OK Let me give you a brief of the SP Logic here, you can try at liesure
in case you still have problems you can post a new specific question

Open Excel, and set the CurrWS object to the Sheet
Loop through all the Rows in the Sheet and build a string of Coma separated values of your Serial Numbers
For i=2 to CurrWS.UsedRange.Rows.Count
      SomeVariable &= CurrWS.Cells(i,2) & ", "
Next

Create Command Object
Set the Parameter value to this variable
i.e Pass this Coma separated list of Serial numbers to SP (Check MSDN on how to pass params to SP)

Now the SP breaks the Coma separated values and gets all serial numbers
then write a query so that you get all matched/Unmatched Serial numbers
build a string return all Unmatched Serial Numbers

Now back at the front end , Loop through all these Serial Numbers
serach the Serial Sheet for unmatched Serials
    CurrWS.Cells.Find(......)
Move them to NoMatch page

I understand it will be difficult for you to do all this initially to start with break them into individual tasks, like creating the variable of coma separated values, passing param to SP, searching returned Serials etc.
do them individually in small test programs, that will give you some idea of how to do it

0
 

Author Comment

by:nyee84
ID: 16860107
Ok thanks
0
 

Author Comment

by:nyee84
ID: 16867884
Hi Arif,

Filling the datatable with 1 million records is taking one miniute and my excel worksheet got 20 k(twenty thousand) rows and checking these rows with my datatable taking long time sometimes going to 20 mins alteast, Is there a way to aviod this performace degradation issue.



0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16868528
Hi nyee84
1 Million Record in DB
and 20K Rows in Excel
Now that's bound to take time, You can do a little bit of tuning like creating an index on the database table (I think there would be one already)
After revisiting the code I think there are only slight changes possible for eg instead of "DT.Select" you can create a primary key on SerialNo field and use "DT.Rows.Find" which is slightly faster

Add these lines just after
              Dim DA As New SqlDataAdapter("Select SERIALNO From pppppp", ConnectionString)
              DA.Fill(DT)   ' Fill the DataTable from SQL so we need not Query the DB again and agian
     '---- New Lines ------
              Dim PK() As DataColumn = { DT.Column("SERIALNO") }
              DT.PrimaryKey = PK
      '--------------------------

Now Change the line
       If DT.Select("SerialNo='" & Serial & "'").Length <= 0 Then
To
        If DT.Rows.Find(Serial) Is Nothing Then


This change would slightly improve the prformance, but if you have 20K rows in Excel and for each row you are searching for a Match in a Million record, its bound to take time, Even the Stored Procedure would not improve things a lot because passing 20K Serial Nos to the SP is not a very good idea either.

I think you are already showing a Progress Bar, that's one way to let the User know it is a time consuming effort, in case your progress Bar is not incrementing properly try putting the line "Application.DoEvent" inside the loop anywhere.

Also you can run the whole process on a separate Thread but that's not going to speed up the process but your User can, in the meanwhile do something else, if at all that is required.
0
 

Author Comment

by:nyee84
ID: 16870538
Hi I will try the above mentioned changes.. Are we able to filter the datatabe(I heard about this method from my friend) by any methods? .

Finally, i am putting a progressbar when i start process my excel row comparing with the datable. I would like add some thread or progressbar, while fillling datatable, since it is a not loop i dont know how to add thread or program value. I dont want the user to say my program freeze instead would like to show some thing to user that program is still processing.

Hopefully, By this i am unsubscribing from this thread....

0
 

Author Comment

by:nyee84
ID: 16909036
Hi Arif,

Thanks alot for your great help..............

Keep up the good work
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses
Course of the Month20 days, 3 hours left to enroll

872 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