Is importing multiple files at the same time possible ?

S

Sirritys

Hi

I have about 20 .txt text files that I import regulary from database.
This is easy and done with 2 clicks.

Then what is more time consuming is to import those 20 files to acces.
Is it possible somehow automate this function so that acces would pick
the right import specification and import all the 20 files at the same
time.

The names of files and import specifications are always the same.

Yours,
Sirritys
 
G

Guest

Hi Sirritys,

You should be able to automate such an import. Check out Access Help on the
Transfertext method. However, I am confused on two issues:

1.) You indicate that you regularly import 20 files, and it is easily done
with two clicks, but then you go on to say that importing the 20 files to
Access is time consuming. Did you mean to say that you regularly export 20
files (from another database)?

2.) How can the 20 text files have the same name? Are they in different
folders?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
S

Sirritys

Tom said:
Hi Sirritys,

You should be able to automate such an import. Check out Access Help on the
Transfertext method. However, I am confused on two issues:

1.) You indicate that you regularly import 20 files, and it is easily done
with two clicks, but then you go on to say that importing the 20 files to
Access is time consuming. Did you mean to say that you regularly export 20
files (from another database)?
Click to expand...

This database is not acces database but tables from ERP. So what I do
is EXPORT these tables from ERP to *.txt files and want to import them
quickly to acces. (Sorry mixed import and export, always had taht
problem :)

2.) How can the 20 text files have the same name? Are they in different
folders?
Click to expand...

Sorry, I was unclear here. The files don't have same filename, but each
individual table I export from ERP always has same name when I import
it. So I have ie. Table1.txt, table2.txt etc.
So every time Table1.txt should use acces import specification "Table1
import specification"

What I do now: I choose import --> then choose corresponding import
specification --> click few times next, and don't let acces create
primary key and click finnish.

What I want to do: Same as above but faster =). Meaning not having to
do the procedure for each individual file Table1.txt, Table2.txt etc.


I hope I was clearer this time =)

Yours,
Sirritys
 
G

Guest

Hi Sirritys,
I hope I was clearer this time =)
Click to expand...
Yep, much clearer!

The easiest method to start with will be to use a hard-coded folder. Once
you get that much working, then you can replace a hard-coded path with API
code that allows you to select the appropriate file(s) within a given folder,
or a folder to process all .txt files. Let's work on the easy part first,
where we will use a hard-coded path, and we will process all text files.
Therefore you should only have valid text files, containing data to import,
saved to the folder.

Add a command button to a form. Name it cmdImportFiles. Add the following
code (untested) to the click event of this form. The code is untested but I
think it will work. I have used named arguments for the TransferText method,
as opposed to positional arguments. You can highlight DoCmd.TransferText with
your mouse and press the F1 button to open context sensitive help on this
topic.

The TransferType argument specifies a delimited text file, such as a comma
separated variable (csv) file. Replace YourTableName with the appropriate
name of your table in the database that you want to import the records into.
This value should be enclosed in quotes, as indicated below. Set the
HasFieldNames argument to either True or False, whichever is appropriate for
your text files.



Option Compare Database
Option Explicit

Private Sub cmdImportFiles_Click()
On Error GoTo ProcError

Dim strFolder As String
Dim strFile As String

strFolder = "C:\Temp\" '<---Hard-coded path
' strFolder = CurrentProject.Path & "\" '<---Path is same as .mdb file

strFile = Nz(Dir$(strFolder & "\*.txt"), "")

Do While Len(strFile) > 0
'Debug.Print strFolder & strFile
DoCmd.TransferText TransferType:=acImportDelim, _
SpecificationName:="Table1 import specification", _
TableName:="YourTableName", _
Filename:=strFolder & strFile, HasFieldNames:=True
strFile = Dir$
Loop

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdImportFiles_Click..."
Resume ExitProc
End Sub


Click on Debug > Compile ProjectName, where ProjectName is the name of your
VBA project. Hopefully the code will compile without any errors.

If you want to later expand on this code, here is a good article to serve as
a primer:

http://advisor.com/doc/16279


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
S

Sirritys

Hi,

That code really got me going. With little tweaking it worked great.
Thank you!

I still have 1 problem. How do I substract ".txt" from end of string
(As you prolly noticed, I'm total newbie with VBA). I intend to write
something like this:

SpecificationName:=strFile & " import specification", _

problem in this is that strFile contains something like "item.txt" and
corresponding specification is only "item import specification". So I
need to get rid of that ".txt" =).


Another point. For some reason my excel 97 doesn't work with function
name "cmdImportFiles_Click()" (does nothing when I click), but always
recreates "sub Command0_Click()". So when I use that name everything
works correctly. Wondering why is that ?
 
G

Guest

Hi Sirritys,
I still have 1 problem. How do I substract ".txt" from end of string
Click to expand...

You can use the Replace function (Access 2000 and higher):

SpecificationName:= Replace(strFile, ".txt", "") & " import specification", _


or, you can use the Left function (watch for word wrap):

SpecificationName:= Left$(strFile, InStr(1, strFile, ".txt") - 1) & " import
specification", _


or the Mid function (again, watch for word wrap):

SpecificationName:= Mid$(strFile, 1, InStr(1, strFile, ".txt") - 1) & "
import specification", _


The Left and Mid functions will work in Access 97, however, you would need
to use a custom function instead of the Replace function in Access 97.
Another point. For some reason my excel 97 doesn't work....
Click to expand...

Are we dealing with Excel 97 or Access 97? I don't know the answer to this
problem in any case, at least not without being able to see the actual file.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

import multiple Excel worksheet into a main Access table 1
Is it possible to import only relations data to to database. 1
importing excel spreadsheets 2
Importing multiple csv files 7
Creating import specification doesn't work. What to do? 3
Importing multiple .xls files to tables at once with code 2
Importing Excel files into Access -- without having to change theData Type to "text" one field at a 6
Multiple spreadsheets 4

Top