-1

This is a question about how you would go about tackling it.

Every week or so, we get some client delivering an Excel file that needs its contents to be uploaded to their CRM package. It's always something different. For instance, now it's a list of all of their product-barcodes and the current stock. They want us to update the stock of all of their products this one time.

Since it's always something different some client requires, we haven't taken the time to automate this yet (there are other priorities) and we've been doing it by hand. We have automated the most received requests already.

What we do now when such a request comes in, is find the table that the data belongs to in the database, and then use Excel to create INSERT or UPDATE sql scripts that we can copy paste into SSMS to execute. The way I would do it, is by first writing my INSERT STATEMENT in one cell, and then use excel functions on each row of data to concatenate my insert statement with all the values that are in that row in Excel.

This is quite error prone, time-consuming and I was wondering if anyone can offer any tips on what they would do? How would you handle a question like that? Is there a quicker way of doing it that you can think of?

Mind you: it's always a different question. Today it has to do with products, tomorrow it could be a list of VAT-numbers that they want to see uploaded so all of their clients now have the correct VAT number.

I'm very curious how you would handle this.

Steven Lemmens
  • 1,441
  • 3
  • 17
  • 30
  • I know that MS Access integrates well with Excel, and you might want to consider that database if you really expect many raw Excel sheets each week. – Tim Biegeleisen Dec 29 '16 at 09:15

1 Answers1

1

Since request is not about automation, I can suggest alternate solution which is still manual, but requires less work.

If you are using any tools for database access like TOAD or Sqldeveloper , there is facility to import data directly from excel.

What you can do is to import data into a separate schema in production or any other database, by creating a temporary table. Further use sql queries for any data massaging and update in target table.

Here are 2 sample threads

Note: threads may refer Oracle database, but its no different in case of mssql too. Ability of tool remains same.

Community
  • 1
  • 1
Vijayakumar Udupa
  • 1,115
  • 1
  • 6
  • 15