automate link to multiple text files

[Dave_epic]

Hi
I have 400 .txt files in fixed width format. I would like to import these into access as linked files (as the total file size is 80 GB.)

I would like to automate the process of course as it would take too long to link each file individually. The access field specifications are 10 fields of the following character length 6,8,10,1,8,8,7,1,3,1.

I'm not sure where to start with it. Is it a VBA function or can batch processing like this be done with an access query?

Regards

 

[DCrake]

I think linking 400+ text files to your mdb will become a bit unmageable, I would be tempted to import them all into one table and have an additional field that contains the name of the text file that the corresponding row relates to.

Or is there some other concept that does not allow this?

However 80GB is too big for Access you may need to look as SQL

David

 

[Dave_epic]

Yes, but the reason I wanted to link rather than import into one table is that access has a maximum file size of 2GB I think and the total of my text files is 80GB.

What I really want to do is run a text string search on all the files to find out in which files the a text string occurs. I thought the way to do this would be to run a union query on all the linked files, but there may be a quicker way to do this? I'm very new to VBA.

regards

 

[DCrake]

This is air code but if I wanted to know if a certain string existed in one or many text files then this is what I would be tempted to do.

I would iterate through the folder containing the text files. Hopefully this folder only contains text files.

I would open up each one in turn using the Open File For Input As #1 method

Loop through each row and use Instr() to test for the search string.

Depending on what your goal is would be defined here

when I reach the end of the file I would close it and move onto the next file.

My form would have a progress bar on that depicted the number or files found/processed in the folder and a further textbox detailing the name of the current text file and the number of occurances the search string was found.

What happens when you match your condition has not been defined as yet so I cannot comment at this point.

To link 400 files to acces then proceed to run queries on each table seems very memory intensive.

If you can explain what you intentions are this may lead me down another path.

David

 

[Dave_epic]

Hi
Thanks for your reply. This seems like the way to go though I don't know much about VBA code I'm afraid. If you had some pointers about where to find code for your processes on here that would be helpful.

• The files are currently in ASCII format but I can batch convert all files to .txt with a program I have.

• All I want to do when I match the condition is count the occurences per file.

• The text string I want to search for is actually ten 0's but only if it occurs in row 3 as it is in the access row specifications below. So in the text file it would occur only in the 15th to the 25th character of the row. If you see what I mean.

"The access field specifications are 10 fields of the following character length 6,8,10,1,8,8,7,1,3,1. "

Regards

 

[DCrake]

If you can post a couple of sample files with minimul data in them and highlight the data you want to examine I will look at a solution for you.

David

 

[Dave_epic]

Thanks

Here are a couple of text files with sample data.
They are in fixed width format. The data looks like this:

0001197201011001400078R000000000000000032...120000I
0001199201011001400256R00000000000000005853.110000I
0001199212211066000000R19931221199212218B314000000I
0001199212210000000000R19931221000000008B314000000I

The text string I want to search for is actually ten 0's which may occur only in the 15th to the 25th character of the row. (highlighted above) If you see what I mean.

Many thanks.

P.S. I was born in Burnley and went to Mansfield High School in Brierfield. Used to go on Turf Moor quite a lot.

David

 

[DCrake]

I unmderstand fully what you want to do and can offer a full solution, my only concern is that the goal is to only find out the total number of occurances of 10 0's in the 400 text files?

Do you need to know how many occurances there were in each specific file?
Then a grand total?
Is there anything about the actual record you need to know about? if so what?

What naming convention has been used, if any, with the incoming text files?
Do they contain headers and footers?

Is this going to be a repetative exercise or a one off job?

The original concept of linking the 400 files is definately a overblown exercise for the anticipated outcome.

David

 

[Dave_epic]

Thanks for your reply.

I think you are right that this is too much just for a count.

What I want to do in the future is to find a way of running the type of sql queries access can without importing files that are too big.. This may be looping through each one or amalgamating each file into one text file with the file name in a new column as an identifier as you mentioned in your first post. This latter may be the best way.
Though Access is what I know it may be time to learn another program like SQL server.

Anyway no worries and thanks for your help.

 

[DCrake]

Did you get my PM to you private email, if not here is a demo of a solution.

 

[DCrake]

Tested it on a new pc and realised I did not send any sample files here is a revised version. You will need to save the zip file locally then unzip it or you will only have read only status on the demo and it will not delete any records or refresh list boxes.

David