[Webinar] Streamline your web hosting managementRegister Today


Inserting large data files into Access database

Posted on 2015-02-10
Medium Priority
Last Modified: 2016-02-11
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?

Question by:Mohamed ElSheikh
  • 6
  • 6
  • 2
  • +3

Expert Comment

by:Matthew Borrusso
ID: 40602313
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.

Author Comment

by:Mohamed ElSheikh
ID: 40602975
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,
LVL 85
ID: 40602995
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.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 53

Expert Comment

by:Gustav Brock
ID: 40603738
What is slow? The unzipping or the import?

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40603792
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.


Author Comment

by:Mohamed ElSheikh
ID: 40604749
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

LVL 53

Assisted Solution

by:Gustav Brock
Gustav Brock earned 2000 total points
ID: 40605089
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.

LVL 85
ID: 40605350
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.
LVL 24

Expert Comment

ID: 40609003

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.



Author Comment

by:Mohamed ElSheikh
ID: 40615249
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,
LVL 53

Expert Comment

by:Gustav Brock
ID: 40615281
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.


Author Comment

by:Mohamed ElSheikh
ID: 40615521
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?

LVL 53

Accepted Solution

Gustav Brock earned 2000 total points
ID: 40616176
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


Author Comment

by:Mohamed ElSheikh
ID: 40753955
LVL 53

Expert Comment

by:Gustav Brock
ID: 40754234
You are welcome!

Did you succeed?


Author Closing Comment

by:Mohamed ElSheikh
ID: 40754451
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
LVL 53

Expert Comment

by:Gustav Brock
ID: 40754457
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.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question