• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

Inserting large data files into Access database

Hi Experts,

I receive 5 zip files in a daily basis. each zip file contains other zip files.
When we extract the last zip files we will get 50 text files
these text files contain millions of records, I want to inload these records into access database, then move the text file to history folder.

What is the fastest way to extract the text files from the zip files in one folder, loop in each file, and import the data into access database using VBA.
***speed is important***

Any advice?

Mohamed ElSheikh
Mohamed ElSheikh
  • 6
  • 6
  • 2
  • +3
2 Solutions
Matthew BorrussoCommented:
I know there will be a better way to do this.

I am going real low tech here.

Write a script that uses a command line zip/unzip utility.
use it to decompress down to the text files in a specified directory.
Then use something like http://support.microsoft.com/kb/230265 or http://www.databasedev.co.uk/text-import-macro.html

Supposedly, you can use some of the MySQL tools to pump text into access.

Again, low tech, I am sure one of the experts can give you a much better way to accomplish.
Mohamed ElSheikhAuthor Commented:
Thank you Matthew,
I am currently using code like what you mentioned above, but very slow to inload all of these files and taking hours.
could you please give me more information or instructions to use the MySQL tools?
can we use this tool using VBA?
what software do I need to install? wamp? or what
kind regards,
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Text file handling in Access is somewhat limited, so you're not going to get much in the way of performance.

You might try using TransferText AFTER unzipping the files. Move the text files into an Access table, and then process them in that table. Assuming the import works properly (i.e. the resultant table is properly formed, with valid rows/columns) this would likely provide the best performance.

However, if performance is the major factor in this, then you might consider a different platform, like .NET.
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Gustav BrockCIOCommented:
What is slow? The unzipping or the import?

Jeffrey CoachmanMIS LiasonCommented:
Just some notes here.
Is this system meant to be temporary?
Because ...opening zip files, extracting text files and importing these text files to Access is not a very efficient way of updating a database.
There is just too much to go wrong...:

What if the zip file is corrupted?
What if a text file is not in the correct format?
Suppose a text file contains invalid data?
What if the code fails?
How are you ensuring that the code runs every day? (Suppose a day is missed?, suppose the code is inadvertently triggered twice in one day?)
How would you detect for these issues (and the many others that might occur)
How would you ...or the code, react if an error occurred?
How valuable is this data if it is lost, or inconsistent?

Complex systems like this work great when all goes well.
The real problems occur when things don't go as smoothly as you had hoped. (the above issues...)
So in the above scenarios, your code would need robust: Error Handling, Validation, and possibly Roll-Backs

< you can use some of the MySQL tools to pump text into access>
...<<can we use this tool using VBA?>
From what I know of MYSQL, these tools will mostly pull data ino *MySQL databases*, not into Access directly...
Being MySQL tools, they are probably not going to be easily automated with Access VBA.
(Perhaps you may be able do something with .net ...but I am not sure...)

The other issue is your requirement for "speed".
While "hours" certainly seems like a long time, I can’t see a complex/robust system like this ever being "Fast"

So while you may get help here in doing this ...your long term goal should look into having a more efficient way of getting this data into Access.

Mohamed ElSheikhAuthor Commented:
Hi Scott McDaniel, Jeffrey Coachman
thank you for your try to help
actually the company does not have budget to buy SQL server or any other tools. I wish if I can use SSIS to import data, into SQL server, but for limited resources I have to use VBA or any other free resources
What i understood that MySQL is free, and we can setup the server by installing WAMP. am I right? do you know if the MySQL importing tool is free or not?
thank you all for your help and valuable information

I mean importing data

Gustav BrockCIOCommented:
OK. Then - if you stick with MySQL - is the command LOAD DATA INFILE:

From a test I did in 2006:
When inserting records by a pass-through query from Access, I've found that reading the records off a text file using


into an empty non-indexed table runs at about 50,000 records per second even using very modest hardware.
This is way faster than any normal "Insert Into ... " I've tried.

To run MySQL, you wouldn't need a WAMP setup; that's intended for web usage. MySQL did run on even Windows NT, don't about the newer versions but Windows 7 should be fine - if you wish to refrain from Linux servers.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
actually the company does not have budget to buy SQL server or any other tools
SQL Server Express is free, and comes with Management Studio to allow you to run queries, create databases/tables, etc.

But as gustav has indicated, you can do this in Access. if you determine where the bottleneck is.

Does the slowdown occur during the unzip process, or during the import process?

If it's the unzip process, then I doubt there's much you can do about that.

If it's the import process, then gustav's suggestion may be the way to go - but you'd have to test to determine that.

you can use SQL directly to import data, for this purpose a SCHEMA.INI file can be created which describes the columns to import.

A good description of this theme can be found here:
Working with external text files in MS Access

In SQL Server this can also be done using OPENROWSET.


Mohamed ElSheikhAuthor Commented:
Thank you all for your help.
I am trying to use MySQL "Load Data Local Infile" and really is is very fast
But i have an issue, when i write the T-SQL in SQL tab i have no issue, but when i write same t-sql into stored procedure, and compile it i got an error message "LOAD DATA is not allowed in stored procedures"

I also install the MySQL ODBC drive in order to link the MySQL table to MS Access
I imported one text file (1.7GB) and it took 5 minutes which is great, but when i create access query to git gust top 100 records i got an error message "Out of memory"

any advise for both issues? load the data is really very fast in SQL tab, but not supported in procedure or manipulating the data in access

please advise

kind regards,
Gustav BrockCIOCommented:
A note: You mention T-SQL but I believe that is the specific SQL dialect for SQL Server, not for MySQL.

But do you really need a stored procedure? Can't you write plain SQL and feed it to the server with one or more pass-through queries?
Also, to retrieve records, I would use pass-through queries and the specific SQL dialect of MySQL.
Finally, check that you haven't created fields of Big Integers or other data types that Access doesn't understand.

Mohamed ElSheikhAuthor Commented:
Thank you Gustav

I am using the MySQL syntax and I have no issue to run the code from phpMyAdmin. the only thing i want to schedule importing data to run automatically and this is why I want to create a procedure

I am able to query the table for small amount of data (300k records), but i am not able to query the big table (7.5 million) using where conditions

what do you mean by pass-through query?

Gustav BrockCIOCommented:
That's a query with server specific SQL (not validated by Access) which you send via the ODBC driver directly to the server; it then runs on the server and - for select queries - return a read-only result set.

So you can build your query in phpMyAdmin and copy/paste to Access. However, if it won't in phpMyAdmin, neither it will in Access.

Here's an obtuse guide:
Create Pass-Through Query

Mohamed ElSheikhAuthor Commented:
Gustav BrockCIOCommented:
You are welcome!

Did you succeed?

Mohamed ElSheikhAuthor Commented:
Hi Gustav Brock
the command you mentioned above "LOAD DATA INFILE" MySQL is very helpful and i can load the 4GB of text files into indexed tables in 5 to 10 minutes and without index in just 3 minutes which is great
the only issue I have right now is unzipping files is slow. any way can speed the extracting by vba?
last point how do you install MySQL without wamp?
thanks again for your help
Gustav BrockCIOCommented:
The download for MySQL is here:


As for the unzipping, I don't know how you do it right now, but numerous tools - all claiming to be fast - is available. Like this:


I guess you just have to check out some of these.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 6
  • 6
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now