Text Files & Access Question

[RHONDAK72]

We use a third part data system to export data into a csv file. We then import it into a MS Access database. However, the size of the Access database seems much larger than it should be compared to the amount of data imported into it. Is it possible that there could be data imbedded in a text file that would cause the size of the database to increase? (I wouldn't think that is possible for a text file, but not sure...)

 

[Joe4]

Note that Access databases have some overhead, so the size of an Access database will be larger than a comparable data file.

But here are a few things to look at, in keeping the size down to a minimum:

1. Look at the tables you are importing the data into. Are the data types and sizes of the fields set-up efficiently (i.e. you are picking the most efficient data type and size for the job?). For example, if importing a column of zeros and ones, you wouldn't need a Long Integer. Or if imported 2 character state codes, you don't need your Text field to be 255 characters long. Choose the smallest data type and size for the job.

2. Do you compact and repair your database regularly? Deleting old data does not reduce database size until the database is compacted.

 

[HiTechCoach]

As Joe4 pointed out, the key is compacting.

Best Practice for importing:
1) make a backup of the databse
2) performinport
3) make backup of databse
4) run compact & repair.


TIPS:
1) Always make a backup before running a compact and repair.

2) After deleting or adding (importing) lots of records you need to perform database maintenance to clean up and optimized the dataabse. This is done by running a compact and repair.

 

[RHONDAK72]

Thanks for the tips. We are actually using a newly created Access database. One interesting thing my co-worker noted was that the text file was larger than even Excel after importing the data to Excel. The csv file is 321,458KB and the same data in the excel file is 124,684KB. Why would the text file be bigger than the Excel file? Would you agree that perhaps the delimiter symbols within the text file would cause that? Can you think of any other reason the text file would be larger?

 

[Joe4]

Lots of trailing blank spaces or blank rows at the bottom could cause that (as Excel would drop those).
If you see a lot of "white space" in your CSV file, that could be what is happening.

 

[SpillerBD]

XLSX files are actually compressed (ZIP) folders and will often appear smaller. I think the Access files are one of the few "new" files that are not zipped.

 

[xenou]

In addition to the points already made, depending on the type of data it may be stored differently. For example, a number in Excel may be stored as an Integer or Doubles, but a number in a CSV file would be stored as Text, including commas, periods, slashes, and dollar signs - all of which could take up more storage for numbers or dates.