Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2004
    Posts
    64

    Unanswered: How can I read a text file into a Access Database

    This is what I would like to achieve
    Each time a person comes into our building, they use a electronic key. The information is recorded into a text file. I would like to extract information as described next

    The date from the line marked D. Date is marked in bold
    The time from the line marked T. Time is marked in bold
    The key number and Location from the line marked M. Key Number & Location is Marked in Bold

    Sample of the text file:

    D[ Fri 30/04/04 ]
    T[ 08:19 ]
    M[ Valid key:130 Ing group, tenancyUNCITY, door:Lower Roller Dr, panelURF CITY. ]
    N[ 0 2 0 0 5 23 1 0 0 0 130 2 ]

    D[ Fri 30/04/04 ]
    T[ 08:22 ]
    M[ Valid key:20 LEAD NGUYANST, tenancyUNCITY, shaft:0, panelURF CITY. ]
    N[ 0 1 0 0 5 16 1 0 0 0 20 2 ]

    D[ Fri 30/04/04 ]
    T[ 08:23 ]
    M[ Valid key:556 Ing Group, tenancyUNCITY, door:Lower Roller Dr, panelURF CITY. ]
    N[ 0 2 0 0 5 23 1 0 0 0 556 2 ]

    …..and so on the text goes

    I am reasonably familiar with access but I have not had any experience with Text Files . Could someone post a sample db that would give me a starting point. any information would be appreciated.



    Peter
    Last edited by Peter.t; 01-04-05 at 06:29.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You can do this thru VBA, like this:

    ...
    Open "c:\textfile" For Input As #1
    Do Until EOF(1)
    'read 4 lines from the file
    Input #1, varDate, varTime, varKeyLoc, varData
    ...
    clean up the variant data, stripping out the pieces you need
    and put it in new variables
    ...
    'append the data to your table
    strSql = "Insert into YourTable (Date, Time, Key, Location) Values (#" & dtmDate & "#, '" & strTime & "', '" & strKey & "', '" & strLoc & "')"
    Docmd.RunSql strSql
    Loop
    Close #1
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2004
    Posts
    64
    Thanks Redneckgeek for your reply. I have it working, but I can see it is going to be a challange

    Thanks for your help

    Peter

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Instead of using the Input statement, I prefer the

    Line Input #<FileNr>, <String Variable>

    which reads the text file line by line. So, you have more control in case your input isn't always structured the ame way.

    Hope this helps.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Nov 2004
    Posts
    64
    DoktorBlue & RedNeckGeek thanks for your suggestions, I have built atleast in part a DB using your code, I have posted the database so you can see where i have got to. I have run out of ideas and am hoping I can get some more help.

    cmdButton 1:Using Line input I get a good copy of the text file, but I need to get each set of four lines into 1 record having 4 fields, and extract the information as in the sample below.

    cmdButton 2 :Using Input only, I get the record of 4 lines from the text file being accepted into a mixture of mixed information in 4 fields

    This sample information is taken from the first record in the text file “Security.txt”. The DTMM is the line the information is in. The line starting with N is not required.
    D.....................T.........................M. ............................M
    fieldDate.........fieldTime..............fieldKey. ...............fieldLocation
    30/04/04..........08:19 .................130....................Ingles group


    Hoping you can help

    Peter
    Attached Files Attached Files
    Last edited by Peter.t; 01-07-05 at 07:51.

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I did not dig into your code, but you will have to write a nested loop:

    .....
    WHILE NOT eof(1)
    ' gather your 4 fields
    WHILE NOT eof(1) and not <4th field got>
    LINE INPUT #1, <text Line>
    WEND
    ' process your 4 fields
    WEND
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    It looks like you have one empty field at the beginning of your file, so you need to account for that.

    Using Line Input, your code should look like:

    ...
    Line Input #1, vardate 'get rid of the first blank field
    Do Until EOF(1)
    '***Line Input***
    Line Input #1, vardate
    Line Input #1, vartime
    Line Input #1, varKey
    Line Input #1, VarLocation
    ....


    Or using regular INPUT:

    ...
    Input #1, vardate
    Do Until EOF(1)
    '***Input only***
    Input #1, vardate, vartime, varKey, VarLocation
    ...

    You'll need to use the various string functions (MID, LEN, INSTR) to extract the data you need.
    For example, to get the date (as a string value) out of this string:
    "D[ FRI 30/04/04]"

    strDate = Mid(vardate, 4, Len(vardate) - 3)

    That would return "Fri 30/04/04". That assumes the field always starts with
    D[(space) and always ends with ].
    Inspiration Through Fermentation

  8. #8
    Join Date
    Nov 2004
    Posts
    64
    Thanks guys you have been a great help to me I really appreciate what you have done, the database is looking good
    I have played with the different codes and this is the one that seems to suit the best

    Line Input #1, vardate 'get rid of the first blank field
    Do Until EOF(1)
    '***Line Input***
    Line Input #1, vardate
    Line Input #1, vartime
    Line Input #1, varKey
    Line Input #1, VarLocation

    My problem now is to extract or strip away unwanted data. I have been trying various ways but cannot get it quite right.
    strDate = vardate ‘inserts into the field, the whole line for the date i.e D[ Fri 30/04/04 ]
    strDate = Mid(vardate, 4, Len(vardate)) inserts into the field Fri 30/04/04 ]
    strDate = Mid(vardate, 4, Len(vardate) - 4) inserts into the field Fri 30/04/04 for 1 record then has a error “invalid procedure call or argument” .

    Your help will certainly be appreciated . I have posted the latest db sample


    Peter
    Attached Files Attached Files

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    couple of general things:

    1. try a compact & repair from time to time. your db as posted was 1304kB before and 148kB after compact

    2. put all your declarations (Dim...) at the top of your routines: access is happier that way


    ...but back to your db: you forgot to handle the blank line between the records.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Nov 2004
    Posts
    64
    Just brilliant izyrider. I take your points about programming, thanks for the tuition (if im never told, I’ll never no)
    I separated the day and date now this database is pretty much finished except for the reports etc, thanks to all that have helped. There is just one final thing. To make the date sorting more efficient it should be in date format (strdate)?
    How would that be programmed in to the code. Below is the code, attached is the up to date database



    Do Until EOF(1)
    Line Input #1, vardate 'get rid of the blank line
    Line Input #1, vardate
    Line Input #1, vartime
    Line Input #1, varkey
    Line Input #1, varLocation
    strDay = Mid(vardate, 4, Len(vardate) - 14)
    strDate = Mid(vardate, 8, Len(vardate) - 9)
    strtime = Mid(vartime, 4, Len(vartime) - 5)
    strkey = Mid(varkey, 4, Len(varkey) - 4)
    strlocation = varLocation

    SQL = "Insert into Table1 (FieldDay, FieldDate, FieldTime, FieldKey, FieldLocation) Values ('" & strDay & "','" & strDate & "', '" & strtime & "', '" & strkey & "', '" & strlocation & "')"
    db.Execute (SQL)

    Loop

    Thanks

    Peter
    Attached Files Attached Files
    Last edited by Peter.t; 01-08-05 at 08:02.

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    use
    dim myDate as date
    myDate = DateSerial(year, month, day)

    to make an access date-type.
    year, month, and date should be integers. so for your strings try:

    DateSerial(cint(strYear), cint(strMonth), cint(strDay))


    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Nov 2004
    Posts
    64
    izyrider I can not see how i should put this into my code ?

    dim myDate as date
    myDate = DateSerial(year, month, day)

    to make an access date-type.
    year, month, and date should be integers. so for your strings try:

    DateSerial(cint(strYear), cint(strMonth), cint(strDay))

    help !

    Peter

  13. #13
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    You should take a look at constant values in Access. I didn't test it, but I guess you even don't need to convert anything, since your text file already contains suitable values.

    Also, you should consider to merge date and time into 1 datetime field.

    Then, your SQL statement should look like

    SQL = "Insert into Table1 (FieldDay, FieldDateTime, FieldKey, FieldLocation) Values ('" & strDay & "',#" & strDate & " " & strtime & "#, '" & strkey & "', '" & strlocation & "')"
    If you keep having two separate fields for date and time, your code should be like

    SQL = "Insert into Table1 (FieldDay, FieldDateTime, FieldKey, FieldLocation) Values ('" & strDay & "',#" & strDate & "#, #" & strtime & "#, '" & strkey & "', '" & strlocation & "')"
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  14. #14
    Join Date
    Nov 2004
    Posts
    64
    Just like to say a big thank you, RedNeckGeek, izyrider and Doktorblue for your help. You are right Doktorblue when you said "you don't need to convert anything, since your text file already contains suitable values." and that is true

    Thank you all

    I am now intrigued as to how to change date formats i.e. date serial etc and formatting a date in a sql statement. so in a few days I will start a new thread to work it out

    Thanks again the database will be a tremendous help

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •