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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

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,915
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,114,019
Messages
5,545,531
Members
410,689
Latest member
ConfuzzledThomas
Top