Text Files

[Wasp1]

What I am needing assistance on:



To create a module that will capture ALL of my TEXT file names (My .txt file names varies in length) and be able to count how many records is in each of my text files and update my table with this information. This will all go into one table called: I do not want to import these .txt files.

tblFileName

FileNameId-Auto
FileName - Text
RecCount - Number


Respectfully

Wasp1

[rpeare]

not a lot of information here. For instance, are you searching one directory? Are you recursively searching all folders on your computer? Local drives? Network drives? is your goal just to count the number of times a specific text file name occurs?

All of this would be done using filesystemobject commands

[Wasp1]

The .txt files are located in a specific folder on our company share drive. So, i am only using this one folder. The vendors send in CDs (Yes! CDs) for their telephone billing. My tasking is to capture each file name and record count and place this in a table. Then I will need to create a FTP script using the vendors FILENAMES.TXT files. The file names cannot be altered in any way. This is our company policy. Once I create the FTP Script the FILENAMES.TXT files will be FTP to our company mainframe.

[rpeare]

oh so you're trying to count records within the text files. Again this is all done with filesystemobject

Look at this to cycle through text files in a specific directory:
http://blogs.technet.com/b/heyscript...ubfolders.aspx

Look at this to open a text file and cycle through individual lines within it:
http://stackoverflow.com/questions/1...point-vba-code

with these two tools you can figure out how many lines are in each text file then append the results to your table using the .execute method.

[Wasp1]

ok. But how to I capture the file names? For example: One vendor CD will be file name ABCDE.TXT while others are 12345.txt. Do you have any reading material for how to capture the file names and update my table?

[rpeare]

the link I gave you tells you how to capture the file name. The example just prints it to the screen instead of storing it as a variable so you can do something with it.

[Wasp1]

ok. thanks

[casinc815]

Wasp1

Did you get it working? I am interested in your outcome since I have a similar task!

I would appreciate a note about your solution!

Thanks!

Jim

[Wasp1]

Jim,

Here is what i have so far. I still need assistance on counting records in each of my .txt files. Any other thoughts on how to modify my existing code and add counting records?



Option Compare Database
Option Explicit

'Creating a FileSystemObject
Public FSO As New FileSystemObject



Sub ListFiles()

'Declaring variables
Dim objFolder As Folder
Dim objFile As File
Dim strPath As String
Dim NextRow As Long
Dim objExcelApp As Object
Dim wb As Object

'Specify the path of the folder

strPath = "C:\Documents\Darlene"

'Create the object of this folder
Set objFolder = FSO.GetFolder(strPath)

'Check if the folder is empty or not
If objFolder.Files.Count = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If


Set objExcelApp = CreateObject("Excel.Application")

Set wb = objExcelApp.Workbooks.Open("C:\Documents\Darlene\E xcel\book1.xls")

'================================================= ==========
wb.Activesheet.Cells(1, "A").Value = "File Name"
wb.Activesheet.Cells(1, "B").Value = "Size"
wb.Activesheet.Cells(1, "C").Value = "Modified Date/Time"


'================================================= ==========

'Find the next available row

NextRow = wb.Activesheet.Cells(wb.Activesheet.Rows.Count, "A").End(-4162).Row + 1

'Loop through each file in the folder
For Each objFile In objFolder.Files

'List the name, size, and date/time of the current file
wb.Activesheet.Cells(NextRow, 1).Value = objFile.Name
wb.Activesheet.Cells(NextRow, 2).Value = objFile.Size
wb.Activesheet.Cells(NextRow, 3).Value = objFile.DateLastModified

'Find the next row
NextRow = NextRow + 1
Next objFile

wb.Save
wb.Close
objExcelApp.Quit
Set wb = Nothing
Set objExcelApp = Nothing
End Sub

[casinc815]

Jim,

Here is what i have so far. I still need assistance on counting records in each of my .txt files. Any other thoughts on how to modify my existing code and add counting records?



Option Compare Database
Option Explicit

'Creating a FileSystemObject
Public FSO As New FileSystemObject



Sub ListFiles()

'Declaring variables
Dim objFolder As Folder
Dim objFile As File
Dim strPath As String
Dim NextRow As Long
Dim objExcelApp As Object
Dim wb As Object

'Specify the path of the folder

strPath = "C:\Documents\Darlene"

'Create the object of this folder
Set objFolder = FSO.GetFolder(strPath)

'Check if the folder is empty or not
If objFolder.Files.Count = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If


Set objExcelApp = CreateObject("Excel.Application")

