Question Text File Import larger than 3 gig

[Dr_Foxy]

Hi,

This is my first post so please bare with me .

I need to import a text file into access 07, the problem is as i understand it access has a db limit of 2gig. The text file i am trying to import is 3.22 gig so surely this will exceed the DB limit of 2 gig when imported.

The text file is delimited by | symbols - i do not need all of the data (around 29 million lines) but have no way of reducing the file size because cant get a text editor with sufficient capabilities to open it (except thegun.exe which doesnt have functionality to edit)

Is there a simple way i can get round this problem? e.g. import half the records into one DB and half into another ?

 

[namliam]

Welcome to the forum...

If you dont want all the data you can import only the data/columns you need using the Import wizard.

Is there anyway you can reduce the data while importing? Like:
- Replicating data, using normalization
- Repetative data, using lookups

29 million lines sounds like this must come from some other database. Any chance you could get (select) priveliges on that database?

 

[Dr_Foxy]

Thanks for the quick response

each data line is unique , i need all columns but not all rows e.g..

date | time | person | = cols , if person = we/er/ty/ui i only want to import we and ui rows - make sense ?

Trouble is because its over 2gig access import wizard wont take the file and says File:X does not contain data

its a back up text file from a db i dont have im afraid

 

[namliam]

well in access date and time are to be combined... As there is not a seperate date or time field, its always a date/time field.

If you only want part of the text file, it depends on what % that part is. You can still load it if the total is <2 gig that is to be loaded. Offcourse there is also the issue of having 2 different formats, the size of the text file will not equal the size of the database.

Two options
1)
Link the text file to the database using a linked table.
Use a (create table or append) query to fetch the records you want.

2)
Write some VBA code to go thru the file line by line and do simular filtering.

In general I would expect #1 to be the best way.

Also atleast the dates are going to be repetative, you may be able to get some reduction in space required by reducing the repetative data to linked tables... But I am not sure as date/times are basicaly the same as any related fields...

 

[Dennisk]

Although the upper limit is 2Gb. You can link any number of tables.

What i would do is
develop a sub to read the whole file and maintain a line count.

develop a sub to read x number of records and write them to another file.
then a sub to read from x+1 to end of file again writing out the records to another table.

you should now have a number of smaller files.

Import each one into its own db.

Link all the tables in one main db.

develop an union quiery to view all the records as if they where one file.

 

[lhomme]

Hi, I have a similar problem: I have a massive data dump split into 12 CSV files, 11 of which are over 4GB in size (>100M rows total). I cannot access the source data or request another data dump.

I have Access 2003 & 2007 available. I also have Excel 2003 & 2007. I am running XP.

When I try to link the CSV files to Access (in both versions) I get the following error: "Import Text Wizard - Bad File Name or Number". The 1 of the 12 files that is under 2GB imported fine so I don't think its anything to do with the file name or data format. I could easily trim some of the rows and columns once I get it into Access and shrink and aggregate the useful data, but I need to get it into Access first.

What is my problem? Thanks!

p.s. I don't understand what a "sub" is.

 

[RuralGuy]

I believe the previous posters were suggesting you link to the files, not import them. File>Get External Data>Link Tables... (in ac2003)

 

[DCrake]

I have this VB project that splits files into smaller chunks and rejoins them if you want it to.

I have quickly converted it to Access. although not fully debugged and compiled it will enable the user to nominate a file and either tell it to split into into nn smaller files or split by size.

PS
If anyone wants the VB source code I can send you a link to the project.

David

 

[lhomme]

RuralGuy: I am aware of the difference between Importing and Linking tables. The "Bad File Name or Number" is what I get when trying either. Do you recognize the error?

DCrake: Thanks for the file. Unfortunately, I cannot get it to split a test 4GB file into anything larger than 200MB chunks, when specifying "File Count: 2". Setting the split size at the max of 3000 results in 100+ files. Do you know what's wrong?

Thanks for the quick replies!

 

[DCrake]

From what I am aware if you tell it you are splitting it by number of files and leave the size blank it divides the total file size by the number you provided.

 

[lhomme]

That may be a bug then, because imputing "2" or "3" results in lots of small files. (~200MB and ~140MB) respectively.