Open and extract info from file with .asc extension

Hi Neo78,

Between those info, add a special character to seperate them, example the ","

ProdEntr,0301,1929,009917,Abd Khalid,00013,QA,Senior E,0

Fistly Open the file, then Read the Lines, use Split function in VB and put into an Array, from there you should able to get the particular info (Assume you know how to open a file).

Cheers,
ryancys

Hey ryancys

The problem is I can't be adding commas as a delimiter to each line cos there will be thousands only for one day. What happens if I need to check at the end of every month??
Know what I mean?

Regards.

Summary:

1. Open file.
2. Extract info and split them into recognisable text or
   sentence. Refer to the above.
3. We have two groups of staff, office and production. For
   office staff, check only for the time-in and time-out
   at two doors, Entrance2 and Reception. For production
   staff, check only for the time-in and time-out at two
   other doors, canteen and Entrance2.
4. Even for two doors for each group, the program must be
   able to identify the earliest entry or latest exit
   between the two.

Regards.

Then you should develop a small database (maybe M$ Access) to handle this as i can foresee thousand of rows (records) will be generated within a month.

Hmm, any other suggestions besides that, ryancys? I don't have MS Access and not familiar with that (laugh at myself).
Even if there r thousands of records, I still have to use this method as I don't think I have any choice. :((

The system which keeps track of all the time-in and out is a third-party system bought from another company. That system produces the reports in .asc/text file and another in .qrp(quick report). So I can only do with the .asc or text file. The transactions are not stored as a database...

If you still need to use File, this may not efficient if compare to database. The M$ Access database is just use for data storage, you can simply create a table only to handle your situation. Nothing difficult to do it :)

So, if you insist to use file to store info, > do you start your coding already? Maybe i can help you through your current coding :)

and you may need to add a tag > to save whether this staff is from office or production if the (dept) is not represent the group of staff

cheers

I'll try and get MS Access on my pc here if possible. I don't want to use the file to store info but the system saves the whole thing in a file and I can't change that. So how am I gonna use Access to open that file and store it there?

OOPS!  The line:

sStr = "Door: " & .Card & vbCrLf

Should read:

sStr = "Door: " & .Door & vbCrLf

> I don't want to use the file to store info but
the system saves the whole thing in a file and I can't change that.

So, you mean you cannot change the Format of the saving file?

Why don't you just send me a copy of .asc file to me, ryancys78@yahoo.com

regards

i think u r trying to do a program that extract data from a security system that record the entries for staff, then the program use the data to generate some info, may b some kind of reports, or to keep into database (eg Ms Access).

by doing this u should download the data from the system. n teh format is as u mentioned above.normally if it is not separated into delimeter it should be in a fixed format, for eg first 10 chars is for door, next 4 chars for date, next 4 chars for time n so on.

so ur vb program should b able to capture the position in each line of data, n read it or copy it into a database.u can use mid function to do it.for example:

         .
         .
         .
'After u download the data as a textfile
Open strFilename For Input As #1
' Open file in text file format.

Do While Not EOF(1)  
    line input #1, textline
    Pos1 = mid(textline, 1, 10)
    Pos2 = mid(textline, 11, 14)
        .
        .
    Open App.Path & "\filename.asc" For Append As #2
    'To append from previous data
    Write #2, Pos1, " ", Pos2, .....
    'i dont know wat is the format u want as output
LOOP
close #1
Close #2

MsgBox "Process Complete...", vbInformation + vbOKOnly

          .
          . 'Other operations


i hav a sample which use ',' as delimiter to separate each field, n use instr function to search n capture data by searching each delimiter, since u hav no delimiter i think mid function can do it for u.

to check the time, i think better solution is to export data into a database (eg Ms Access) instead of a .asc file, then u can build function that use SQL to search a particular staff id , then compare its time in n out, n take the earliest or latest as u desired. (assume time format is 0000 for beginning of a day n 2359 the latest).

if ur company running shift overnightly then it will b more complicated since u hav to check the date also, but i think that should not b a problem.

regards,
daniel

Ok..trkcorp..I've tried your suggestion but I don't see anything as if nothing happens.

Send me a sample of the data...
trkcorp@midsouth.rr.com

Send me a sample of the data...
trkcorp@midsouth.rr.com

