Advice Needed

[melodyF]

An overview of what I have been tasked to try to do. We have a text file that is downloaded every day from a progam called Observer/Composer which monitors productivity on our ACD lines. We would like to automatically import this data from the txt file and place in an Access 97 table on a daily basis. I have some experience in Access, but I'm not sure if I have enough for this task. Is their a third party piece of software that anybody knows of that might help me or some code that you might know of that I may be able to stumble through to complete the above task. Any Help would gratly be appreciated. Below is an example of the text file we recive daily.

AGENT AGENT
ID NAME UNAVAL WORK
______ __________ ______ _____
810976 BBUNN 11.7 12.1
811058 BLUCAS 18.5 8.7
811061 ABENNETT 27.4 0.9
812100 MPITTMAN 25 8.4
813952 SWINSTEAD 12.9 32.7
814458 AAGAR 14.2 61.4
814832 LHOWELL 29 25.7
815741 JHETZLER 15.4 6.2
817492 JHALES 22.3 12.3
818148 SBEALE 15.8 18.6
818220 SBEST 0 37.2
819972 JEVANS 10.1 22.6
832672 JGREENE 21.9 23.9
832674 BNORVILLE 23.2 64.3
860694 DFOWLER 0 98.7
878593 BDEAN 32.9 65.7


Thanks,
Melody

 

[Nero]

Hi,
Does your software have the option of exporting this data as a .dbf file or is it just .txt only?
If there is a way of changing the format to .dbf then I have some code I will share with you that wull solve your problem.

 

[jatfill]

is the file name always the same?

The reason I ask is because this can be done with text files or DBF information fairly easily. You're going to want to look at the TransferText method in VB for the import... I'll be more than happy to get into the specifics, I'm doing this now (in Access 2000, though) for some long distance call data, and it really just took me an afternoon to set up.

The first step is to create an import spec for the data file so that access will know where in each column certain data is stored for future use:

You can do this by manually importing a file the first time. Select import from the Tables tab, then select the text file with the data in it and click Import.

From the looks of your file, I would select the Delimited option and use space as the Delimiter. Select "append to existing table" if you already have a table, if not you can create one.
Before you click finished, click the "Advanced" button and verify the import specs, click "Save As" and then name it whatever you like (i.e. "ACD_IMPORT") then finish.

Now your database has an import specification for your file that you can call at any time.

If you don't have any VBA experience, you can easily create a Macro to automatically import new files:

Create a new macro, and on the action, select "TransferText:"

for the Action Arguments:
Transfer Type: Import Delimited
Specification Name: (your spec created above)
Table Name: (your table)
File Name: The path to the file you want to import
Has Field Names: if your file has the field names listed at the first line, then the answer is yes


If your file name changes or the path changes, you'll want to consider switching to VB to handle this. If you prefer using VB anyway, let me know and I'll send you a sample of what I'm doing currently.

good luck...