Text files (about 1400 of them) import...

[Doran]

Background:
=======
1. My directory Name is N:\ASCDATA\
2. File name will vary but all with have same txt
extension (there are 1400 text files)
3. They will all be fixed delimited and same format (which
i can write a function and call the function)


My Goal:
=====
1. Click a Button
2. After click, system should import this files (one by
one) into a table called "tblASC"
3. After importing the data, system should move that file
to another direcotory called "N:\ASCData\Done\
4. System should now import the next text file from the
same directory "N:\ASCDATA\"
5. After all the import . Msgbox "Import is done, Total
Import file 1400 (count)"

Is it possible to accomplish ? Can anyone please please
help? I am new to this kinda programming.




= = = Code = = =
This example uses the Dir function to check if certain
files and directories exist. The MacID function may be
used on the Macintosh to specify the file type.

Dim MyFile, MyPath, MyName
' In Microsoft Windows:
' Returns "WIN.INI" if it exists.
MyFile = Dir("C:\WINDOWS\WIN.INI")

' Returns filename with specified extension. If more than
one *.ini
' file exists, the first file found is returned.
MyFile = Dir("C:\WINDOWS\*.INI")

' Call Dir again without arguments to return the next
*.INI file in the
' same directory.
MyFile = Dir

' Return first *.TXT file with a set hidden attribute.
MyFile = Dir("*.TXT", vbHidden)

' Display the names in C:\ that represent directories.
MyPath = "c:\" ' Set the path.
MyName = Dir(MyPath, vbDirectory) ' Retrieve the first
entry.
Do While MyName <> "" ' Start the loop.
' Ignore the current directory and the encompassing
directory.
If MyName <> "." And MyName <> ".." Then
' Use bitwise comparison to make sure MyName is a
directory.

If (GetAttr(MyPath & MyName) And vbDirectory) =
vbDirectory Then
Debug.Print MyName ' Display entry only if it
End If ' it represents a directory.
End If
MyName = Dir ' Get next entry.
Loop

' On the Macintosh:
' Use the MacID function to specify file type.
' The following statement returns the first "TEXT" file
found in the
' specified directory or folder.
MyFile = Dir("HD:MY FOLDER:", MacID("TEXT"))

 

[Guest]

do you need to keep 1400 tables separate?
if not, and they are in some format and dont need to know
which source file a record came from,

simply try copying all files into single text file, then
import it

 

[doran]

Will the new code work??

Dim MyFile, MyPath, MyName, fs
MyFile = Dir("N:\ASCDATA\*.txt")
Set fs = CreateObject("Scripting.FileSystemObject")
Do While MyName <> ""
DoCmd.DeleteObject acTable, "NewData"
DoCmd.Transfertext acImportdelim, , "NewData", myfile
DoCmd.OpenQuery "Append NewData to your final output"
fs.CopyFile myfile, "N:\ASCDATA\Done\"
'fs.deletfile myfile <=== this sucker will kill you ( if
it works)
MyName = Dir
Loop

 

[Doran]

I have to import about 1400 text files every other week.
They are being dumped by another system from anohter
company. They are all in same fixed delimited format. All
I want to do is to append the data and then move the file
to another directory. I hope I am now clear. I dont want
to manually append them into one text file and then
import. It's gonna be tons of work.

 

[doran]

I have to import about 1400 text files every other week.
They are being dumped by another system from anohter
company. They are all in same fixed delimited format. All
I want to do is to append the data and then move the file
to another directory. I hope I am now clear. I dont want
to manually append them into one text file and then
import. It's gonna be tons of work.

 

[Guest]

its manual but can merge them into one just once then
import it

 

[???]

kkkk

I have to import about 1400 text files every other week.
They are being dumped by another system from anohter
company. They are all in same fixed delimited format. All
I want to do is to append the data and then move the file
to another directory. I hope I am now clear. I dont want
to manually append them into one text file and then
import. It's gonna be tons of work.