Import specific data from file

[hniessen]

Dear experts,

I'm trying to import data from a file Epl_asc.exf. This file is generated by another programm and is ascii compatible. Within this file I am looking for lines that start with code '117' (at the end of this message I added a fragment of such a file). As you can see the data is not delimited by a comma or semi-colon everwhere. So I was thinking to create some sort of turing machine that scans the file and imports the desired data into a table. But here lies the problem for me. How can I create some such a function within Access with help of VBA.

I want to thank everbody in advance who is willing to rack his\her brain over this problem.

Hans Niessen

fragment of the file Epl_asc.exf:

850 1120,7360,0,255,-84
105 1,3,0,0,NHOOFDZEKERING: 25A
998 DHAUPTSICHERUNG: 25A
998 EFUSE IN MAIN: 25A
998 FFUSIBLE DE SECTEUR: 25A
998 SFUSIBLE PRINCIPAL: 25A
998 OHOVEDSIKRING: 25A
998 K
998 PFUS¡VEL NA REDE: 25A
998

850 1440,1120,0,255,-281
851
852 166,7,0,4,0

850 1440,3520,0,255,-198
117 1,1,1,0,1L1

850 1440,5760,0,255,-128
116 10,7,1,4,0
116 1,960,160,960,160
116 1,960,160,960,160
116 10,2,3,4,0
116 1,640,160,960,160


"Funny how such little knowledge can get me into so much trouble"

 

[hermanlaksko]

Hi Hans

Create a table consisting of 2 fields 1 text (len 3) 2 text (255), import your file into this table as text.
Now we have the data in out base and can now do what ever we want i.e. delete all records not haveing 117 in field 1.

 

[hniessen]

Herman,

Thanks for your help. I have tryed it and it worked. But the file I want to import consists of up to 12,000 lines and only up to 300 lines contain the code '117'. Therefore I am looking for a way to import the data more efficient since the data has to be imported every time the file is changed.

Greatings,
Hans

"There is never time to do it right but there is always time to do it over"

 

[THoey]

Actually, I think Herman's way is fine. If you try to loop through the data file, you are still going to have to go through each record and manually search for the 117. This would probably make the import longer than just to load everything. And then deleting everything w/o 117, or maybe just copying everything with 117 from this staging table to your table shouldn't take too long... Terry M. Hoey

 

[THoey]

Another thought. There is software on the market called Monarch by Datawatch, that could take this file and strip out only the records with 117. The following is their web page. Take a look, it might be worth the purchase:

http://www.datawatch.com/

Terry M. Hoey

 

[hniessen]

Thanks for the support.

Greatings,
Hans

 

[microdisney]

Just FYI...
There is a great utility for Unix and Windows called grep.
You can tell it to search for any pattern and pipe it to a file of your choice. I've used this utility on Unix, AS400 and WINNT and it works like a charm. Here is an example.
grep ^117 nameoffiletosearch > nameoffileyouwanttopipeto.
This will search the beginning of each line that contains 117.

HTH

 

[microdisney]

Just FYI...
There is a great utility for Unix and Windows called grep.
You can tell it to search for any pattern and pipe it to a file of your choice. I've used this utility on Unix, AS400 and WINNT and it works like a charm. Here is an example.
grep ^117 nameoffiletosearch > nameoffileyouwanttopipeto.
This will search the beginning of each line, find records that contains 117 and pipe the results to a new file.

HTH

 

[hniessen]

Thanks,

But there is one problem. I know to use the command grep in linux and unix. But how does it work in Windows NT????

Greatings,
Hans

 

[jtr1888]

I got my copy of awk and grep from the Free Software Fondation site. The CD was well worth the price.

http://www.fsf.org/order/windows.html

Regards,

Eugene Dew