Solved

fields from Multiple, non-formatted, text files into Access 2002 table

Posted on 2002-05-13
6
174 Views
Last Modified: 2010-05-02
I know i am asking a lot in one question, but i first need to get an idea of the quickest way o get this data into a database, and i only know a little access programing, and even less VB.

Premise:
 To read through multiple files,from single directory. Ther could be up to 1000 files. to search each file for particular data, that is delimeted by specific Html tags
e.g "<title>....</title>". then collect the data from between the delimiters, and place it in fields in the database.
 
If i can get it to work with one field, as in the above example, ill then move on to further fileds. at this stage, page title, and about 4 other fields are all i need, but that will probably change once i can get data from the pages... he he he!


MY Question: ( finally)

Should this be done in VB, or some form of SQL, or what.
if in VB then how to execute this from Acess, and exactly where to put it on a from.table or macro?

if in SQL then HELP!!!!
0
Comment
Question by:dtsupns
6 Comments
 

Accepted Solution

by:
HobbitHouse earned 100 total points
ID: 7005638
I'd say do it in VB --- SQL is really for queries, not for reading files.  Lay out your algorithm something like what I show below and then attack the problem one step at a time.  If you have specific difficulties, ask questions.

in loop1:
     read a file
     in loop2:
          read a line & scan for tag
          if tag then store data
          if EOF then end loop2
     end loop2
     if no more files then end loop1
end loop1

see the "open statement" & the "line input statement" if you're not clear on how to read files in VB
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7005976
I'd agree with doing this from inside of VB, then, once you parse out your text, insert it into Access with a regular insert type of query.  I'd recommend something more like this:

Private Sub ScanForTag(sTag as string)
Dim sMatch as string
Dim Pos1 as long
Dim Pos2 as long
Dim sFile as String
Dim sResult as String
Dim fHandle as long

    sMatch = Dir$("c:\mydocs\*.*", vbNormal)
    Do
       fHandle = FreeFile
       Open "C:\Mydocs\" & sMatch for input as fHandle
       Input #fHandle, sFile
       Close fHandle
       Pos1 = Instr(1,sFile, "<" & sTag & ">", vbTextCompare)
       if Pos1 > 0 then
          Pos2 = Instr(Pos1,sFile, "</" & sTag & ">", vbTextCompare)
       End if
       If Pos1 <> 0 and Pos2 <> 0 then
          Pos1 = Pos1 + Len("<" & sTag & ">")
          sResult = Mid(sFile, Pos1, Pos2 - Pos1)
          InsertToDb(sResult)
       End IF
       sMatch = Dir$()

    Loop While sMatch <> ""

End Sub

Now, I might be one character off, plus or minus on the Mid statement above, so, you'll have to step through it and make any adjustments as necessary.

Also, the first DIR$ statement might miss files that are defined as system or hidden or archived files etc, so, you can add more properties to search for if you need to.  Just make sure not to retrieve any directories as they will cause the file open code to blow up.

Let me know if you need help with the code to update the Access Database.
0
 

Author Comment

by:dtsupns
ID: 7007300
thanks for the info guys.
I should have asked this question in the databses in general section, but didnt realise i was in VB section . never mind.

I like the idea of VB first then importing it to access once its formatted a little better.
But i was hoping to make this process as seemless as possible.  its  only for my private use, but i allready have to run 2 diffrent programs to collect and generate the text files from the HTML pages. so i wouild like to be able to just run access, and press a button to collect the titles, and relevant info i need, then have it put int a table or two. so i can do my work on it.

can i put that code you have shown me, into a n acxcess button to execute when the button is presed? I should be able to?
the files are stright text, so the code you have written is fine, i dont need hidden files or anything.

and yes i might need  your assitance with the access coding aswell, we'll wait and see.
 
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:mdougan
ID: 7007322
You might be able to put the code I have above in an Access Function that exists in a Code Module, then invoke that under a Button Click.  Access uses VBA which in many ways is similar to regular VB, so, it might work.

However, you are under the impression that you will have to have Access open if you do this through VB and that is not the case.  The VB program will take care of opening up Access behind the scenes and inserting the info into the tables.  Then, later, if you want to open the database and look at the data, you may.

Before we'd work up a sample for inserting to the database through VB, can you tell us, given the example tag listed above, what tables/fields you'd like to update (insert to) in your Access database?
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7765321
Hi dtsupns,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Split points between: HobbitHouse and mdougan

dtsupns, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 

Expert Comment

by:SpideyMod
ID: 7838559
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange


mdougan, points for you at:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20487761.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now