Import Text from Text file...

[arifmasum]

Dear members,

Please help me. I am working with a HR database.
I need to load the attendance data from employees from text file. Note that, employees attendance data is recorded through a card reader machine and it stays in text file format. I need to import that text file to my access table to calculate employee's attendance. What should I do and how?

Thanks in advance. Any help will be appriciated.

Arif Masum

 

[MStef]

Send a short example of your text file, (cca 10 records).

 

[arifmasum]

Here is the text file

Dear MStef,

Thank you very much for your reply. I am attaching a text file with this reply. Please note that first 3 digits represent machince ID which reads the card number, next 8 digit represents date (year, month, day) and next 6 digits represents time (hour, min, sec), last 10 digit represent ID card number. I want to get the work hour for each day for every employee. in this text file there are 3 different card number.

Hope to hear from you soon. Thanks in advance for your valuable time and efforts.

Arif Masum

 

[Mike375]

If you go to here

http://www.webcheatsheet.com/SQL/access_functions/mid.php

That is the link to Mid function. On the left you will see a whole list of functions including Right and Left functions.

You just import the text file as one field.

Because each of the components that you want are an equal number of characters and each type/category is an equal distance from the right or start of the field this will be easy for you.

In the date part where you have 20080413 if this varies for a day of less than 10, that is, 2008047 as opposed to 20080407 then the Len function will let you break the records in two. The Len() counts the number of characters in the field. It will be also on the website link I gave you.

 

[Mike375]

I forgot..here is your text file in Access so you know import works OK. The query will show you the Len fucntion working and fields for Mid/Right are made the same way

 

[arifmasum]

Thanks a lot mike. It helped me to work with the imported table. Now I need to allocate leave for employees. Can you advice me the method, i should use to allocate leave to employees?

Pls note that, the file you attached is the attendance file for the employees. Thanks in advance for your time and comments.

Best regards.

 

[stopher]

If you go to here

http://www.webcheatsheet.com/SQL/access_functions/mid.php

That is the link to Mid function. On the left you will see a whole list of functions including Right and Left functions.

You just import the text file as one field.

Using the import function (fixed width not delimited) will allow you to parse the text automatically (without having to use MID) and also saves you having to import the text into a temporary table. Furthermore you can save your import specification so that you can automate the import for future use.
Chris

 

[Mike375]

Chris,

I thought it was better to take the opportunity to learn Mid/Right on an easy example because one day it will be needed.

 

[arifmasum]

Thank you guys for coming up and help me grow my knowledge. By the way, I am able and completed that part. Now I am stack at leave management system. Can anybody guide me, how I can do it in MS Access? Thanks in advance for your time and efforts.

Masum