Why are Access files so much larger than Excel files with the same data?

[tomhouy]

I have a rather large Excel file, with approxmately 800,000 rows of data in it. I noticed when I save this as an Excel file, the total file size is about 44 MB. However, if I export it as a CSV file, it shoots up to about 588 MB. Similarly when I import the 44 MB Excel file into an Access database, the database winds up being 566 MB. I even tried the compact and repair option, but it made no difference to the total file size. I thought Access was supposed to be more efficient with large data, am I missing something here?

[June7]

I never thought about this. Don't know if Access is more efficient in terms of storage but it will hold more records than Excel has rows.

[tomhouy]

So if close to 1MM rows is a little over a half Gig in file size, does that mean the maximum number of rows Access can handle is roughly 4MM?

My original intent was to import these Excel files on a daily basis (they're daily performance data on some ad campaigns) so I can have all the data in one large database, but at this rate it seems like I would max out the maximum file size for Access (which I believe is 2GB) in just about 3-4 days.

[June7]

Review this discussion http://answers.microsoft.com/en-us/o...8-1b895c25fa19

It does seem odd that your db is maxing so quickly.

The db has only the one table - no queries, forms, reports, code? Is it a normalized structure?

[John_G]

Hi -

It may well be a result of differences between how MS Access and Excel store data, especially numeric data. My (possibly wrong!) impression that Access pre-allocates fixed amounts of space for numeric data, whereas for text fields it doesn't.

If many of your 140 fields are numeric, and you have say 1000 bytes per record as a result, then 800,000 rows result in 800MB, before anything else.

140 fields in a record is a lot - what is the structure of the records?