Home....
Developer Network >> Visual Basic Programming >> Visual Basic 6.0

[RESOLVED] Loops within Loops within Loops within open File for input! Help!

Hey!

I'm writing a program to simplify my major duty at work. ;) Love it. Anyway... I have the program open a file of about 6000 lines. It's simply a text file containing UPC, Order Num, Batch Num, Desc, Etc. All kinds of information regarding retail products. It reads a single line, then checks if the first 20 characters are present within an array by the use of a FOR Loop(For i = 0 to ubound(lninfo)). Sadly, the array is around 2600 long in dimension. So it takes around 5 seconds or so for that loop to conclude. 5 seconds multiplied by 6000 lines of text is around... too long. quite a few hours. Is there any better way to do this? like load the entire file to memory then have it search through there? Anything would be helpful. :)
[760 byte] By [The1nk] at [2007-11-20 10:01:29]
«« Toolbars in Vista - Dropdown Problem
»» Exception in thread "main"
# 1 Re: [RESOLVED] Loops within Loops within Loops within open File for input! Help!
Post your code.

You might create a string and use InStr(), but it'd depend on your code.
dglienna at 2007-11-9 19:35:50 >
# 2 Re: [RESOLVED] Loops within Loops within Loops within open File for input! Help!
Well, here's the code. lol.


Private Sub mnuImpDatabase_Click()
Cnt = 0
CommonDialog1.DialogTitle = "Select file to Import..."
CommonDialog1.Filter = "Text Documents(*.txt)|*.txt"
CommonDialog1.ShowOpen
If CommonDialog1.FileName <> "" Then
Open CommonDialog1.FileName For Input As #1
Do While Not EOF(1)
DoEvents
Line Input #1, dData
Form2.Caption = "Inputting File, Line #: " & Cnt
LnInfo(Cnt).UPC = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
LnInfo(Cnt).OrderNum = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
LnInfo(Cnt).Descript = Left(dData, 40)
dData = Right(dData, Len(dData) - 40)
LnInfo(Cnt).BatchNum = Left(dData, 12)
dData = Right(dData, Len(dData) - 12)
LnInfo(Cnt).Measure = Left(dData, 12)
dData = Right(dData, Len(dData) - 12)
LnInfo(Cnt).Quant = Left(dData, 8)
dData = Right(dData, Len(dData) - 8)
LnInfo(Cnt).Size = Left(dData, 6)
dData = Right(dData, Len(dData) - 6)
LnInfo(Cnt).ExDescript = Left(dData, 40)
dData = Right(dData, Len(dData) - 40)
LnInfo(Cnt).LabelType = Left(dData, 8)
dData = Right(dData, Len(dData) - 8)
LnInfo(Cnt).PrintAmt = Left(dData, 4)
dData = Right(dData, Len(dData) - 4)
LnInfo(Cnt).LikeCodes = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
dData = Right(dData, Len(dData) - 22)
LnInfo(Cnt).ProdType = Left(dData, 8)
dData = Right(dData, Len(dData) - 8)
LnInfo(Cnt).Price = Left(dData, 9)
dData = Right(dData, Len(dData) - 9)
LnInfo(Cnt).PriceSplit = Left(dData, 3)
dData = Right(dData, Len(dData) - 3)
LnInfo(Cnt).SaleEnds = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
LnInfo(Cnt).SaleBegi = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
LnInfo(Cnt).Department = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
For i = 0 To UBound(LnInfo)
DoEvents
If Cnt <> i Then
If LnInfo(i).UPC = LnInfo(Cnt).UPC Then 'same UPC
ReDim Preserve LnInfo(UBound(LnInfo) - 1)
Cnt = Cnt - 1
Exit For
End If
End If
Next i
ReDim Preserve LnInfo(UBound(LnInfo) + 1)
Cnt = Cnt + 1
Loop
ReDim Preserve LnInfo(UBound(LnInfo) - 1)
Close #1

