Need alternative to 6 nested for loops

[ElPresidente]

Now that I have your attention haha...

I have this weekly file that contains several hundred invoices that
need to be given batch letters in according to the following
hierarchy.

Employee -> Division -> Customer -> Product Type -> Expense Code

I had previously programmed a working version in Python that
successfully did this by putting those 5 categories into 5 nested for
loops with a 6th for loop to iterate through all the Excel rows. If
the row didn't fit the criteria, it was skipped over. When there were
no rows left, it would cycle the parent for loop, increment a batch
letter and try again.

While this executed fast in Python, in VB.Net it's bogging down
heavily. There has to be a better way for me to write this code. Any
suggestions?

 

[sstory]

Hola ElPresidente,

Well, I think you will need to supply more clear details than what you have
given.

Where does the data come from? Employee Division, etc comes from that
single file? If so what are you putting a batch number on?

 

[Uli Netzer]

Hi,

the first thing that comes into my mind is to create a datasource to the
Excel file and then set it as the datasource of a datagridview and use the
"select" method of the datagridview to pull out the information you need.

Use Excel as DataSource:
http://www.codeproject.com/KB/vb/Excel_Connectivity.aspx

Uli

 

[Uli Netzer]

Hi again,

just as I sent the answer off I had another idea. If you dont need the
visual part of the datagridview you also could use a datatable object which
creates a "virtual database" in your memory.

Uli

 

[Brian Gideon]

Now that I have your attention haha...

I have this weekly file that contains several hundred invoices that
need to be given batch letters in according to the following
hierarchy.

Employee -> Division -> Customer -> Product Type -> Expense Code

I had previously programmed a working version in Python that
successfully did this by putting those 5 categories into 5 nested for
loops with a 6th for loop to iterate through all the Excel rows. If
the row didn't fit the criteria, it was skipped over. When there were
no rows left, it would cycle the parent for loop, increment a batch
letter and try again.

While this executed fast in Python, in VB.Net it's bogging down
heavily. There has to be a better way for me to write this code. Any
suggestions?

We're going to need more information. Can you post some code?

 

[Cor Ligthert[MVP]]

Hi,

I tested this today, for about 1000000 rows 100 times in a loop there was
needed 5 seconds, therefore what do you mean with slow, because some people
call this slow. Of course was this not done in Excel, but you can set put
the rows first in a OleDB DataSet table. (Like already mentioned in this
message thread)

I don't know of course how fast your python solution was?

Cor