Better Way To Import Specific Excel Data? 5

[SgtJarrow]

Here's the scenario...I work for a distribution company and we provide space planning for our customers (store front and shelf layout) if they desire it. One of our customers does not have their head on too straight, but unfortunately we cannot get them to change their ways nor provide us with good data. So I am having to create a conversion program that will take their data and transmute it into something our space planner can use.

The trouble is the time it takes to import their data...I am hoping someone can give me a tip or two as to a better method. I don't need full code or anything, just some help finding a better solution.

They send us an Excel file, usually about 62K lines long.
[tt]

STORE	PCODE	DESC	SIZE	QTY SOLD IN BOTTLES 	UPC #1	UPC #2	UPC #3
1	121546	AMSTEL LIGHT 2/12/12 CAN	   EA	240	7289010017	0	0
1	121547	AMSTEL LIGHT 2/12/12 NR	   EA	816	7289010016	0	0
1	121531	AMSTEL LIGHT 4/6/12 NR	   EA	249	7289010011	7289010001	7289000008
1	120831	ANCHOR LIBERTY 4/6     AR	   EA	113	7278300300	7278300007	0
1	120931	ANCHOR PORTER 4/6      AR	   EA	72	7278300006	7278300200	0
1	121031	ANCHOR STEAM 4/6/12    AR	   EA	397	7278300100	7278300005	0
1	123245	ANHEUSER WORLD 4/6/12 LN	 EACH	48	1820000368	1820006989	0

[/tt]

What I need to be able to do for this step is get a UNIQUE listing of PCode and UPC. But I have to check UPC#1, UPC#2 and UPC#3 for each line. I don't need to include any UPC#x where the value is 0.

So I need output such as the following into a table:

PCode        UPC
121546       7289010017
121547       7289010016
121531       7289010011
121531       7289010001
121531       7289000008
120831       7278300300
120831       7278300007
'etc

The code below does this...but for the 62K lines in the Excel sheet it is taking about 30 minutes to process.

        If Me.txtImportFile.Text = String.Empty Then
            MsgBox("You have not chosen a file to import.  Please select a file and try again.", MsgBoxStyle.Information, _
                "Missing File")
            Exit Sub
        End If

        Me.pgbSpacePlanner.Visible = True
        Dim dtStart As Date = DateTime.Now

        ' Clear the link table
        For i As Int32 = 0 To Me.dgvPCodeUPCMatch.Rows.Count - 2
            Me.dgvPCodeUPCMatch.Rows.RemoveAt(0)
        Next

        Me.Validate()
        Me.PCodeUPCMatchBindingSource.EndEdit()
        Me.PCodeUPCMatchTableAdapter.Update(Me.SpacePlannerDataSet.PCodeUPCMatch)

        ' Fill the link table
        Dim exApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
        Dim wb As Microsoft.Office.Interop.Excel.Workbook = exApp.Workbooks.Open(Me.txtImportFile.Text)
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = wb.ActiveSheet

        Try
            Dim j As Int32 = 2
            Dim rng As Microsoft.Office.Interop.Excel.Range = ws.Range("B" & j.ToString)
            Dim UPC As String = String.Empty
            Do Until rng.Cells.Value.ToString = String.Empty
                Me.pgbSpacePlanner.Value = j
                rng = ws.Range("B" & j.ToString)
                Dim PCode As String = rng.Cells.Value.ToString
                Dim aCells() As String = {"F", "G", "H"}
                For k As Int32 = 0 To aCells.GetUpperBound(0) - 1
                    rng = ws.Range(aCells(k).ToString & j.ToString)
                    UPC = rng.Cells.Value.ToString
                    If UPC <> "0" Then
                        Try
                            Me.PCodeUPCMatchTableAdapter.Insert(PCode, UPC)
                        Catch ex2 As Exception
                        End Try
                    End If
                Next
                j += 1
                If j Mod 50 = 0 Then Application.DoEvents()
            Loop
        Catch ex As Exception
        End Try

        Dim dtEnd As Date = DateTime.Now
        Me.stlImportTime.Text = DateDiff(DateInterval.Second, dtStart, dtEnd).ToString & " seconds"
        Me.stlImportTime.Visible = True
        Me.pgbSpacePlanner.Visible = False

        exApp.Quit()

        Me.Validate()
        Me.PCodeUPCMatchBindingSource.EndEdit()
        Me.PCodeUPCMatchTableAdapter.Update(Me.SpacePlannerDataSet.PCodeUPCMatch)

