-1

I'm wondering if the functionality exists within SQL to achieve the following:

I have an excel spreadsheet which users update DAILY with information something like the following:

workbook, Type, account Reference, Company Name, Booking Type, Type of Client, Type of Work, Product Code, Value of appointment,

Jan KPI.xlsx, Sales, SA1326, company1, Questionnaire, Current, Ad Hoc, Q1, £18.00

Jan KPI.xlsx, Sales, SA1327, company 2, Home visit, Current, Ad Hoc, MR7, £325.00

Jan KPI.xlsx, Sales, SA1328, company 2, home visit, Current, Ad Hoc, MR6, £0.00

Using the first column which defines the name of the worksheet (and will change every month) I want to import this information to a table, overwriting any existing data from the current spreadsheet but leave anything from any other spreadsheet previously uploaded.

The end result being that we hold and can report on all historical data whilst also bing able to view the current work as well.

I understand the import could be done using SSIS but I have no experience with that side of SQL.

Thanks.

user3318535
  • 41
  • 4
  • Try http://stackoverflow.com/questions/375991/import-excel-spreadsheet-columns-into-sql-server-database – indofraiser Jun 02 '15 at 14:04
  • You can delete the data for the current spreadsheet from your table, and then import the whole spreadsheet. – Tab Alleman Jun 02 '15 at 14:10
  • Then I would lose all historic data, the spreadsheet contains only the current months worth of data. So for example after the 31st of January they will start a spreadsheet for February with no data in it. So I need it to not delete the data from January that they have been updating and uploading but to remove and the re-write the February each time until they then move onto March and so forth. – user3318535 Jun 02 '15 at 21:14
  • I think I could use MERGE potentially but I don't have a unique key for each line so I'm not sure if that would work? – user3318535 Jun 03 '15 at 06:32

2 Answers2

0

Maybe you would like to try out a tool I have developed? It's an Excel Add-In that exports users' Excel data to tables in SQL Server. Its purpose is to collect data from end users.

There is a feature to create a filter where users can select data. You can create a filter with months and use that filter to let the users work only with the current month. When you move on to the next month, data for the previous month will be archived in the table.

If you would like to try it out, I can give you a free license to test your case. If you are interested in testing it out, just send me an email:)

www.sqlpreads.com

JohannesGbg
  • 101
  • 1
  • 4
0

So in the end I went with an SSIS package, not so difficult to create as I expected. There's an excellent video explaining how to set up an excel import that will allow you to pick up either a renamed excel (ie the name changes per month or has a date stamp on it etc) or to pick up multiple files:

https://www.youtube.com/watch?v=_B83CPqX-N4

Then it was a case of importing the spreadsheet to a temp table, using a simple delete command that compared the workbook column of the temp table to the workbook column in the destination table and deleting anything that matches and then simply importing the temp table into the final table and clearing down the temp table.

The important part for me was that I had no unique key field for each line, only a column that was unique to the spreadsheet it was coming from and wasn't sure how best to proceed. The above method works how I want it to with the added benefit that as long as the spreadsheet remains named correctly my users can add or edit data from any month not just the latest one.

user3318535
  • 41
  • 4