April 13, 2012 at 12:48 pm
Hi
I need help and pointing in the right direction!
I have a database which is recording employee hours and a spreadsheet that contains additional informtion on how these hours were allocated (ie based on employee number and date).
My requirement is to "join" both of these datasets together. I can then connect (eg) crystal to report on these.
Task:
I am able to create a csv out of our employee hours DB each day. Can I have a routine to import this automatically ? (ie so "anyone" can create the report, and place it in the given area)?
Can i run a routine to import the excel file (either apend or delete and start again)
Thanks
April 23, 2012 at 2:25 pm
I'm a bit confused from your question about the direction of data.
However, it seems to me that SSIS would be a good starting point - it can handle the import and/or export of data via packages. The packages can be then be scheduled to run as your wish.
Hope that's a starting point for you but come back if you have more questions.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 23, 2012 at 3:48 pm
markpc2009 (4/13/2012)
I need help and pointing in the right direction!I have a database which is recording employee hours and a spreadsheet that contains additional informtion on how these hours were allocated (ie based on employee number and date).
My requirement is to "join" both of these datasets together. I can then connect (eg) crystal to report on these.
Task:
I am able to create a csv out of our employee hours DB each day. Can I have a routine to import this automatically ? (ie so "anyone" can create the report, and place it in the given area)?
Can i run a routine to import the excel file (either apend or delete and start again)
I would probably create a table in the database with a schema mirroring whatever is on the Excel document then load the Excel document into such a table - probably using SSIS. Once both tables a.k.a. datasets are in the database you can report from them as needed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 23, 2012 at 4:49 pm
I like Paul's suggestion. Since you have some of the data in the database, I wouldn't think you need to export that. You can load a new table with the Excel data, then initiate a join.
SSIS will handle uploading the data, and it can be configured to "watch" a folder for a file and then import it.
http://bidn.com/blogs/MikeDavis/ssis/157/ssis-wmi-event-watcher-check-file-exist-creation
April 24, 2012 at 12:58 pm
Thanks for your replies. I think I have sorted it. I am now exporting from the source in a flat file. I am then using the import routine to import into a "temp" table. From here i am then inserting into the proper table, taking into account any data types, and restricting some of the data.
My next step will be to aggregate some of the data (to give daily totals), but need to accumlate some data first - so i will be back 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply