I have my data model's in excel sheets and my actual database is postgresql 9.5, I would like to make some automated process that should compare the tables in the database and the data model's in the excel and make the changes in the db automatically or at least list out the differences between them. How can I do this? Can It be done using vba macros? or is there any other alternative? Please Give your suggestions on this.
Asked
Active
Viewed 54 times
1 Answers
1
Comparison is one of the bigger weaknesses in Excel. My approach would be something like this:
- Make use of Postgresql's built in functionality to describe its data model and copy that to Excel (or via ODBC if you want to over-engineer it)
- Reshape the output of step 1 to something that has the same format as your Excel based data model
- Do the comparison (either in Excel or in an external diff tool)
Step 1 and 2 can be done in VBA with a lot of string manipulation, but can also be a copy/paste operation, depending on what tools you have available.
The transformation in step 2 can also be handled with Get & Transform (in newer Excel) or PowerQuery (in older Excel).
Sam
- 5,424
- 1
- 18
- 33
-
What is the built in functionality of postgresql to describe its data model? How can I do that? – Karthik Jul 20 '17 at 10:23
-
I have never worked with postgresql, but I think this is a good start: https://stackoverflow.com/questions/109325/postgresql-describe-table – Sam Jul 20 '17 at 11:05
-
Thanks for your suggestion :) – Karthik Jul 20 '17 at 11:22