[Help] Loading A file Specifically My Program

[jasonmbrown]

I need to load a file very specifically into my program. In a sense that everything needs to be as if the program itself was running previously. As such I am trying to make it load each line into the array where it was saved however I am encountering problems.

The File Looks like this EXACTLY

INSERT INTO Drop_Data (dropperid, itemid, minimum_quantity, maximum_quantity, questid, chance) VALUES 
(1, 1, 1, 1, 1, 1), 
(2, 2, 2, 2, 2, 2), 
(3, 3, 3, 3, 3, 3);

I need the load function to load the numbers into an array positioned by the first number there (eg "(1,1,1,1,1,1)," goes into array(1). "(3,3,3,3,3,3);" goes into array(3). I need it to go into the array without the ; instead it needs to be a comma.

I also need it to read everything in the first like between the "(" and ")" , As well as the "drop_data"

I have no idea how to get it to properly Store the variables. I will post the compiled program below as well as my current code (slightly newer then the program itself which hasnt had the load feature activated since it doesnt work...)

The Area Im having problems with is command1 its about half way down the code (its my load function but its compleatly broken excluding the fact that it loads the file line by line

Dim TextOutPut() As String

'Use 9999999 / 99999.99 < or > TextOutPut(Number) <

Dim FileHandle As FileSystemObject
Dim ActiveMobID As String
Dim splitted As Variant
Dim splittedoutput() As String
Dim tempstring As String


Private Sub Add_Click()

If tstid.Text = "SQL Table ID's" Then tstid.Text = "dropperid, itemid, minimum_quantity, maximum_quantity, questid, chance" 'Change to Standard SteadyMaple SQL
If tstn.Text = "SQL TableName" Then tstn.Text = "Drop_Data"                                                                 'Change to Standard SteadyMaple Sql
TextOutPut(0) = "INSERT INTO " & tstn.Text & " " & "(" & tstid.Text & ") VALUES "                                           'Sets First Line in the output array
If Check1 Then
    If Val(tmid.Text) < 1 Or Val(tiid.Text) < 1 Or Val(timin.Text) < 1 Or Val(timax.Text) < 1 Or Val(tiqid) < 0 Or Val(tichance.Text) < 1 Then
        a = MsgBox("There Appears to be an Error. The values of the Textboxes (Item Drop) must be Greater then 0 Excluding the QuestID", vbCritical, "Error") = vbOK
        Exit Sub
    End If
End If

If Check2 Then
    If Val(tmid.Text) < 1 Or Val(tmmin.Text) < 1 Or Val(tmmax.Text) < 1 Or Val(tmchance.Text) Then
        a = MsgBox("There Appears to be an Error. The values of the Textboxes (Meso Drop) must be Greater then 0", vbCritical, "Error") = vbOK
        Exit Sub
    End If
End If


Text4.Text = ""
'Store MobID's that are currently in use
splitted = Split(ActiveMobID, ",")
i = -1
For x = LBound(splitted) To UBound(splitted)
    splitted(x) = Val(splitted(x))
    If splitted(x) = Val(tmid.Text) Then i = x
Next x
If i = -1 Then ActiveMobID = ActiveMobID & "," & Val(tmid.Text)
splitted = Split(ActiveMobID, ",")




tempstring = Empty
If Check1 Then
itemcode = "(" & tmid.Text & ", " & tiid.Text & ", " & timin.Text & ", " & timax.Text & ", " & tiqid.Text & ", " & tichance.Text & "), "

    splittedoutput = Split(TextOutPut(Val(tmid.Text)), vbCrLf)
    For x = LBound(splittedoutput) To UBound(splittedoutput)
        tempstring = tempstring & splittedoutput(x) & vbCrLf
        If splittedoutput(x) = itemcode Then Exit For
        
        ' TextOutPut(MobID) Contains That Mobs Drop Data, Stored "(mobid, itemid, mindrop, maxdrop, questid, chance), vbcrlf" Next Item "(mobid"..etc
        
        If x = UBound(splittedoutput) Then
            TextOutPut(Val(tmid.Text)) = tempstring & vbCrLf & itemcode
            Exit For
        End If
        
    Next x
    If UBound(splittedoutput) = -1 Then TextOutPut(Val(tmid.Text)) = vbCrLf & tempstring & itemcode
End If


'Meso Drops
tempstring = Empty
If Check2 Then
itemcode = "(" & tmid.Text & ", " & "0" & ", " & tmmin.Text & ", " & tmmax.Text & ", " & "0" & ", " & tmchance.Text & "), "
'ItemCode = Sql Data Import Code




    splittedoutput = Split(TextOutPut(Val(tmid.Text)), vbCrLf)

    For x = LBound(splittedoutput) To UBound(splittedoutput)
        tempstring = tempstring & splittedoutput(x) & vbCrLf
        If splittedoutput(x) = itemcode Then Exit For
        Debug.Print splittedoutput(x)
        ' TextOutPut(MobID) Contains That Mobs Drop Data, Stored "(mobid, itemid, mindrop, maxdrop, questid, chance), " NextLine
        
        If x = UBound(splittedoutput) Then
            TextOutPut(Val(tmid.Text)) = tempstring & itemcode
            Exit For
        End If
        
    Next x
    If UBound(splittedoutput) = -1 Then TextOutPut(Val(tmid.Text)) = vbCrLf & tempstring & itemcode
End If







'remove Multiple vbCrLfs
For x = LBound(splitted) To UBound(splitted)
    TextOutPut(splitted(x)) = Replace(TextOutPut(splitted(x)), vbCrLf & vbCrLf, vbCrLf)
Next x


    'Displays Text In Big TextBox on the left of Form1
For x = LBound(splitted) To UBound(splitted)
    Text4.Text = Text4.Text & TextOutPut(splitted(x))
Next x

' Adds SQL End ";"
    Text4.Text = Mid(Text4.Text, 1, Len(Text4.Text) - 2)
    Text4.Text = Text4.Text & ";"
End Sub

Private Function FirstEmptyString(StringArray, TextString, MobID)
End Function

Private Sub Command1_Click()
'On Error GoTo error:
CommonDialog1.CancelError = False
CommonDialog1.InitDir = App.Path
CommonDialog1.FileName = Text2.Text
CommonDialog1.Filter = "*.sql|*.sql|*.txt|*.txt"
CommonDialog1.DefaultExt = "*.sql"

CommonDialog1.ShowOpen

If FileExists.FileExists(CommonDialog1.FileName) = True Then
Open CommonDialog1.FileName For Input As #1
fline = 1
eline = 1
Do While Not EOF(1)
Line Input #1, ttext
eline = eline + 1
Loop
Close #1
Open CommonDialog1.FileName For Input As #1

Do While Not EOF(1)
    Line Input #1, ttext
    
    If fline = 1 Then
        TextOutPut(0) = ttext
    End If
    If fline > 1 Then
        lsplit = Split(ttext, ",")
        lsplit(0) = Mid(lsplit(0), 2, Len(lsplit(0)))
        ActiveMobID = ActiveMobID & "," & lsplit(0)
        
        'If fline = 2 & fline <> eline - 1 Then TextOutPut(Val(lsplit(0))) = TextOutPut(Val(lsplit(0))) & Mid(ttext, 1, Len(ttext) - 2) & "," & vbCrLf
        
        If (fline > 2) And (fline <> (eline - 1)) Then TextOutPut(Val(lsplit(0))) = TextOutPut(Val(lsplit(0))) & Mid(ttext, 1, Len(ttext) - 2) & "," & vbCrLf
        
        'If fline = eline - 1 Then TextOutPut(Val(lsplit(0))) = TextOutPut(Val(lsplit(0))) & Mid(ttext, 1, Len(ttext) - 2) & ","
        Debug.Print ttext
        Debug.Print TextOutPut(Val(lsplit(0)))
    End If
fline = fline + 1
Loop

splitted = Split(ActiveMobID, ",")
For x = LBound(splitted) To UBound(splitted)
    Text4.Text = Text4.Text & TextOutPut(splitted(x))
Next x

Text4.Text = Mid(Text4.Text, 1, Len(Text4.Text) - 2)
Text4.Text = Text4.Text & ";"

Close #1
End If

error:
If Err.Number = 54 Then Exit Sub
If Err.Number = 32755 Then Exit Sub
If Err.Number <> 0 Then
    a = MsgBox("Encountered Unhandled Error: " & Err.Number, vbOKOnly, "Error: " & Err.Number)
    a = Empty
End If


End Sub

Private Sub Command2_Click()
On Error GoTo error:
CommonDialog1.CancelError = False
CommonDialog1.InitDir = App.Path
CommonDialog1.FileName = Text2.Text
CommonDialog1.Filter = "All Files (*.*)|*.*|*.sql|*.sql"
CommonDialog1.DefaultExt = "sql"

CommonDialog1.ShowSave

If CommonDialog1.FileName <> "" Then
Open CommonDialog1.FileName For Output As #1
Print #1, Text4.Text
Close #1
End If

error:

If Err.Number <> 0 Then
    a = MsgBox("Encountered Unhandled Error: " & Err.Number, vbOKOnly, "Error: " & Err.Number)
    a = Empty
End If
End Sub

Private Sub Form_Load()
ReDim TextOutPut(0 To 9999999)

ActiveMobID = "0"
End Sub



Private Sub Command3_Click()
Dialog.Show
End Sub

[Doogle]

Something like this may help

Option Explicit

Private Function LoadData(strFile As String, strTable As String, strCols As String, strErr As String) As Variant
'
' Reads strFile and isolates various items in the records
' Expected file Format:
' INSERT INTO [tablename] ([column list]) VALUES
' (Numeric Value, Value, Value ,......, Value),
' (Numeric Value, Value, Value,......., Value),
' etc
' (Numeric Value, Value, Value,......., Value);
' [EOF]
' 
' Function sets:    strTable to [tablename]
'                   strCols to [column list]
'                   strErr to any error that may occur
' Function Returns:
'                   Each Values list record as an element in an array
'                   The element number in the array
'                   is defined by the first value in the list of values
'                   the trailing semi-colon on the last record is replaced by a comma
'
Dim intFile As Integer
Dim intPos As Integer
Dim intPos1 As Integer
Dim intI As Integer
Dim intElement As Integer
Dim strData As String
Dim strRecords() As String
Dim strTemp() As String
Dim strElement As String
ReDim strTemp(2)
strErr = vbNullString
intFile = FreeFile
'
' Read the entire file and split into records
'
Open strFile For Input As intFile
strData = Input(LOF(intFile), intFile)
Close intFile
strRecords = Split(strData, vbNewLine)
'
' Find and store the Table Name in the first record
'
intPos = InStr(strRecords(0), "INTO ")
If intPos > 0 Then
    intPos1 = InStr(intPos + 5, strRecords(0), " ")
    If intPos1 > 0 Then
        strTable = Mid$(strRecords(0), intPos + 5, intPos1 - (intPos + 5))
    Else
        strErr = "Invalid Table Name"
    End If
Else
    strErr = "Table Name Not Found"
End If
'
' Find and store the list of columns in tthe first record
'
intPos = InStr(strRecords(0), "(")
If intPos > 0 Then
    intPos1 = InStr(intPos + 1, strRecords(0), ")")
    If intPos1 > 0 Then
        strCols = Mid$(strRecords(0), intPos + 1, intPos1 - (intPos + 1))
    Else
        strErr = strErr & " Closing Bracket not found in Record 1"
    End If
Else
    strErr = strErr & " Opening Bracket not found in Record 1"
End If
'
' For subsequent records:
' locate the opening bracket
' find the first item between the bracket and first comma
' this will be used as the element number into which
' the record will be stored
'
For intI = 1 To UBound(strRecords)
    intPos = InStr(strRecords(intI), "(")
    If intPos > 0 Then
        intPos1 = InStr(intPos + 1, strRecords(intI), ",")
        If intPos1 > 0 Then
            strElement = Mid$(strRecords(intI), intPos + 1, intPos1 - (intPos + 1))
            If IsNumeric(strElement) Then
                intElement = CInt(strElement)
                If intElement > UBound(strTemp) Then ReDim Preserve strTemp(intElement)
                strTemp(intElement) = Replace(strRecords(intI), ";", ",")
            Else
                strErr = strErr & " Non Numeric first Value in Record " & CStr(intI + 1)
            End If
        Else
            strErr = strErr & " No Comma found in list in Record " & CStr(intI + 1)
        End If
    Else
        strErr = strErr & " Opening Bracket not found in Record " & CStr(intI + 1)
    End If
Next intI
LoadData = strTemp
End Function

Private Sub Command1_Click()
Dim intI As Integer
Dim strTest() As String
Dim strErr As String
Dim strTab As String
Dim strCols As String
strTest = LoadData("C:\Test.txt", strTab, strCols, strErr)
If strErr = vbNullString Then
    Debug.Print "Table: "; strTab
    Debug.Print "Columns: "; strCols
    For intI = 0 To UBound(strTest)
        Debug.Print "Element " & CStr(intI) & ": "; strTest(intI)
    Next intI
Else
    Debug.Print "Errors "; strErr
End If
End Sub

Using your data in post #1 the output should look like this

Table: Drop_Data
Columns: dropperid, itemid, minimum_quantity, maximum_quantity, questid, chance
Element 0: 
Element 1: (1, 1, 1, 1, 1, 1), 
Element 2: (2, 2, 2, 2, 2, 2), 
Element 3: (3, 3, 3, 3, 3, 3),

[jasonmbrown]

Hmm Thank you alot. Looks like you put alot of work into the response. I hope I can figure it out easily enough.