Importing large txt file into Access 2010

[Jimbo]

I am attempting to import an extremely large text file (12 million records) into Access 2010 using the Import Text Wizard. The text file is comma delimitated and has 26 column's. The text file is approximately 1.7GB.



The problem is that the Import Text Wizard say "Not Responding", and Access is froze. I thought that maybe with 12 million records it is just taking a long to to import. Is that likely my problem?

I've received no other error messages.

Thank you in advance!!!

Edit: it has been froze for about an hour and a half.

[June7]

Access file size limit is 2GB. Might just be too much for Access to handle. Try a limited import of maybe 100,000 records and see what happens.

[ajetrumpet]

I just ran a test on this by streaming 1.7GB of data FROM an access module into a text file. The process took only 3 and a half minutes. here's the code I used:

Sub f()

Dim i As Double

Open "c:\myfile.txt" For Append As #1

For i = 1 To 12000000

    Write #1, "String,String,String,String,String,String,String,String,String,String,String,String,String," & _
              "String,String,String,String,String,String,String,String,String,String,String,String,String," & _
              "String,String,String,String,String,String,String,String,String,String,String,String,String"

    i = i + 1

Next i

Close #1

End Sub

more than likely, you ARE overwhelming the program. Especially if you are using a wizard. Try streaming the data on in with vba, just as I did above. My code produced 12 million comma-delimited records but that's all it did. With your attempt you would obviously have to import it all into one table with a recordset, OR make a bunch of small tables and issue append operations.

Give it a try. I think your RAM likes I/O a lot more than programs trying the equivalent. After all, my code above was run on a refurbished laptop with a 1.5gbs processor and 512 ram. That's pretty sad specs!

[Jimbo]

The process just completed, but I only got approximately 1.3 million records. Supposed to have around 12 million.

Does it makes sense that the import process would complete and only import a portion of the records?

I did have some import errors, but only about 400 or so.

Should I try another program?

Just so you know, the text file contain data related to accounts. Ultimately, I want to be able to sort the entire population (12 million records) based upon certain criteria, do a mail merge and send out hard copy letters to the appropriate "customers".

I did just purchase Access 2010 today, but would be willing to try another program if there's one out there that's inexpensive.

Thank you.

[Jimbo]

Adam - I'm not even sure where to use the code you wrote or how to change it to import from the text file.

Sorry to bother, but can you help with that?

[ajetrumpet]

yep. I can!

Post one record up here, just as it appears in your text file, and I'll run a test on it to import it into one access table and tell you how long it takes on this "piece of junk" machine I have. then you can halve that number and use the code to do it yourself.

Hopefully it will work on a test run!

(but actually now that I think about it, notepad won't open files that big so you might have to use a program like ultraedit or something. sorry bout that!)

[Jimbo]

Hey Adam - found another program for opening large text files and found that my .txt file does not contain the appropriate number of records (i.e., 12 million). Got the same number of records as when I was in Access - approximately 1.3 million.

As such, I'm going to request another file from the publisher.

Thank you very much for your help!