Importing large files

[jhendryx04]

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.

 

[boblarson]

Unfortunately you'll probably have to read this one line at a time using

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]

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]

Another potential issue; the Access file size limit is 2 Gb. If you try to exceed that you will have problems.

Denis