Solved

Inserting large data files into Access database

Posted on 2015-02-10
17
158 Views
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?

Mohamed
0
Comment
Question by:Mohamed ElSheikh
  • 6
  • 6
  • 2
  • +3
17 Comments
 
LVL 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.
0
 

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,
Mohamed
0
 
LVL 84
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.
0
 
LVL 49

Expert Comment

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

/gustav
0
 
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?
...etc.

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.

JeffCoachman
0
 

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

Gustav,
I mean importing data

thanks
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 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

  LOAD DATA INFILE
    '../data/test.txt'
  INTO TABLE
    tempload

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.
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

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.

/gustav
0
 
LVL 84
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.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40609003
Hi,

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.

Cheers,

Christian
0
 

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,
Mohamed
0
 
LVL 49

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.

/gustav
0
 

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?

Thanks
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 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

/gustav
0
 

Author Comment

by:Mohamed ElSheikh
ID: 40753955
Thanks
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40754234
You are welcome!

Did you succeed?

/gustav
0
 

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
Mohamed
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40754457
The download for MySQL is here:

http://dev.mysql.com/downloads/mysql/

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:

http://www.altools.com/ALTools/ALZip/Features/Speed-Extractor.aspx

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

/gustav
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now