Many File Import

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I need to import approximately 230 text files into
access. Is there any way to import all 230 at one time.
For instance, is there a way to import a whole folder or
something like that.
 
Hi Mike,

This procedure - originally posted by Joe Fallon MVP - should get you
started:

How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub
 
Thank You,

Just one question. How/where would I go about setting
this procedure up. And if I remove the Kill line, will
the files just remain where they are?

-----Original Message-----
Hi Mike,

This procedure - originally posted by Joe Fallon MVP - should get you
started:

How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText
Click to expand...
acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub

I need to import approximately 230 text files into
access. Is there any way to import all 230 at one time.
For instance, is there a way to import a whole folder or
something like that.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Mike,

I think Joe envisaged having a form with an "Import all files" button.
This code would be in the button's Click event procedure. If you want to
specify the folder and filename at run-time, you can replace the
hard-coded lines
ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
with something like
ChDir Me.txtFolder.Value
strfile = Dir(Me.txtFileName.Value)
where txtFolder and txtFilename are the names of textboxes on the form:
just type the names in.

If you remove the Kill line the files will stay where they are - but if
there's any interruption in the import process it won't be easy to tell
which files ahve been imported and which not. One approach would be to
copy the files you want into a temporary folder and import them from
there (with the Kill statement), leaving the originals undisturbed
elsewhere. ALternatively, use the Move statement to move each file
instead of Kill-ing it.


Thank You,

Just one question. How/where would I go about setting
this procedure up. And if I remove the Kill line, will
the files just remain where they are?

-----Original Message-----
Hi Mike,

This procedure - originally posted by Joe Fallon MVP - should get you
started:

How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText
Click to expand...
acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub

I need to import approximately 230 text files into
access. Is there any way to import all 230 at one time.
For instance, is there a way to import a whole folder or
something like that.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
How to create the "ImportSpecName".

I have an MDB having this implementation of importing text delimited files.
But I couldnt locate where the "spec" files are situated.
Any help in finding them?
 
Hi Satya,

Import the file (or a file with the same structure) once manually. As
you do so, click the Advanced... button in the Text Import Wizard. This
allows you to create and save the specification (which is stored in the
mdb, not as a separate file).
 
Back
Top