Hi everyone:
  I have a flat text file I need to convert to a database and was hoping I could get some help. Basically, here is the flat file format to start.
Everything in <> is the data entry. Otherwise, it's all hard coded. End signifies the end of that entry. I need to take this flat file and convert it to a database table.

Anyone got any ideas or thoughts on helping me? I would appreciate any help I can get.

Best Regards,
Arholly

Is the file in this format?

<security> <topic keywords>~
<blank field 1>~
<blank field 1>~
Topic <Topic>~
Desc <Desc>~
End
<security> <topic keywords>~
<blank field 1>~
<blank field 1>~
Topic <Topic>~
Desc <Desc>~
End
<security> <topic keywords>~
<blank field 1>~
<blank field 1>~
Topic <Topic>~
Desc <Desc>~
End

etc?

If so, you want to use a Scripting.FileSystemObject, and do a ReadLine. For each line, Check InStr() for the words that will indicate which field the text goes into.

Do you need help with this?

mmcdonal

Yes, that is the file format with one catch. The <Desc> could be multiple lines, but it still is not broke by the tilde. So, it might look like this:
Other than that, you are exactly right.

Dim objFSO As Variant
Dim objFile As Variant
Dim sFileName As String
Dim sContents As String
Dim sTopic As String
Dim sDesc As String
Dim sColName As String
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim bEnd As Boolean

bEnd = False

sFileName = "C:\PathToFile\FileName.txt"
sSQL = "SELECT * FROM tblLocalTable"

Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(sFileName) Then
   Set rs = new ADODB.Recordset
   rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

   Set objFile = objFSO.OpenTextFile(sFileName)
       Do Until objFile.AtEndOfStream
          sContents = objFile.ReadLine
          sColName = Left(sContents, 4)
          Select Case sColName
            Case "Topi"
                sTopic = Right(sContents, (Len(sContents) - 6))
            Case "Desc"
                sDesc = Right(sContents, (Len(sContents) - 5))
            Case "End"
                bEnd = True
'Other cases?
          End Select

    If bEnd = True Then
                 rs.AddNew
                 rs("Topic") = sTopic
                 rs("Desc") = sDesc
                 rs.UpDate
                 bEnd = False
              End If
     Loop

rs.Close


Did that help?


mmcdonal

I am not sure if I accounted for word wrap. Did you say that a DESC might wrap a couple of lines?


mmcdonal

Yes, the Desc could wrap a couple of lines.

Okay, I tried doing a test. I setup a simple form with a button and the code attached to a button in order to run it.

This code was throwing an error:
Invalid outside procedure.

That is a little problematic then.

You will want to check for the word DESC in one line, and then in the next line, check to see if it equals something other than DESC. But only in the next line. If it doesn't, then do sDESC = sDESC & Right(sContents, (Len(sContents) - 5))

This might require another boolean.

Brute force coding. Gotta love it.

Like:

If bDesc = True And sColName <> "End" Then
   sDesc = sDesc & Right(sContents, (Len(sContents) - 5))
End If

Select Case
   Case "DESC"
        bDesc = True
        sDesc = Right(sContents, (Len(sContents) - 5))
   Case "END "
        bEnd = True
        bDesc = False
End Select


Does that work? Note the extra space in the case for scolname when checking for "END " since you are checking for 4 characters.


mmcdonal

Did adding a space to the string "End " work?

mmcdonal

I ran this and it worked. Did we spell Boolean correctly?

Dim bEnd As Boolean

bEnd = False

MsgBox bEnd


mmcdonal