Pulling data from text file with custom extension

[dep999]

i'm intermediate with access... this one is a little more custom for me not to look for some guidance

I'm working with a software that I'm trying to keep inventory in access
the software has a built in way of tracking inventory but no reporting!

the software stores it inventory not in the DBF files for the database but in text documents with a custom extension .CNT (SHORT FOR COUNT) see examples below... the number IN the name ITM510.CNT
is the key number for the DBF that stores the database
ITM510.CNT
ITM515.CNT
ITM520.CNT
ITM525.CNT

In side each .CNT file there is a single line of data
6
24
10
1



I'm looking for a way to import or link these .CNT files so i can pull reports like what the inventory is, how many were sold in a day,week,month


any ideas?
have attached itm.dbf and some .CNT files

Itm.zip

[ItsMe]

Use a shell command to copy the .cnt file and paste it as .txt then import to DB

[dep999]

I can set up a batch file that daily would copy any .cnt files from the folder they reside in, to a new folder then do a ren *.cnt to *.txt

If someone has a suggestion on:
1) how to import multiple .txt files though an import micro
it would need to be something along the lines of:
Import *.txt from c:\inventory

2) I think It would be best to import it to a new table everyday
can you it be set up to do this and name the new table something like the date

3) when I import can I make make a coloum that refrences the file name IE. dropping the ITM
so if the file name is ITM510.txt and inside that .txt file is the number 6

the import would look like
Column 1
510
Column 2
6

I guess right now even someone opinion on am I going down the right path, is what I'm trying to do possible for access to handle with micros, VBA.

[June7]

1. code can iterate through the files of a folder and do what you want with each, in this case perhaps run TransferText method

2. Why do you think separate tables is best? Usually isn't but can be done. Refer to the TableName argument of TransferText.

3. column can be created and populated

Did you mean macros instead of micros? No, use VBA.

[dep999]

1. thank you the TransferText Method,
2. your right a separate table might not be best
3. very good

and yes I macros is what I was going for there!


so thinking out load for a min. Everyday I'm going to
create a new folder name it by the date IE. 20140207 copy my .CNT files to the folder Rename them to .txt files ... I'll do this from a batch file that way the end user won't need to do anything I can set it to run as an event

when they open the access database I'm going to have a VBA script that
imports the new .TXT files using the TransferText method to a table called counts

it will import
column 1
the ITM#
column 2
the count
column 3
the name of the folder its importing from 20140207

[June7]

Sounds like you understand what needs to be done now need to work on the how. Start pulling together the various pieces of code. Post question when you have specific issue.

Re item 3 - If you go with single table, the field should already be in table and just need to populate it with UPDATE sql action

[dep999]

Yea on the idea of a single table, I also want to be able to pull historical information
IE i want it so I can say what was your inventory as of 01/16/2014

[ItsMe]

Here is some food for thought.....

You could use Dir() to loop through the files in a given directory and retreive the names/paths for your transfertext.

You could also incorporate the entire thing into one procedure using the filesystemobject. Here is an example that loops through a folder and deletes the PDF files. SIDE NOTE: I use this code for files I place in there via code, so the file names don't need to be overly scrutinized within the If Then statement.

'Delete the PDF files in the temp folder
Dim strPath As String
    strPath = "\\ServerName\FolderName\TempEmailFolder"
Dim fsoFile As New FileSystemObject
Dim objFolder As Folder
Dim objFile As File
    Set objFolder = fsoFile.GetFolder(strPath)
    
        For Each objFile In objFolder.Files
        
            If InStr(objFile.Name, ".PDF") Then 'You could use Len() function to further validate
                objFile.Delete True
            End If
        
        Next
    
    Set objFolder = Nothing
'end 'Delete the PDF files in the temp folder

With objFile you could
objFile.Copy "PathToTemp.txt"

and then use the path for .txt for your transfertext.

This code would require reference to Microsoft Scripting Runtime