My dataset is too big for Excel to handle efficiently
As I’ve mentioned before, Excel is not a storage place! If you’re having problems processing the sheer volume of your data with it, you might need to look into consolidating monthly compilations into a centralized place.
Assuming you have no DB access and no ETL development expertise, MS Access could be a solution.
Should you go about it
- Always format your Excel columns in the exact same way as their corresponding field in MS Access (e.g. Number to Number, Text to Text, etc.)
2. If you are importing data to Access from a text file (.csv), make sure you’ve eliminated the commas before that. Not doing so will mess the data imported in your Access Table columns
3. If you are importing data from an Excel tab, make sure you’ve cleared the unused range. Not clearing your Excel unused range will enter empty rows in your Access Database Table
(if a larger area than your actual data set is selected when you press Ctrl + Shift + End, then you’ve got unused range. To clear it, just delete the seemingly empty rows by using the “Delete Rows” functionality and hit Save).
4. Do Compact & Repair: MS Access files do have a limit of 2 GB and as you create and delete objects, Access does not give you back the formerly used space. So, if your file size grows any bigger than 2 GB, the whole file is corrupted and the DB will start giving the following error upon data import:
Cannot open database”, It may not be a database that your application recognizes or the file may be corrupt
- Last but not least, do not use spaces when naming your tables and fields. Using a single word table name or field will mean that when/if you are writing SQL code or a Access DB macro later, you won’t have to enclose your table names and field names in square brackets , which will ultimately mean that your code will be easier to read and follow