Solved

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

Posted on 2002-05-13
6
176 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
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…

808 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