Open App.Path & "\main.db" For Input As #1
Cnt = 0
Do While Not EOF(1)
Line Input #1, dData2
TruFalse = False
For i = 0 To UBound(LnInfo)
Form2.Caption = "Checking Against UPC #: " & i
DoEvents
dData = dData2
If LnInfo(i).UPC <> Left(dData, 20) Then
Else
TruFalse = True
End If
Next i
If TruFalse = False Then
ReDim Preserve LnInfo(UBound(LnInfo) + 1)
LnInfo(UBound(LnInfo)).UPC = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
LnInfo(UBound(LnInfo)).OrderNum = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
LnInfo(UBound(LnInfo)).Descript = Left(dData, 40)
dData = Right(dData, Len(dData) - 40)
LnInfo(UBound(LnInfo)).BatchNum = Left(dData, 12)
dData = Right(dData, Len(dData) - 12)
LnInfo(UBound(LnInfo)).Measure = Left(dData, 12)
dData = Right(dData, Len(dData) - 12)
LnInfo(UBound(LnInfo)).Quant = Left(dData, 8)
dData = Right(dData, Len(dData) - 8)
LnInfo(UBound(LnInfo)).Size = Left(dData, 6)
dData = Right(dData, Len(dData) - 6)
LnInfo(UBound(LnInfo)).ExDescript = Left(dData, 40)
dData = Right(dData, Len(dData) - 40)
LnInfo(UBound(LnInfo)).LabelType = Left(dData, 8)
dData = Right(dData, Len(dData) - 8)
LnInfo(UBound(LnInfo)).PrintAmt = Left(dData, 4)
dData = Right(dData, Len(dData) - 4)
LnInfo(UBound(LnInfo)).LikeCodes = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
dData = Right(dData, Len(dData) - 22)
LnInfo(UBound(LnInfo)).ProdType = Left(dData, 8)
dData = Right(dData, Len(dData) - 8)
LnInfo(UBound(LnInfo)).Price = Left(dData, 9)
dData = Right(dData, Len(dData) - 9)
LnInfo(UBound(LnInfo)).PriceSplit = Left(dData, 3)
dData = Right(dData, Len(dData) - 3)
LnInfo(UBound(LnInfo)).SaleEnds = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
LnInfo(UBound(LnInfo)).SaleBegi = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
LnInfo(UBound(LnInfo)).Department = Left(dData, 20)
dData = Right(dData, Len(dData) - 20)
End If
Loop

Close #1

dData = ""
For i = 0 To UBound(LnInfo)
Form2.Caption = "Working on UPC #: " & i & "/" & Cnt
DoEvents

If LnInfo(i).UPC = "" Or LnInfo(i).UPC = " " Then
Else
If dData = "" Then
dData = LnInfo(i).UPC & LnInfo(i).OrderNum & LnInfo(i).Descript & LnInfo(i).BatchNum & LnInfo(i).Measure & LnInfo(i).Quant & LnInfo(i).Size & LnInfo(i).ExDescript & LnInfo(i).LabelType & LnInfo(i).PrintAmt & LnInfo(i).LikeCodes & LnInfo(i).UPC & " 1" & LnInfo(i).ProdType & LnInfo(i).Price & LnInfo(i).PriceSplit & LnInfo(i).SaleEnds & LnInfo(i).SaleBegi & LnInfo(i).Department
Else
dData = dData & vbCrLf & LnInfo(i).UPC & LnInfo(i).OrderNum & LnInfo(i).Descript & LnInfo(i).BatchNum & LnInfo(i).Measure & LnInfo(i).Quant & LnInfo(i).Size & LnInfo(i).ExDescript & LnInfo(i).LabelType & LnInfo(i).PrintAmt & LnInfo(i).LikeCodes & LnInfo(i).UPC & " 1" & LnInfo(i).ProdType & LnInfo(i).Price & LnInfo(i).PriceSplit & LnInfo(i).SaleEnds & LnInfo(i).SaleBegi & LnInfo(i).Department
End If
End If
Next i
Form2.Caption = "Writing to Database File..."
Open App.Path & "\main.db" For Output As #1
Print #1, dData
DoEvents
Close #1
Form2.Caption = "Complete."
End If
End Sub

The first chunk of code inputs all of the information from the file to update the database with, while doing so it also makes sure there are no duplicates within itself. So I won't end up having around 60 entries in the array including the same data.

Then it opens the main database for input and brings it in line by line, checking to see if the UPC it just brought in is present within the array. If it is, it skips it. If it is not, then it adds it to the array. A potential problem for this is that since it actually adds it to the array it is searching through, it becomes exponentially longer depending on how many UPCs are being added.

Here is an example of a line from the text file being imported first. The whole file is generated by a program so it follows the same template.

2073511001 00066174TURKEY HILL VANILLA 1FZ 0.00 56.00 6UPBY1 1 ALE 1 1LIQUID 5.00 27/7/07 1ASADV 7

Oops, here's the declarations in the Module
Option Explicit
Type AdItem
ItemCode As Integer
Descript As String * 40
ExDescript As String * 40
UPC As String * 20
Size As String * 6
BatchNum As String * 12
OrderNum As String * 20
Measure As String * 12
Quant As String * 8
LabelType As String * 8
PrintAmt As String * 4
LikeCodes As String * 20
ProdType As String * 8
Seq As String * 2
Scan As String * 10
PriceSplit As String * 3
SaleEnds As String * 20
SaleBegi As String * 20
Department As String * 20
Price As String * 9
End Type
Public LnInfo() As AdItem
Public dData As String
Public ddData As String
Public dddData() As String
Public dData2 As String
Public TruFalse As Boolean
Public Cnt As Long


