Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > Courses > Access > Expert > X20 > < X19 | X21 >
Back to Access Expert 20    Comments List
Importing large text files Upload Images   Link   Email  
Bill Allen       
2 years ago
I need to import large (> 2Gb) delimited text files from federal databases. These are only available for download as delimited text files.
I watched your Members Only example from your Import Data video on YouTube but it's intended for fixed-length field text files.

How do I accomplish the same thing using VBA to import my large DELIMITED data files into an Access table?

Many thanks!
Richard Rost              
2 years ago
You'd have to use the same kind of file I/O but process each line as delimited data. I talk about File I/O in a lot more detail in Access Developer 30. You could also break that big file into smaller pieces and then do a regular import of that. Lots of ways to attack the problem.
Kevin Yip       
2 years ago
Assuming your text file has a line break separating each row, you can use the Line Input statement to read each line, E.g.:

Open "C:\myfile.txt" For Input As #1
Do While Not EOF(1)
     Line Input #1, s

The Split() function can put delimited data into an array.  E.g.:

Dim arr as Variant
arr = Split("Doe~John~56~9/11/1970~100 Main St", "~")

After running this, arr(0) equals to "Doe", arr(1) to "John", and so on, all strings.
Scott Axton            
2 years ago
No one has addressed the pink elephant in the room yet so here goes...

If your db is already at 1.5 gb in size you are not going to be able to import > 2 gb in data. You'll only be able to import .5 gb.  Actually less I think because while the limit is 2 gb,  I don't think you can use 2 gb,  due to Access overhead.

Ok so let's create a brand new empty db, You still have some overhead from Access AND you have to tie it all together.

Now for pink elephant's little brother, blue elephant.

A delimited file is not going to have the wasted space like the example of the video where the fixed length file has wasted space of the "spaces".

Depending on what fields you actually keep / import you are going to get huge fast.

The likelihood of needing to up-size your db to SQL Server, in my mind, is looming fast if you are using data sets that large.
Richard Rost              
2 years ago
Good point, Scott. SOME of the fields MAY take less space. For example, a Long Int only takes 4 bytes. If you're storing a number like 3,423,214,231 in a text field, that's 10 bytes right there. So... you may get SOME compression. But yeah, you may definitely need to consider a bigger database - or splitting that file up into multiple tables (i.e. back-end ACCDB files).

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Expert 20.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/22/2024 9:48:54 AM. PLT: 1s