Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 6th, 2007, 01:34 PM
Authorized User
 
Join Date: Aug 2006
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default Converting Flat File to Access

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.
Code:
<security> <topic keywords>~
<blank field 1>~
<blank field 1>~
Topic <Topic>~
Desc <Desc>~
End
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

 
Old July 6th, 2007, 01:38 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old July 6th, 2007, 01:43 PM
Authorized User
 
Join Date: Aug 2006
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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:
Code:
Desc The quick brown fox jumps over the lazy dog.  The quick brown fox jumps
over the lazy dog.  The quick brown fox jumps over the lazy dog.~
Other than that, you are exactly right.

 
Old July 6th, 2007, 02:03 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old July 6th, 2007, 02:04 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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


mmcdonal
 
Old July 6th, 2007, 02:06 PM
Authorized User
 
Join Date: Aug 2006
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, the Desc could wrap a couple of lines.

 
Old July 6th, 2007, 02:20 PM
Authorized User
 
Join Date: Aug 2006
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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:
Code:
bEnd = False
Invalid outside procedure.

 
Old July 6th, 2007, 02:22 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old July 6th, 2007, 02:24 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal
 
Old July 6th, 2007, 02:27 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

Dim bEnd As Boolean

bEnd = False

MsgBox bEnd


mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
importing flat file into Access 2007 olgolg VBScript 0 March 29th, 2008 07:53 AM
Masking flat file longs Linux 0 August 2nd, 2007 04:50 PM
Need help with my Flat File Schema cmiller66 Biztalk 0 August 21st, 2006 10:48 PM
flat file login new2php PHP How-To 0 February 7th, 2006 07:02 AM
Converting Flat File Database to Relational DB rj_655@hotmail.com Access 5 June 12th, 2004 09:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.