Unanswered: Import and Clean Dirty Excel 2007 Files to SQL Server 2005
I've been tasked with the following, and am looking for some ideas. Importing Excel Files into SQl Server 2005.
1) The data needs to be harmonized as it is coming from many different branch offices. For example: I will need to truncate fields in Excel, convert different methods of writing currencies (periods instead of commas to denote thousands and millions).
2) I am also interested in getting some feedback report on things that may need attention from the user to correct if it is outside of pre-set constraints, but not necessarily a “show-stopper”. Upon upload the page will approve the Excel file or reject it based on row level data constraints.
3) Eventually I will scale up to a Website, where users can submit their files through Http or Ftp. I assume this will be using XML. After the automated-editing and constraint checks, the upload page will place the data in a holding position for a business analyst to check it and then kick off the automated process to import the file into the SQL Server.
Step #3 is in the future, I want to implement this step at a time.
What software or programming language(s) can I use to accomplish this? I'm not much of a programmer, but I do have good knowledge of SQL.
If I have to use a programming language, which one should I use? Which is easier? C#, VB, etc?