Solved

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

Posted on 2002-05-13
6
173 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 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

12 Experts available now in Live!

Get 1:1 Help Now