Can anyone out there give me a better solution to this problem??? Again, I just need to know where to look for a better answer, but if you have some sample code from anything you may have tried that is similar I wouldn't mind seeing it.

Also, if it matters, I will be using the qty field as well later in the process...so maybe there is a good way to just bring the whole Excel sheet in??? If so, can someone point me in that direction as well?

Thanks

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[RonRepp]

Use the ADO connection string and bring it in just like SQL server or Accesss. This way you can specify your criterion in a normal SQL environment.

For full connection string properties, see

http://www.connectionstrings.com

I hope this helps.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[jebenson]

Try this:

Dim dsAllCodes As DataSet
Dim dsCurrentCodes As DataSet
Dim daCodes As OleDbDataAdapter
Dim Conn As OleDbConnection
Dim ConnStr As String
Dim SQLStr As String

ConnStr = "provider=Microsoft.Jet.OLEDB.4.0; data source=C:\Temp\Test.xls;Extended Properties=Excel 8.0;"

Conn = New OleDbConnection(ConnStr)

Try
dsAllCodes = New DataSet

dsCurrentCodes = New DataSet

SQLStr = "Select [PCODE], [UPC #1] as UPC from [Sheet 1$] where [UPC #1]>0"

daCodes = New OleDbDataAdapter(SQLStr, Conn)
daCodes.Fill(dsCurrentCodes)

dsAllCodes.Merge(dsCurrentCodes)

dsCurrentCodes.Dispose()
dsCurrentCodes = Nothing

dsCurrentCodes = New DataSet

SQLStr = "Select [PCODE], [UPC #2] as UPC from [Sheet 1$] where [UPC #2]>0"

daCodes = New OleDbDataAdapter(SQLStr, Conn)
daCodes.Fill(dsCurrentCodes)

dsAllCodes.Merge(dsCurrentCodes)

dsCurrentCodes.Dispose()
dsCurrentCodes = Nothing

dsCurrentCodes = New DataSet

SQLStr = "Select [PCODE], [UPC #3] as UPC from [Sheet 1$] where [UPC #3]>0"

daCodes = New OleDbDataAdapter(SQLStr, Conn)
daCodes.Fill(dsCurrentCodes)

dsAllCodes.Merge(dsCurrentCodes)

dsCurrentCodes.Dispose()
dsCurrentCodes = Nothing

DataGrid1.DataSource = dsAllCodes.Tables(0)
Catch ex As Exception
MsgBox(ex.message)
End Try

Note that the data are not sorted - that is, the records for each PCODE are not grouped together. I will leave that up to you.

Post again if you need any further assistance with this.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[RonRepp]

jebenson:

I gave you a star for a great example on how to pull data from Excel.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[SgtJarrow]

If only I could give you more than 1 Star!!!!!

Have ****************************************

My import time went from approx 28 minutes to under 1 (about 12 seconds)!!!!

Thanks so much!

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[earthandfire]

Not tested, but just a thought - would it not be quicker (and possibly easier) to use a union query. That way you would only need to make the call once instead of three times. Something like:

[tt]
strSQL =
"SELECT [PCODE], [UPC #1] AS UPC FROM [Sheet 1$] WHERE [UPC #1]>0
UNION ALL
SELECT [PCODE], [UPC #2] FROM [Sheet 1$] WHERE [UPC #2]>0
UNION ALL
SELECT [PCODE], [UPC #3] FROM [Sheet 1$] WHERE [UPC #3]>0"
[/tt]

As I said, I haven't tested this.


Hope this helps.

 

[SgtJarrow]

earthandfire,

I tried your suggestion and it is about 2 seconds faster. Not a huge difference from jebenson's but enough to warrant a star, as it is the concept improvement that I may use more and more later anyway. Thanks.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer