Importing Multiple Text Files

[sathishkm]

Hi All,

Is there a way to import multiple text file in MS access database. I have more than 50 - 60 file every time I need to import in access database.

[#2]

Do the files always have the same names? Are you just importing the latest version of each text file?
If so, then you can save the file names in a table and loop through the files in turn to import them
OR link the text files instead of importing them - then they will always be up to date

[#3]

@sathishkm 

The short answer, of course, is yes you can do that.

The long answer is that it will require VBA and functions that can enumerate files in a folder (preferably using FileScripting, although old school Dir is feasible). Then, once you've created a list of those files in a temp table, you can loop through the list importing each one in turn.

However, it also depends on WHY you need to do this. If the files are constantly being replaced by an external process, such as output from a testing station, or orders from an online store application, then you can't just link to them and use them that way, not easily at any rate.

I believe that at least two of the projects I completed for clients over the years would be similar to this, but each involves multiple, significant VBA functions and subs and would not be something easily passed through a forum.

So, define the nature of the requirement a little more fully and we can maybe offer more ideas.

[#4]

Can you post the VBA code

[#5]

It's in half a dozen procedures scattered over different modules and it is highly specific to one environment. It took a couple of weeks to work it all out and implement the supporting form.

I can make it available as is, but it'll be up to you to implement the concepts.

[#6]

Besides, you haven't yet responded to the questions Colin asked, nor my request for details. It's a considerable amount of work to go to on the possibility it would be appropriate.

[#7]

Files names are different.

example :

abc.txt
bs.txt
ddn.txt
73m.txt
hh.txt

Likewise the file name will be.

[#8]

Everyday I will get lot of text file.
Example:
bs.txt
ddn.txt
hh.txt
73m.txt

likewise so on. My task is to import all these tiles in MS Access database. Where I can work on these files. If I have 50 files today. I have to manual click the each text file and import them. It's very time consuming job.

[#9]

@sathishkm 

Thank you. As I noted, I can probably provide some code, but you would need to adapt it to your situation yourself. It's on a back up computer, so I'll have to start it and find the appropriate files. Later today.

[#10]

Use a while loop with the DIR function to get the filenames, then inside your loop perform your import. I cover the DIR function in: https://599cd.com/ACD31 - and I'm sure there's a TON of different free resources available online. Google it.

[#11]

@George_Hepworth After I finally located a back up of the relational database application to which I referred earlier (it was in the wrong subfolder of a backup drive), I realized that it is even more complex than I'd recalled, so much so that trying to provide it as a template seems to be hopeless overkill. I'm sorry I got your hopes up. It would take hours to even cut it down to an example that might be usable.

That said, I didn't just want to drop it. Attached you'll find the core module that explores one or more folders and any subfolders in them and returns a list of files found.

As you can see, it's not a simple matter and I don't have time to put it into a more generic form.

I know that's of little help, but it's about the extent of what I can offer. Perhaps someone has an already simplified procedure to share.

[#12]

@sathishkm 

I know I am late to the party with this, but I thought I'd add my solution for anyone else with the same "problem" in the hope it will help them.

You will need to use the command prompt and navigate to the folder containing your data files.

At the prompt, type the following (assuming the extension is "csv" - change this to "txt" if needed):

copy /b *.csv _allfiles.csv

This will copy (or merge) all the files with the defined extension into one file. The underscore at the start of the filename will put it at the top of the list to make it easier to find.

This does rely on all the files having the same column structure, and you may have to go through the generated file to remove any column headers.

[#13]

Thank you