0

I have a CSV file that I imported into a table in Microsoft Access 2016, there is only one table in the .accdb file. On disk, the CSV file only takes up 66 MB, while the .accdb file takes up 105 MB. I've run a compact and repair operation on the database with seemingly no effect on size. I've tried to tweaking the field datatypes in the database to be the minimum needed, ie. Integer not "Long Integer" and "Short Text" not "Long Text", but this doesn't seem to help. Furthermore, doing a ZIP compression on the CSV file produces a file of only 18 MB, which makes the large size of the database particularly galling.

Why is Access so inefficient when storing this data? What could be done to decrease the size of the database?

The first few rows of the CSV file look like this:

0,,299-8,08-011-03,22,6,5,80.0,51.16,-0.88,-57.2,-42.91,-63.99,9.07,124.52,-11.22
1,,299-8,08-011-03,22,6,5,80.15,50.01,-0.96,-51.84,-43.7,-66.7,9.12,123.79,-11.21
2,,299-8,08-011-03,22,6,5,80.3,48.83,-1.04,-59.14,-44.03,-69.22,9.16,123.13,-11.18
3,,299-8,08-011-03,22,6,5,80.45,47.97,-1.06,-54.28,-48.42,-71.65,9.24,121.83,-11.19
4,,299-8,08-011-03,22,6,5,80.6,47.03,-1.16,-56.69,-44.96,-74.12,9.27,121.1,-11.09
5,,299-8,08-011-03,22,6,5,80.75,46.14,-1.16,-65.44,-46.2,-76.52,9.36,120.76,-11.08

My table design in the database looks like this:

Table design in database

  • Are you sure the sizes are in GB and not MB? Access should have a limit of 2 GB per file. – techturtle Sep 22 '17 at 15:05
  • @techturtle You're right, I meant to say MB, not GB. I corrected the question to reflect this. – DakotaD Sep 22 '17 at 15:43
3

The CSV file stores only text, nothing else. The database table includes metadata about the fields, rows, columns and the database itself. The Data Types in the picture you attached are evidence of this. On top of all this, Access is not the most efficient database engine, and does especially poorly with large amounts of data.

So you know it's not just you or something you did wrong, here are some sample files I put together:

enter image description here

Both files have about 4000 rows of data. The larger file has 10 fields, including different number types and lengths of text. The smaller has only a single text field (plus PK).

  • 1
    +1. Extra features come at a cost. Access is optimized for efficient data exploration, not efficient storage usage. – gronostaj Sep 22 '17 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.