Large .txt file to table issue

[PureSlate]

Hello,

I've been trying to import a large (>2GB) text file into access lately.

My main problem has been that I either get import errors for everything after 400k~ rows, or I get #Num! errors when linking on around half the rows imported, still missing many rows.

The file is fixed width, and I have two questions based around it:

1. Is there any way to import such a large file without splitting it up?
2. If I need to split it up, are there any programs that can handle such a large file? (notepad++, pfe, and the microsoft programs have failed me.)

Thanks for your time.

 

[dhookom]

With that size of a file, I would use SQL Server or SQL Server Express (free).

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

Try linking the original text file to your Access database. Then query the records into your destination table in batches. For example:

Select top 300000 * into AccessTable
from yourtable;

Once you've imported the first set of records, you'll need to setup an append table to bring in the subsequent set of records. An example might be

Insert into AccessTable
(fieldname1, fieldname2, fieldname3, etc)
Select yourfield1, yourfield2, yourfield3, etc
from yourtable
where rowed between 300001 and 700000

I've used something similar before, but there are dependencies on making it work this way. You might have to bring the data in multiple tables and then combine.

As for utilities you mentioned, try using ASAP Utilities to split the text file so you can load the data in smaller sets.

 

[Golom]

You may also note that Access has a total 2GB size limit and a single table may not exceed 1 GB. Text file size does not necessarily equate to DBMS size but it is possible that you are approaching or exceeding the Access limit.

Look at Duane's suggestion. I believe that SQL Server Express has somewhat larger limits than Access.