March 10, 2021 at 6:12 pm
I am not sure this the right place to post this but,
Is it possible to update an Excel file from an ODBC SQL database?
I only want to add new entries and not refresh the existing data in the Excel file.
The reason for this is there is "legacy" data in the SQL database that is not correct and I made the corrections in the Excel file.
Unfortunately it is not possible to fix the legacy data in the SQL database.
I am using the Excel file as a database for electronic design software (Altium).
Thanks for any advice.
March 10, 2021 at 8:38 pm
One way (not ideal) to do it would be to have the excel have the "corrected" data in worksheet 1. Worksheet 2 has a connection to SQL and pulls in data when you click on the "refresh all" button. Assuming you update the excel file weekly, the SQL Query could have a filter on it so it only pulls data from the past 7 days (reduced data set, thus faster refresh) ordered by the date.
Then you have a macro/VBA that grabs the data from worksheet 2 and pastes it to the end of worksheet 1. Potentially highlighting it so you can validate that the data is correct or make corrections as needed.
As far as having SQL export a subset of data to the end of an excel file, I don't know of any way to do that from the SQL side. Well, no nice way to do it. I imagine you could build up some funky powershell to handle it, but that feels like overkill for this.
Another way (method 2), you could make a new table in the database that contains the "corrected" data that you are not allowed to modify. Then make a view that pulls from both tables (UNION and EXCEPT could probably handle it... may be slow if there is a lot of data) then have Excel pull from the view.
Personally, I like option 2 as if the excel ever gets corrupted, it is easy to fix.
Downside to BOTH approaches is that someone needs to open the Excel and click on the refresh button. If possible, it would probably be better to take approach number 2 listed above AND change the tool to pull from SQL Server instead of Excel.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 10, 2021 at 9:50 pm
Thanks for the ideas.
How do I create a filter to pull data for the past 7 days?
I found this but not sure is the right approach:
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/build_query_4.html
March 10, 2021 at 9:56 pm
WHERE <date column> >= DATEADD(day,-7,GETDATE()) AND <date column> <= GETDATE()
Mind you, you may need to do some tweaking to those to make sure you are getting the EXACT date you want as that will get you the date based on the current time too which you may want to filter out. CASTING everything to a DATE datatype can fix that though.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 10, 2021 at 10:38 pm
One thing I am confused about is where the date comes from in the SQL database.
Is this something that is automatically generated by default and always there, or does the app that creates the data need to put it there?
Currently there is no date column in my database.
I was able to add one by adding "Request Date" to the database.
This is the date that the part was requested by a user, which will work fine.
The format is like this: 2021-02-19 11:10:07
I am not sure if the query that you gave above will work with this format or not.
Thanks
March 11, 2021 at 3:09 pm
SQL Server only captures the data you give it. So if you have no method of checking when data was inserted (modified date column for example), then you would need to add one (as it sounds like you did).
As for the format, it depends. What is the datatype of your "Request Date" column? If it is a DATETIME column, then my suggestion will work fine. Otherwise you will need to cast/convert it to a datetime for the comparison if you use the method I suggested.
Of the 2 methods I suggested, I still prefer option 2 where your database is the "source of truth" for the data and the data that is incorrect, you massage it into a new table so you have a table with correct data and a table with incorrect + new data and then have a view to grab the correct and new data while excluding the incorrect data. The reason for this being that if your excel file gets corrupted or modified (intentionally or not), your database will still be intact and your data will still be accurate and you can just re-build the excel from the database data. By relying on your excel to be the source of truth, you run the risk of anyone with permissions to modify the file to change the data and save it resulting in bad data going to your tool. If the data they changed is still valid input to the tool, you may not notice the bad data for days, months, or even years. Changing data in the database is MUCH easier to lock down. Plus your database has backups (right?), does your excel? If you get hit with ransomware for example and that excel gets encrypted, can you recover it?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 11, 2021 at 3:22 pm
Brian,
Thanks for you help, much appreciated.
I am an EE and do not have any experience with this but will play around your suggestions.
Just adding the date column has been a big help, even manually I can see what data is new and append it to the corrected data.
My understanding is that we will eventually correct the database, but that is a long process that will take many months.
In the mean time this is the best I can do.
-Joel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply