Importing a series of txt documents

[Guest]

I need to write code that will import all txt documents in a specified file
location. Maybe a For Each...Next?

 

[Teh Brian]

You could make a batch file with a command like "dir/b>filedump.txt" to
create a text file with all the file names in a given directory. Then
run the batch file from access with the DoCmd.RunApp method. You could
then import the "filedumpt.txt" file using the TransferText method to
create a table of file names. Then import all the file names from the
imported table by using a loop. This could be accomplished using a
recordset of the table called "rec" for instance (rec.MoveFirst and
rec.MoveNext seem like they would be handy here)
Good Luck

 

[Teh Brian]

You could make a batch file with a command like "dir/b>filedump.txt" to
create a text file with all the file names in a given directory. Then
run the batch file from access with the DoCmd.RunApp method. You could
then import the "filedumpt.txt" file using the TransferText method to
create a table of file names. Then import all the file names from the
imported table by using a loop. This could be accomplished using a
recordset of the table called "rec" for instance (rec.MoveFirst and
rec.MoveNext seem like they would be handy here)
Good Luck

 

[Pieter Wijnen]

Or

Dim FName As String

FName = Dir("C:\temp\*.txt)
While Len(FName)
DoCmd.Transfertext ...
Fname=Dir()
Wend


HTH

Pieter

 

[Pieter Wijnen]

Or

Dim FName As String

FName = Dir("C:\temp\*.txt)
While Len(FName)
DoCmd.Transfertext ...
Fname=Dir()
Wend


HTH

Pieter

 

[Teh Brian]

Pieter is right; this is a much more elegant solution. Remember the
second quote in the 'FName =' line

FName = Dir("C:\temp\*.txt")

 

[Teh Brian]

Pieter is right; this is a much more elegant solution. Remember the
second quote in the 'FName =' line

FName = Dir("C:\temp\*.txt")

 

[Pieter Wijnen]

caught me out cold again <g>

Pieter

 

[Pieter Wijnen]

caught me out cold again <g>

Pieter

 

[gdaniels]

Hi, I need some help. The routine below runs when a command button is
clicked. A prompt appears to enter the directory path to the folder
containing delimited text files to be imported into an Access 97 table.
The test folder contains 9 files. The problem is that the 1st file is
imported 9 times into the table. How do I correct the routine so it
loops properly and imports each file? Thanks in advance.



Gary


Private Sub Command0_Click()
Dim InputDir, ImportFile As String, tblName As String
Dim InputMsg, Name As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
InputDir = InputBox(InputMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.txt")
Name = ((InputDir) & ("\") & (ImportFile))
Do While Len(ImportFile) > 0
' Use the import file name without its extension as the table
' name.
tblName = "Expenses"

DoCmd.TransferText [acImportDelim], "Batch", "ExpBatch", Name
ImportFile = Dir
Loop
End Sub