Access vs Big CSV Files (1 Viewer)

[kimosavi]

Been working with Access for a long time, but it's the first time I have seen Access bloat so much.

I have a 700MB CSV file, all text columns. This file contains about 2 million rows and 30 columns.

After I import the file (using the Wizard) the final size of the DB, after compact and repair, is 1.8GB (about 2.5 times bigger).

My first thought was that Access was defining each text field size at 255, so I check the max len of each column and changed the field size. No Change.

I understand that the data is now in a structural format (not in a raw data file) and that takes space to archive, but 2.5 times!? something might be off.

any suggestions?

Thanks!

 

[DCrake]

Any reason for importing it when you could have linked it?

 

[kimosavi]

Yes. This might consist of multiple Scenarioes. but the main one is this:

Building a tool that Analyses a data set. I have data/code splitted.

the user will import into the data DB and a link it into the code DB.

When small you can import in the set directly, but sometimes the data set consists of small tables, so we need to combine them into one first, before linking to the code DB.

but my main question, regardless of all the possibilities, is why the growth is so explonential, 2.5 times more (at least in my case), when you are storing the same text data, but in different structure?

 

[Dennisk]

one thing Access may have done is to automatically apply indexes.

Don't forget a string of say 10 characters requires 10 characters plus an integer for the length.

 

[kimosavi]

How Can I check if an index was automatically generated?

In the design view of the table?

 

[Dennisk]

go to tools, options, and somewhere amongst the myrid of options is one for automatcally creating indexes on fields that are integer or fields that have 'code', or 'ID' in the name.

 

[gemma-the-husky]

its not just indexes, but access also isn't just a text file - access must internally manage the records within the notional tables it contains, and it must do this with some very advanced pointer chain management. - it must "manage" each table with some notional b-tree for instance, for each index

the size is just what it is - but 2m text records is getting pretty big.

eg 2m times 30 columns is 60million individual data elements - so to get 700Mb only averages at 10 bytes per item, without any storage overhead at all.

Try to get your functionality working in Access, but maybe this is an instance where you might need to be looking at upsizing to SQL Server.