Did you state the file name correctly?  That is:

Open "c:\x.asc" For Random As #nFN Len = nRecLength

Where you have to change "c:\x.asc" to "YOUR_PATH\YOUR_FILE"
If not nothing will happen except you will create an empty x.asc file on your c drive root...

Ok since most of u suggested to export the .asc file to access database, I'll try and get it installed on my pc. Since with Access I can use SQL to query the info I need right, the only problem left would be to read the .asc file, separate each field for each record and then export it to Access?? Meanwhile keep the suggestions coming...

Regards.

Ok sorry trkcorp...it does work now...

Hi Neo78,

What is the function of the third-party system ? Just use to keep track of all the time-in and out of Staff? Any other functions provided by using this third-part system? If don't then you should stop using the .asc file as this will handle/replace by M$ Access.

Emm, maybe what i should ask is: How is your third-party system functioned?

Neo,
You see, if you do this you can read the data directly.  Then if you wish you can write it to another database or do whatever you want with it.  As soon as I got your test data I read it with this code and no mods.  As I suspected each record is separated by a CR & LF.  If you just need to read the raw data, this is the ticket.

It's a whole big system ryancys..It keeps track of who's going in and out...unauthorised entries....who can enter where and can't
Employees are given access to areas based on their job and security level...get the idea? :))
To enter an area, we have to swipe our security passes. Thus, the system logs the time and date and employee info..etc..

Ok since now I can read the file(thanx to trkcorp), what is the next step I should go about?

Well, I would get myself some kind of relational database (M$ Access, SQL Server, FoxPro, etc.), design tables and keys to accommodate the data and my ultimate goals for using the data.  The raw text with all of the random data is going to be of little use for any practical reporting purposes.  Then I would periodically read and "import" the raw data to my new database where I could really use it.  Then write inquiry screens, reports, etceteras to exploit that data in whatever fashion is required.  For instance, for any given period, (day, shift, week, month, year) I could report the total time each employee was on the premises, whether he was late or not, whether he was out to lunch too long, what days was he late?, how many days was he late this month, this quarter, the last six months?
I could create an employee master (empno, name, card, Dept, pos, Shift), a dept master, and a time and attendance table (empno, date, time) ordered by empno date and time for each employee.  A shift table so I can tell start & stop times, break times, etc. are for a shift.
Gee, I could do a million things with this data if it were stored in a convenient and well ordered manner, selectively accessible...  Sorry for the tirade, but you asked.

I was thinking....what if an employee comes in at night for work and goes back the next morning? In the file, there's no identification of whether he's coming in or going out. So far, we've assumed everything's on the same day thus the earliest time would indicate entry and latest would indicate exit. How would we know the difference then???

That is where an employee master file stating Shift may be helpful, if you have such arrangements.  For Ex: Shift 1 = 0800 - 1600, 2 = 1600 - 2400(0000), 3 = 2400(0000)- 0800.  If you don't have shift assignments or can't make some sort of assumption about in/out times, you are going to have a tough time knowing whether they're coming or going, especially if they work odd or long hours at odd times, given the limitations of the raw data.  I was wondering what that zero at the end of the data was.  Perhaps the cards could be (should be, might have been) encoded with shift there, logically it would seem that that may have been what that was intended to be...
What are the working arrangements?  Surely people don't just come and go as they please...  I mean aren't they expected to be there for some period each work day?  If you don't have shifts then perhaps assigned schedules.  On the emp master you could place an expected in/out time instead of shift.  Given that I have no real knowledge of the situation, the best I can do is speculate...  You need to do some serious analysis and planning.

<<ping>>

Neo,
What you have is a fixed length record with a carriage return & line feed at the end of each line. (At
least I had a CR & LF on the data I cut and pasted from your question...) Why don't you try this:
(1)Create a module and add a Type definition defining the fixed length record:

Option Explicit
Type TimeCard
 Door As String * 8
 Dte As String * 4
 Time As String * 4
 Card As String * 6
 Name As String * 20
 Empno As String * 12
 Dept As String * 8
 Position As String * 8
 Zero As String * 1
 CrLf As String * 2  
End Type

(2) For a test create a form and add a command button. Add this code to the click event. When you click
command you can walk through your data.  Replace "YOUR_FILE.asc" file name with that of your own test data...

