Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
combine data from multiple spreadsheets into a single database
- Thread starter CarolCook
- Start date
[ol][li]Aggregate the data into an Access database. This will allow you to:[/li][ul][li]Write a macro to import all the spreadsheets into a single Access table in one step.[/li][li]You can then link the Access database to a SQL DB allowing you to 'push' the data into SQL (Or you can link to the Access data to 'pull' from SQL).[/li][/ul]
[li]Write a macro to aggregate all the workbooks into a single data file (CSV or other standard format) that can then be imported into SQL.[/li][/ol]
Either option will require a little bit of macro knowledge. If you need help with this either of the following forums would be a good start:
[tt] [/tt]Microsoft: Access Modules (VBA Coding)
[tt] [/tt]VBA Visual Basic for Applications (Microsoft)
Hope this helps,
CMP
I am sorry I have not succeeded in answering all of your questions.
In fact, I apologize for not completely answering any of them.
The answers I have however do serve to raise a whole new set of questions I had not previously thought of. In some ways, I am as confused as you are but I believe my confusions are (as always) on a higher plane and
[li]Write a macro to aggregate all the workbooks into a single data file (CSV or other standard format) that can then be imported into SQL.[/li][/ol]
Either option will require a little bit of macro knowledge. If you need help with this either of the following forums would be a good start:
[tt] [/tt]Microsoft: Access Modules (VBA Coding)
[tt] [/tt]VBA Visual Basic for Applications (Microsoft)
Hope this helps,
CMP
I am sorry I have not succeeded in answering all of your questions.
In fact, I apologize for not completely answering any of them.
The answers I have however do serve to raise a whole new set of questions I had not previously thought of. In some ways, I am as confused as you are but I believe my confusions are (as always) on a higher plane and
Aggregate the data into an Access database." If you're just using some database as an intermediary to SQL, then, because you're dealing with Excel spreadsheets, you probably want some kind of hierarchial database, not Access.
I'm not sure what "aggregate" means in the above statement, but Access is a Relational Database. I see this all the time in companies - people "copy and paste" an Excel spreadsheet into an Access table and stop there. This is not correct. You must then NORMALIZE the table. One spreadsheet may (and highly likely) will create multiple Access tables. And since you stated "identical formats", you're probably going to have duplicate data all over the place. A no-no in Relational Databases.
In the above post, option 2 would be the way to go.
Whichever way you choose, the final tables in your database (SQL, Access, Oracle) will have to be Normalized. That's why they were invented - so you can query data through programming and not structure.
I'm not sure what "aggregate" means in the above statement, but Access is a Relational Database. I see this all the time in companies - people "copy and paste" an Excel spreadsheet into an Access table and stop there. This is not correct. You must then NORMALIZE the table. One spreadsheet may (and highly likely) will create multiple Access tables. And since you stated "identical formats", you're probably going to have duplicate data all over the place. A no-no in Relational Databases.
In the above post, option 2 would be the way to go.
Whichever way you choose, the final tables in your database (SQL, Access, Oracle) will have to be Normalized. That's why they were invented - so you can query data through programming and not structure.
- Status