Importing large files

jhendryx04

New Member
Joined
Jun 3, 2009
Messages
1
I have a text file that is 2.7 GB in size and contains about 4 million rows of data. This cannot be opened in txt format and I need to manipulate the data. I am attempting to upload this data into Access in order to make alterations and run queries. When trying to import, I get a response that there is no data in the file. I need to be able to import but I don't know what else to do.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
Unfortunately you'll probably have to read this one line at a time using

Code:
Dim strPath As String
Dim strInput As String
Dim varTemp As Variant
Dim rst As DAO.Recordset
 
strPath = "C:\YourFolder\YourFileName.txt"
Set rst = CurrentDb.OpenRecordset("YourTableNameHere")
 
Open strPath For Input As #1
Do Until EOF(1)
LineInput #1, strInput
 
varTemp = Split(strInput, ",")
 
With rst
   .AddNew
   !Fields(0) = varTemp(0)
   !Fields(1) = varTemp(1)
   !Fields(2) = varTemp(2)
   !Fields(3) = varTemp(3)
   !Fields(4) = varTemp(4)
   !Fields(5) = varTemp(5)
  .Update
Loop
rst.Close
Close #1
Set rst = Nothing

Something like that. Here's more of a reference on this.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
How exactly are you trying to import the file?

When do you get the message?

If you are using File>Get external data... do you get as far as the Text Import Wizard?
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Another potential issue; the Access file size limit is 2 Gb. If you try to exceed that you will have problems.

Denis
 

Watch MrExcel Video

Forum statistics

Threads
1,108,932
Messages
5,525,690
Members
409,660
Latest member
1817538628

This Week's Hot Topics

Top