Private Sub Command1_Click()
Dim TC As TimeCard
Dim lPosition As Long
Dim nRecLength As Integer
Dim nFN As Integer
Dim sStr As String
nFN = FreeFile
nRecLength = Len(TC)
lPosition = 1
Open "YOUR_FILE.asc" For Random As #nFN Len = nRecLength
Get #nFN, lPosition, TC
With TC
Do While Not EOF(nFN)
 sStr = "Door: " & .Door & vbCrLf
 sStr = sStr & "Date: " & .Dte & vbCrLf
 sStr = sStr & "Time: " & .Time & vbCrLf
 sStr = sStr & "Card: " & .Card & vbCrLf
 sStr = sStr & "Name: " & .Name & vbCrLf
 sStr = sStr & "Empno: " & .Empno & vbCrLf
 sStr = sStr & "Dept: " & .Dept & vbCrLf
 sStr = sStr & "Pos: " & .Position & vbCrLf
 sStr = sStr & "Zero: " & .Zero & vbCrLf
 If MsgBox(sStr & vbCrLf & vbCrLf & "Continue to read records?", _
   vbOKCancel + vbQuestion, "Here is your record!") = vbCancel _
   Then Exit Do
 lPosition = lPosition + 1
 Get #nFN, lPosition, TC
Loop
End With
Close #nFN
End Sub

Well, I would get myself some kind of relational database (M$ Access, SQL Server, FoxPro, etc.), design
tables and keys to accommodate the data and my ultimate goals for using the data.  The raw text with
all of the random data is going to be of little use for any practical reporting purposes.  Then I would
periodically read and "import" the raw data to my new database where I could really use it.  Then write
inquiry screens, reports, etceteras to exploit that data in whatever fashion is required.  For instance,
for any given period, (day, shift, week, month, year) I could report the total time each employee was
on the premises, whether he was late or not, whether he was out to lunch too long, what days was he
late?, how many days was he late this month, this quarter, the last six months?
I could create an employee master (empno, name, card, Dept, pos, Shift), a dept master, and a time and
attendance table (empno, date, time) ordered by empno date and time for each employee.  A shift table
so I can tell start & stop times, break times, etc. are for a shift.
Gee, I could do a million things with this data if it were stored in a convenient and well ordered manner,
selectively accessible...  Sorry for the tirade, but you asked.

I'm not closing this question as yet cos I would still like to have more comments and suggestions from u guys.
I've managed to read the info and put it into a flexgrid. So instead of flexgrid, I can just read the data and save it into a database like Access rite? From here, I can try and manipulate the data like what trkcorp has suggested?

Ok experts..
I've manages to read the file, sort out the info into 2 groups, Office and Production. I have done all these without saving the info into a database and displays the remaining info in a flexgrid. Right now, from the remaining records, I need to find the earliest time or latest time.
Suggestions anyone?

Regards.

Oh yeah, the remaining info also consists of multiple employee names with different times. So basically, I would like to narrow further to the earliest/latest time for each employee.

Neo,

are you against using the database?
SQL provides some nifty functions (like MIN and MAX) that would allow you to find and trap the earliest and latest times for an employee for a date.

Cheers!

:p i wanna use a database!!!! ....the only info i have to work on is in .asc format. this file is generated from another system. i can't change anything about that.

so what i have now is, i'm able to read the info (thousands of records) and display it in a flexgrid. so i guess i can read it and save it into a database rite b4 displaying the records? can i eliminate unwanted records and then save so the number of records is reduced and easier to work with?
And after i have done that, how about manipulating the records using SQL? I need help on that...

regards.

Neo78 has requested a point split.  To accomplish this, I have awarded one expert here, per your request and ask that you post two new questions in this topic area to award the other two experts the points you wish.  This question was reduced to 50 points and awarded, the rest was refunded to your account so you can now post the other two questions.

Please entitle each of the ..... Points for __expertname__ and in the comment, simply paste the URL of this quesiton.

They then will comment or propose an answer for their portions of this point split award for you to grade and close.

Further details provided to you here:
https://www.experts-exchange.com/questions/Q.20279573.html

Thanks,
Moondancer - EE Moderator