Excel to Access

[Mohanss82]

I have an excel dump with 150 columns and 50K rows generated from system every day from which I just require 30 columns every day. Similarly I have a foreign key in this file to be compared for relationship with 3 more files with 50K rows. I wanted to create a relationship for all 4 files. I tried applying power pivot on them so that I can refresh and get the required details but ended up file size beng huge and frequently not responding. I am suppose to prepare reports and dashboards to my management frequently. Please advise whether these tables can be loaded in to Access database to make my life easier ?



Attached is the layout. Please advise.

[RuralGuy]

Did you mark this thread as Solved in error?

[Mohanss82]

Did you mark this thread as Solved in error?

I did not mark this as solved and not sure how it happened? Still running with the issue and looking for solutions...

[RuralGuy]

I "unsolved" the thread for you.

[Micron]

You haven't said if
a) the spreadsheet files are the same ones every day (possibly with new data), or
b) they are over-written (which is basically the same as (a), or
c) they are additional files
d) the same fields are required each day

There are several options you could research. Which one is best, or which ones are best in combination, depends on the business need:
- from Access, link to the static files. Each day, run the same Access queries for your reporting, pulling in only the fields you require. The query can perform the joins.
- import them as tables each day using the Get Data tool on the Ribbon. You've asked if they can be imported. Yes, but would you over-write or add to the Access tables? There is a limit of 255 table rows (so you're OK there), but the data volume is 2 GB for the whole database, including overhead. I believe the table limit is the same. Linking provides you with the ability to create db relationships, since that seems to be one of your goals.
- import or link to them each day with code that uses the msoFileDialogFolderPicker or msoFileDialogFilePicker. Use queries for linking/reporting as mentioned.
- use Automation to pull in data from Excel. More complicated, and IMHO, there would have to be a good reason for not just linking.

Frequent replacement of Access tables can promote db corruption, so that is something to consider. If you have to, you can also incorporate the data structure into multiple back end (BE) databases, which brings up the point that your db should be split into BE and FE portions.