So anywho, thanks for any input.
The1nk at 2007-11-9 19:36:47 >
# 3 Re: [RESOLVED] Loops within Loops within Loops within open File for input! Help!
Look at this approach. 6K lines should fit with no problem.

Option Explicit

Private Sub Form_Load()
Dim x As Integer, st As String
Dim ff As Integer
Dim strBuff As String
Dim str() As String
ff = FreeFile
Open App.Path & "\to do.txt" For Input As #ff
strBuff = Input(LOF(ff), ff)
Close #ff
' ------ two ways to skin a cat -----
MsgBox "Lines = " & Len(strBuff) - Len(Replace(strBuff, vbCrLf, "x")) + 1
' -----------------
str() = Split(strBuff, vbCrLf)
MsgBox "There are " & UBound(str) + 1 & " lines in the file"
For x = 0 To UBound(str)
st = st & str(x) & vbCrLf & vbCrLf
Next x
MsgBox st
End Sub


Split into lines, then eliminate duplicate spaces in each line, then split each line (on single space). Then everything wll be in an array.
If you loaded them straight into a table, then you could use SQL to do the grunt work.
dglienna at 2007-11-9 19:37:50 >
# 4 Re: [RESOLVED] Loops within Loops within Loops within open File for input! Help!
Hm. Table as in Excel Spreadsheet? Where can I find out how to work with that? And I've never actually even touched SQL. Is it something else I have to pay for, or is it a tool that becomes available with MS VB? I have VB 6.0 Enterprise, if it's of any concern. :P
The1nk at 2007-11-9 19:38:56 >
# 5 Re: [RESOLVED] Loops within Loops within Loops within open File for input! Help!
Should run on your computer. If you have Access you can work with tables without VB, which may be easier. VB can then access the table even if uses don't have Access installed. SQL is built in to the language.

Doesn't do much, but it should run.
dglienna at 2007-11-9 19:39:49 >
# 6 Re: [RESOLVED] Loops within Loops within Loops within open File for input! Help!
lol. I ran your little app, and I get a message box of "CSIN-0001". Which I have no idea what it means. :ehh:

So, this turned into a "Help me speed it up" to a "Help me, I have no idea how to program" thread :blush:

Any links on tutorials for MS Access or SQL usage? Thanks for all your input, by the way. :rolleyes:
The1nk at 2007-11-9 19:41:00 >
# 7 Re: [RESOLVED] Loops within Loops within Loops within open File for input! Help!
seems like you want to merge those text files then save to "main.db" without duplicates in UPC field??

well in that case, just parse the UPC fields then have them save into a collection object for duplicate checking. if a duplicate UPC field found in the collection, don't write the raw text line, otherwise write/save it into a destination file and then append the UPC into the collection. the destination/output file must be created first before you open the input files.

or if you just want to append records into your "main.db" from a text file, you should load the UPCs from "main.db" into the collection object first before reading each line of text from the input file. this is somekind of initialization :D so that your program would know what UPCs are already in the "main.db" for duplicate checking. and then same concept would apply as above.

sample collection code:

dim col as collection

' instantiate the collection
set col = new collection

' dispose the collection
set col = nothing

'
' Return value: TRUE - added to collection; FALSE - failed because same UPC exists in the collection (duplicate)
'
Private Function AddToCollection(ByVal upc As String) As Boolean

On Local Error Resume Next
col.Add upc, upc

AddToCollection = (Err.Number = 0)

End Function
Thread1 at 2007-11-9 19:41:52 >
# 8 Re: [RESOLVED] Loops within Loops within Loops within open File for input! Help!
Well, I've tried a couple of approaches since then. lol. I've actually restarted the entire project, now that I have the design in my head - instead of coding it from the ground up without thinking about it first.

I've decided to go with a MS Access Database, VB6.0's Adodc Control and a MSHFlexGrid to show me the data. I've also decided it'd be best to Trim() the Strings/UPCs, etc, and then later append blank spaces before and after the data to allow it to fit into the output's template.

So, in short, I guess this thread has been resolved? ;P

Thanks for all your help :)
The1nk at 2007-11-9 19:42:54 >
# 9 Re: [RESOLVED] Loops within Loops within Loops within open File for input! Help!
You have to do-it-on-paper, or if you're real good, Do it in your head.
Programming is mostly about designing the best algorithm. Sometimes you have to start over!

Pull down Thread Tools to mark this as Resolved!
dglienna at 2007-11-9 19:44:00 >
# 10 Re: [RESOLVED] Loops within Loops within Loops within open File for input! Help!
glad this has been resolvd already! just emptied my bin though, so that there would be space when i want to throw something :D lol
Thread1 at 2007-11-9 19:44:56 >
Visual Basic Programming
Visual Basic 6.0

Visual Basic Programming

All Classified

Visual Basic Programming New Topic

Visual Basic Programming Hot Topic