Solved

Open and extract info from file with .asc extension

Posted on 2002-03-07
33
377 Views
Last Modified: 2011-09-20
Guys, I need to write a program to check the time employees come to work and go back home. My company uses a system which keep tracks of anyone going in and out everyday thus the number of transactions at the end of the day will be in thousands. The system is able to export the transactions into a file with .asc extension but basically it's a text file. I've opened it in Windows and checked.

My problem is I need to write a program to open that file and extract the info in it. The text in it are kinda jumbled up, looks something like this :

ProdEntr03011929009917Abd Khalid          00013       QA      Senior E0
Entrance03011459012697Chia Soo Zern       V12697      Vendor  None    0
Cantin  03010838013511Chan Su Chen        00012       QA      Incoming0
FinalVis03010956021486TPV 001             TPV 001     ProductiTrainee 0
Receptio03010843018356See Yong Sian       00014       ProductiEngineer0

These r 5 examples which is suppose to look like this:

ProdEntr(door) 0301(date) 1929(time) 009917(card no) Abd Khalid(name) 00013(emp no) QA(dept) Senior E(position).

I've explained what they mean in brackets so u guys know.
The last character(O) is ignored.

Then, I will have to pick out the first time they enter and the last time they leave as there can be lots of going in and out.

I have other criterias to fulfill but will let u guys know when someone gives some suggestions or ask for them. I'm not asking anyone to do for me but would be great haha...just some help on how to do it. I have ideas on what I need from the program but not sure how to do it.
Have only 3 weeks to do this..****....More info will be provided upon request.

Thanks guys.
Regards.
0
Comment
Question by:Neo78
  • 15
  • 10
  • 5
  • +3
33 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
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
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
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.
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
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. :((
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
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...
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
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
0
 
LVL 4

Accepted Solution

by:
trkcorp earned 50 total points
Comment Utility
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 my c:\x.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 "c:\x.asc" For Random As #nFN Len = nRecLength
Get #nFN, lPosition, TC
With TC
Do While Not EOF(nFN)
  sStr = "Door: " & .Card & 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
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
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?
0
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
OOPS!  The line:

sStr = "Door: " & .Card & vbCrLf

Should read:

sStr = "Door: " & .Door & vbCrLf
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
> 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
0
 
LVL 1

Expert Comment

by:daniellyh
Comment Utility
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
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
Ok..trkcorp..I've tried your suggestion but I don't see anything as if nothing happens.
0
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
Send me a sample of the data...
trkcorp@midsouth.rr.com
0
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
Send me a sample of the data...
trkcorp@midsouth.rr.com
0
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
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...
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:Neo78
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
Ok sorry trkcorp...it does work now...
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
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?
0
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
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..
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
Ok since now I can read the file(thanx to trkcorp), what is the next step I should go about?
0
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
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???
0
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
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.
0
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
<<ping>>
0
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
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?
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:ebosscher
Comment Utility
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!
0
 
LVL 1

Author Comment

by:Neo78
Comment Utility
: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.
0
 
LVL 1

Expert Comment

by:Moondancer
Comment Utility
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:
http://www.experts-exchange.com/questions/Q.20279573.html

Thanks,
Moondancer - EE Moderator
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

771 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

11 Experts available now in Live!

Get 1:1 Help Now