Read the file's text into a string. Use Split to separate the file's lines.

Jumat, 14 September 2012

Read the file's text into a string. Use Split to separate the file's lines.

Tweetear

Read the file's text into a string. Use Split to separate the file's lines. For each line, use Split to separate the fields using a specified delimiter. Loop through the fields to compose a SQL INSERT statement and execute the statement.

Private Sub cmdImport_Click()
Dim delimiter As String
Dim contents As String
Dim lines() As String
Dim fields() As String
Dim wks As Workspace
Dim db As Database
Dim fnum As Integer
Dim line_num As Integer
Dim field_num As Integer
Dim sql_statement As String
Dim num_records As Long

    delimiter = cboDelimiter.Text
    If delimiter = "<space>" Then delimiter = " "
    If delimiter = "<tab>" Then delimiter = vbTab

    ' Grab the file's contents.
    fnum = FreeFile
    On Error GoTo NoTextFile
    Open txtTextFile.Text For Input As fnum
    contents = Input$(LOF(fnum), #fnum)
    Close #fnum

    ' Split the contents into lines.
    lines = Split(contents, vbCrLf)

    ' Open the database.
    On Error GoTo NoDatabase
    Set wks = DBEngine.Workspaces(0)
    Set db = wks.OpenDatabase(txtDatabaseFile.Text)
    On Error GoTo 0

    ' Process the lines and create records.
    For line_num = LBound(lines) To UBound(lines)
        ' Read a text line.
        If Len(lines(line_num)) > 0 Then
            ' Build an INSERT statement.
            sql_statement = "INSERT INTO " & _
                txtTable.Text & " VALUES ("

            fields = Split(lines(line_num), delimiter)
            For field_num = LBound(fields) To UBound(fields)
                ' Add the field to the statement.
                sql_statement = sql_statement & _
                    "'" & fields(field_num) & "', "
            Next field_num

            ' Remove the last comma.
            sql_statement = Left$(sql_statement, _
                Len(sql_statement) - 2) & ")"

            ' Insert the record.
            On Error GoTo SQLError
            db.Execute sql_statement
            On Error GoTo 0
            num_records = num_records + 1
        End If
    Next line_num

    ' Close the database.
    db.Close
    wks.Close
    MsgBox "Inserted " & Format$(num_records) & " records"
    Exit Sub

NoTextFile:
    MsgBox "Error opening text file."
    Exit Sub

NoDatabase:
    MsgBox "Error opening database."
    Close fnum
    Exit Sub

SQLError:
    MsgBox "Error executing SQL statement '" & _
        sql_statement & "'"
    Close fnum
    db.Close
    wks.Close
    Exit Sub
End Sub