Automate import of data from several text files

[Phil Latio]

We have despatch process which kicks out picking information into text
files at hourly intervals.

The text files are named sequentially by process; 'ABC.txt',
'ABC01.txt', 'ABC02.txt' for one process, or 'XYZ.txt', 'XYZ01.txt' for
another process.

The folder contents would look like this;
ABC.txt
ABC01.txt
ABC02.txt
XYZ.txt
XYZ01.txt
XYZ02.tx
AAA.txt
AAA01.txt
AAA02.txt

I have created a database which has a link to the first file, the one
that does not contain a number in the filename.

I run a bit of code to import the data from the linked file.
I then go into Explorer, manually delete 'ABC.txt', and rename
'ABC01.txt' to 'ABC.txt'.
I do this until all the text files have been imported, for each process
- yikes!

The import of each set of process text files requires different bits of
code to manipulate them into the database.
So a different query would work on the ABC data to the query that works
on the XYZ data.

As I sit and peck away at the keyboard like a psychotic pigeon, doing
this manually, I realise this is a laborious method and need to refine
it!

My thoughts;
I think I need a SELECT CASE structure to determine the filename and
therefore the correct code to manipulate the data into the application.

Where I need some guidance, is how to automate this!!
I want to basically automate what I do manually - import data from
ABC.txt, delete the text file, grab ABC01.txt rename it to ABC.txt,
import it, delete the ABC.txt text file, grab ABC02.txt, rename it to
ABC.txt and so on!

Anyone have any ideas/thoughts as to which way I go with this?

Any comments are gratefully appreciated.

Thanks in advance.

Phil

[Douglas J. Steele]

Assuming that there will always be a 2 digit sequence number if there is a
sequence number, something like the following untested air-code should work:

Dim intDecimal As Integer
Dim strFolder As String
Dim strFile As String
Dim strFileNoSequence As String

strFolder = "C:\SomeFolder\SomeSubFolder\"
strFile = Dir(strFolder & "*.txt")
Do While Len(strFile) > 0
intDecimal = InStr(strFile, ".txt")
If IsNumeric(Mid(strFile, intDecimal-2, 2)) Then
strFileNoSequence = Left(strFile, intDecimal - 3) & ".txt"
Name strFolder & strFile As strFileNoSequence
Else
strFileNoSequence = strFile
End If

' At this point, you can import strFolder & strFileNoSequence

Kill strFolder & strFileNoSequence
strFile = Dir()
Loop

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Phil Latio" <ph********@lycos.co.uk> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

We have despatch process which kicks out picking information into text
files at hourly intervals.

The text files are named sequentially by process; 'ABC.txt',
'ABC01.txt', 'ABC02.txt' for one process, or 'XYZ.txt', 'XYZ01.txt' for
another process.

The folder contents would look like this;
ABC.txt
ABC01.txt
ABC02.txt
XYZ.txt
XYZ01.txt
XYZ02.tx
AAA.txt
AAA01.txt
AAA02.txt

I have created a database which has a link to the first file, the one
that does not contain a number in the filename.

I run a bit of code to import the data from the linked file.
I then go into Explorer, manually delete 'ABC.txt', and rename
'ABC01.txt' to 'ABC.txt'.
I do this until all the text files have been imported, for each process
- yikes!

The import of each set of process text files requires different bits of
code to manipulate them into the database.
So a different query would work on the ABC data to the query that works
on the XYZ data.

As I sit and peck away at the keyboard like a psychotic pigeon, doing
this manually, I realise this is a laborious method and need to refine
it!

My thoughts;
I think I need a SELECT CASE structure to determine the filename and
therefore the correct code to manipulate the data into the application.

Where I need some guidance, is how to automate this!!
I want to basically automate what I do manually - import data from
ABC.txt, delete the text file, grab ABC01.txt rename it to ABC.txt,
import it, delete the ABC.txt text file, grab ABC02.txt, rename it to
ABC.txt and so on!

Anyone have any ideas/thoughts as to which way I go with this?

Any comments are gratefully appreciated.

Thanks in advance.

Phil

[Phil Latio]

Hi Douglas,

sorry for the delay in responding to your reply, but just to say thanks
for the pointers and code snippet - it has really helped me out!

Many thanks

Phil

Douglas J. Steele wrote:

Assuming that there will always be a 2 digit sequence number if there is a
sequence number, something like the following untested air-code should work:

Dim intDecimal As Integer
Dim strFolder As String
Dim strFile As String
Dim strFileNoSequence As String

strFolder = "C:\SomeFolder\SomeSubFolder\"
strFile = Dir(strFolder & "*.txt")
Do While Len(strFile) > 0
intDecimal = InStr(strFile, ".txt")
If IsNumeric(Mid(strFile, intDecimal-2, 2)) Then
strFileNoSequence = Left(strFile, intDecimal - 3) & ".txt"
Name strFolder & strFile As strFileNoSequence
Else
strFileNoSequence = strFile
End If

' At this point, you can import strFolder & strFileNoSequence

Kill strFolder & strFileNoSequence
strFile = Dir()
Loop

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Phil Latio" <ph********@lycos.co.uk> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

We have despatch process which kicks out picking information into text
files at hourly intervals.

The text files are named sequentially by process; 'ABC.txt',
'ABC01.txt', 'ABC02.txt' for one process, or 'XYZ.txt', 'XYZ01.txt' for
another process.

The folder contents would look like this;
ABC.txt
ABC01.txt
ABC02.txt
XYZ.txt
XYZ01.txt
XYZ02.tx
AAA.txt
AAA01.txt
AAA02.txt

I have created a database which has a link to the first file, the one
that does not contain a number in the filename.

I run a bit of code to import the data from the linked file.
I then go into Explorer, manually delete 'ABC.txt', and rename
'ABC01.txt' to 'ABC.txt'.
I do this until all the text files have been imported, for each process
- yikes!

The import of each set of process text files requires different bits of
code to manipulate them into the database.
So a different query would work on the ABC data to the query that works
on the XYZ data.

As I sit and peck away at the keyboard like a psychotic pigeon, doing
this manually, I realise this is a laborious method and need to refine
it!

My thoughts;
I think I need a SELECT CASE structure to determine the filename and
therefore the correct code to manipulate the data into the application.

Where I need some guidance, is how to automate this!!
I want to basically automate what I do manually - import data from
ABC.txt, delete the text file, grab ABC01.txt rename it to ABC.txt,
import it, delete the ABC.txt text file, grab ABC02.txt, rename it to
ABC.txt and so on!

Anyone have any ideas/thoughts as to which way I go with this?

Any comments are gratefully appreciated.

Thanks in advance.

Phil