Automated Text File Import

[Achez]

Hello, All:

I'm designing a very simple database that will import data (all text
files, all different names, each text file using the same column
headings, one common directory) basically for reference purposes. I
found a posting (titled "import multiple text files into Access
(different file names)" from February 2005...see link at the end) using
macros and a hidden form. That looks like it would work to import all
files from a directory regardless of title. What would one do if they
wanted to tell the database which file to import? In the case that I'm
referring to above, a text box on a hidden form holds the information
needed by the macro (*.txt) to import the right file. Would it then
make sense to adjust the macro to allow for user input (e.g. the file
name to import), and use a control button to start the macro?

The text file that will be imported is a generated file from a hardware
programming utility. The title will reflect the order number
associated with the particular piece of hardware. Usually, it will be
a 5 or 6 digit number, although sometimes it will have a letter or an
underscore (I'm not sure if this makes any difference in designing the
VB code or macro). Each file size will be different, and each file
will contain one or more lines of information, but each line will have
data that reflects common column headings. The values are separated by
commas. The end user is assumed to have very little if any database
experience, and would most likely have trouble using Access's import
wizard.

Any help is greatly appreciated.

-Achez

http://groups.google.ca/group/micro...ile+into+access&rnum=3&hl=en#aa879d1dff2b2b1a

 

[Guest]

Achez:
Your users certainly would have trouble using the wizard because it doesn't
work. Microsoft broke it with their latest service pack, and it is virtually
impossible to get the hotfix (at least if you're in the UK)
Although they broke it, and it's not exactly an esoteric requirement, if
you want the fix you have to phone the 'support' people.

This costs 17p per minute in the UK, and so far I have never been able to
get through.

In the olden days you could have used the common dialog control, with
prefilled directory path and filters to only show *.txt files, but Micrrosoft
no longer allow you to use this because they couldn't get it to work with
different versions. (Yes I know you only have the one version, but you still
can't have it). You can always use the API directly if you want to while away
an hour or so.

What you can do is add excel as a reference, and use

Excel.Application.GetOpenFilename

To allow users to select the file. (I'll post a bit of code as a starter if
you like)

If you also yourself, use the advanced text import wizard to define an
import spec

and then in your code use

docmd.TransferText using your presaved spec name.

All should go quite smoothly (on the assumption that the text import does
work they way it is supposed to)


Dav id

 

[John Nurick]

Hi Achez,

The following, which was originally posted here by Joe Fallon, gives a
basic procedure for importing all the files in a folder.

----Begin quote
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


This sample code goes in the Click eventof a button named
"btnImportAllFiles" on a form. You need to manually imprt one of the
files and use the wizard to set all the properties in the spec.
Do *not* hit Finish! Click Advanced, Save As and give the spec a name.
----End quote

 

[Achez]

John and David...thank you both. For anyone else reading this as
reference, both methods work perfectly!

Kindest regards,
Achez