Set wb = objExcelApp.Workbooks.Open("C:\Documents\Darlene\E xcel\book1.xls")

'================================================= ==========
wb.Activesheet.Cells(1, "A").Value = "File Name"
wb.Activesheet.Cells(1, "B").Value = "Size"
wb.Activesheet.Cells(1, "C").Value = "Modified Date/Time"


'================================================= ==========

'Find the next available row

NextRow = wb.Activesheet.Cells(wb.Activesheet.Rows.Count, "A").End(-4162).Row + 1

'Loop through each file in the folder
For Each objFile In objFolder.Files

'List the name, size, and date/time of the current file
wb.Activesheet.Cells(NextRow, 1).Value = objFile.Name
wb.Activesheet.Cells(NextRow, 2).Value = objFile.Size
wb.Activesheet.Cells(NextRow, 3).Value = objFile.DateLastModified

'Find the next row
NextRow = NextRow + 1
Next objFile

wb.Save
wb.Close
objExcelApp.Quit
Set wb = Nothing
Set objExcelApp = Nothing
End Sub

I need a few questions answered.

1) How many files are found in the folder?
2) All files end in TXT, Y/N?
3) Your code indicates Excel is being used. What for and why?

The hyperlinks provided by rpeare is a good link. Why have you not worked off of it?

What platform is the FTP box? Unix? Windows? etc?
If files go to the FTP server that may be a UNIX box and this app is in the wrong place. There are unix
commands that can be automated to diagnose a file for what you are doing.

The name, size and date modifed are all file notations and can be extracted from the file name?

So Wasp1, we need more details! Provide the details and I would be more than willing to help!

[casinc815]

Jim,

Here is what i have so far. I still need assistance on counting records in each of my .txt files. Any other thoughts on how to modify my existing code and add counting records?



Option Compare Database
Option Explicit

'Creating a FileSystemObject
Public FSO As New FileSystemObject



Sub ListFiles()

'Declaring variables
Dim objFolder As Folder
Dim objFile As File
Dim strPath As String
Dim NextRow As Long
Dim objExcelApp As Object
Dim wb As Object

'Specify the path of the folder

strPath = "C:\Documents\Darlene"

'Create the object of this folder
Set objFolder = FSO.GetFolder(strPath)

'Check if the folder is empty or not
If objFolder.Files.Count = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If


Set objExcelApp = CreateObject("Excel.Application")

Set wb = objExcelApp.Workbooks.Open("C:\Documents\Darlene\E xcel\book1.xls")

'================================================= ==========
wb.Activesheet.Cells(1, "A").Value = "File Name"
wb.Activesheet.Cells(1, "B").Value = "Size"
wb.Activesheet.Cells(1, "C").Value = "Modified Date/Time"


'================================================= ==========

'Find the next available row

NextRow = wb.Activesheet.Cells(wb.Activesheet.Rows.Count, "A").End(-4162).Row + 1

'Loop through each file in the folder
For Each objFile In objFolder.Files

'List the name, size, and date/time of the current file
wb.Activesheet.Cells(NextRow, 1).Value = objFile.Name
wb.Activesheet.Cells(NextRow, 2).Value = objFile.Size
wb.Activesheet.Cells(NextRow, 3).Value = objFile.DateLastModified

'Find the next row
NextRow = NextRow + 1
Next objFile

wb.Save
wb.Close
objExcelApp.Quit
Set wb = Nothing
Set objExcelApp = Nothing
End Sub

I need a few questions answered.

1) How many files are found in the folder?
2) All files end in TXT, Y/N?
3) Your code indicates Excel is being used. What for and why?

The hyperlinks provided by rpeare is a good link. Why have you not worked off of it?

What platform is the FTP box? Unix? Windows? etc?
If files go to the FTP server that may be a UNIX box and this app is in the wrong place. There are unix
commands that can be automated to diagnose a file for what you are doing.

The name, size and date modifed are all file notations and can be extracted from the file name?

So Wasp1, we need more details! Provide the details and I would be more than willing to help!

I am a Data/ETL architect and work in SQL or Oracle. I tell you this for you still have to create a recordset
to work through the count of individual records and I do not see any DML code for insert and update into your table with file,size and date in it. Also the string containing filename will need to be cut, therefore you will be working with Left, Right and MID to extract the appropriate information. Consult the script on your FTP server and you may be able to do some scripting there. create another flatfile representing the upload process from the FTP server, and import it into Access or Excel for storage. Lotsa work here! Good luck!

[Wasp1]

My responses are sited below:

I need a few questions answered.

