MS Access Macro import multiple txt files? ?

Question

I have a process where I receive several txt fies with different naming conventions. Generally I import these into a table one at a time and sort my data as needed. I know a little bit about Macros but not a lot and no nothing about VBA. I want to create a macro that will look into a specific directory and import all the files in that directory with the .txt extension. But, it seems you can only define a specific file name and import only 1 file that is predefined. I was hoping I could define the directory path and then use some sort of wildcard that says import everything that has .txt.

Here is what my Macro looks like

TRansfertext: import delimited
Spec Name: this is filled out right
Table Name: My table name
File Name: (This is where I want to define a directory to import and not just one file. I want it to import all the files in the directory)


Any help please. There simple dilimited txt files I just tired of importing them one at a time.

Answer 1

This (I believe) is impossible with pure Access Macro's - I hope someone will say I'm wrong, I've tried to do this myself. The ONLY way I know to achieve this is to use VBA to, locate the files, read all the files, open the text files, read the data, format the data into the table structure and write the records away.

Answer 2

JA12 is correct. You can get the names of all the text files in the directory and import them one at a time - with VBA. You can't do it in a macro.



The Dir function returns the name of the first file that matches the argument, so Dir("\yourdirectory\*.txt") will return the name of the first text file.



Making a variable equal to that file gives you a file name to open:



s = Dir("\yourdirectory\*.txt")



Then you import all the files in a loop:



While Not s = vbNullString

'import your file here - you write the code

s = Dir() ' get the next file name

Wend



That will keep importing files until Dir() returns a blank because it ran out of txt files.



HTH

Answer 3

You must say (select the the option use headers) to Access. To have fields in correct datatype, all of the rows you´re importing must have the right content in the first import, i.e., all date fields should have dates, all numeric fields should have numbers, etc. Doing like that, you got your field specification and can format it as you want to export it later.