Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How to import large text file into access db?

Posted on 1999-08-09
Medium Priority
Last Modified: 2010-04-30
I have to import large text file(about 10 to 15 mb) into access database. The file is in fixed width format.
Each line contains one record. As it is not a simple "Just dump into" import but requires some intermediate calculations and updation into 4 different tables.
I have wriiten a project for this but it takes lot of time to finish the process and seems that computer is hanged up.
If anybody know any better strategy or a sleek way to do this then please let know.
I have asked this thing before also but didn't get any better suggestions.
Thanx a lot in advance.
Question by:khatrij
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 18

Expert Comment

ID: 1530545
Use a VB program to read the text file line by line using

open "file" for input as #1
while not(eof(1))
Input #1,sLine

''  parse data and save in Access .mdb


DoEvents means you can multitask in windows without locking the PC


Accepted Solution

vindevogel earned 400 total points
ID: 1530546
This answer is valid if your fixed width of the columns is always the same, otherwise, it's garbage.

1)  Create an Access MDB.
2)  Link your flat file into your DB via File - Get External Data - Link File
3)  Choose Text File in the options and select your file.
4)  Say it's fixed width and specify the width for each of the fields in the Link Table Wizard.  You'll then have a table (your flat file) which you can query and so.
5)  If your functions are a little to complex, add all your functions in a Module (program them there)
6)  Create the 4 export tables you need (or if necessary, create them in another DB and link them too)
7)  Create 4 append queries to update your 4 tables.

I've done this with a flat file of 2000 lines, containing 9 fields per row.  There are extracted into 2 tables, but with a join on another external file (Excel file).  The update queries take about 2 seconds to complete.

That I find fast, considering I'm using TXT files and Excel files to get my data into the DB.

Give it a try ...


Author Comment

ID: 1530547
I have to write it thru vb.
thanx for yer comments.

Expert Comment

ID: 6871951

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.
Thank you,
Moderator @ Experts Exchange

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

715 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