1. How many files are found in the folder?

Answer: The range is 1-100 files a day.

1. All files end in TXT, Y/N?

Answer: Y

1. Your code indicates Excel is being used. What for and why?

Answer: The spreadsheet goes back to the vendor on what files.txt were processed

The hyperlinks provided by rpeare is a good link. Why have you not worked off of it?
Answer: The link for counting records only addresses counting 1 text file. I have to count EACH text file and record the number of records per .txt.

What platform is the FTP box? Unix? Windows? etc?
Answer: Windows


The name, size and date modifed are all file notations and can be extracted from the file name?

Answer: Yes. The file name is captured and gets updated in my access db tables and I have a module that creates ftp script and .bat file based on the file names and adds these files and it gets ftp to our mainframe.

Since im able to obtain the filenames.txt and it gets FTP to our mainframe. This works fine.
My last step is to modify the code I shared with you and be able to read and write the record count for each .txt files. I’m issues with this and don’t know how to count records in a txt file. This is new to me. Iv don’t need to import the data because there is no need to.

[rpeare]

The link I provided didn't only address one file. I linked a method to cycle through all files in a folder, and I also linked a method to cycle through all lines in a text file.

'Declaring variables
Dim objFolder As Folder
Dim objFile As File
Dim strPath As String
dim db as database
dim sfilename as string
dim f
dim fs
dim iRecCt as long

'Specify the path of the folder

set fs = createobject("scripting.filesystemobject")

strPath = "C:\Documents\Darlene"

'Create the object of this folder
Set objFolder = fs.GetFolder(strPath)

'Check if the folder is empty or not
If objFolder.Files.Count = 0 Then
       MsgBox "No files were found...", vbExclamation
       Exit Sub
Else
    set db = currentdb
    For each objFile in objFolder.Files
        sfilename = objfile.name
        ifilesize = objfile.size
        dmoddate = objfile.datelastmodified

        set f = fs.opentextfile
        iRecCt = 1
        do while f.atendofstream <> true
            irecct=irecct+1
            f.readline
        loop

        f.close
        ssql = "INSERT INTO tblData (FileName, FileSize, FileModified, FileRecords) VALUES ('" & sfilename & "', " & ifilesize & ", #" & dmoddate & "#," & iRecCt & ")"
        db.execute (ssql)
    next objfile
    set fs= nothing
    set db = nothing
End If

I didn't test this code so there may be some errors, but this assumes you're trying to append the file name, size, the modified date and the number of LINES, NOTE THIS IS DIFFERENT THAN THE NUMBER OF RECORDS IN SOME CASES, to a table called tblData.

[Wasp1]

ok. Let me look at this and will get back to you. Thanks

[Wasp1]

I'm reviewing you code suggestions: I run into error code 450 error. Wrong number of arguments or invalid property assignment.


After reviewing this code, I ran into error code 450. I looked up:
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

how do i change:
From:
Set f = fs.OpenTextFile("c:\testfile.txt", ForAppending,TristateFalse)

To Open multiple text files??????
Set f = fs.OpenTextFile("multiple text files", ForAppending,TristateFalse)


Option Compare Database
Option Explicit
Public FSO As New FileSystemObject

Sub MyList()

'Declaring variables
Dim objFolder As Folder
Dim objFile As File
Dim strPath As String
Dim db As Database
Dim sfilename As String
Dim ifilesize As String
Dim SSQL As Object
Dim dmoddate As Date
Dim fs, f, retstring
Dim MYType As String
Dim iRecCt As Long

'Specify the path of the folder
Set fs = CreateObject("scripting.filesystemobject")

strPath = "C:\Documents\Darlene"
'Create the object of this folder
Set objFolder = fs.GetFolder(strPath)
'Check if the folder is empty or not
If objFolder.Files.Count = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
Else
Set db = CurrentDb
For Each objFile In objFolder.Files
sfilename = objFile.Name
ifilesize = objFile.Size
dmoddate = objFile.DateLastModified
MYType = objFile.Type


'How to modify to account for multiple text files
Set f = fs.OpenTextFile

iRecCt = 1

Do While f.AtEndOfStream <> True
iRecCt = iRecCt + 1
retstring = f.ReadLine
Loop
f.Close

SSQL = "INSERT INTO tblData (FileName, FileSize, FileModified, FileRecords) VALUES ('" & sfilename & "', " & ifilesize & ", #" & dmoddate & "#," & iRecCt & ")"
db.Execute (SSQL)
Next objFile
Set fs = Nothing
Set db = Nothing

End If
End Sub