?
Solved

Upload data from CSV file into MS SQL Database automatically

Posted on 2016-08-01
7
Medium Priority
?
53 Views
Last Modified: 2016-09-13
I have a spreadsheet with data in it. I want to run a script (batch/sql/powershell) which can automatically upload data from the spreadsheet to MS SQL database.
This should happen weekly such that any changes in spreadsheet (add rows/ update values/ remove rows) needs to be updated into the corresponding entries in database (and not create a new row etc for addition of values)
Also there should be a comparison of values in spreadsheet vs columns in database so that only the changes are updated.
For example : if a server has a java version updated in spreadsheet it needs to be updated into corresponding database row while comparison is performed.

Attached is the spreadsheet which is imported into a MS SQL database table
DataCollectionTest.xlsx
0
Comment
Question by:DevSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 41738051
You are in the wrong section most probably. You should post your requirement in EE Gigs: https://www.experts-exchange.com/gigs/
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 41738060
This expert suggested creating a Gigs project.
What you are asking can be done by building an SSIS package to consume the data, connect to the target database, and including a Merge transform to compare rows/primary keys and determine which rows are inserted, updated, deleted, or no action.  

The scheduling can be done via SQL Server Agent.

As far as getting the thing built, pcelba is correct in that what you are asking is well above the scope of a single EE question, and is more appropriate in Gigs.  You can ask single technical questions here, and ask for design help, but not building a whole project.

Also just to give fair warning, Excel is considered a poor data source for any ETL project, as users can edit the file in so many ways that will cause any project that requires a 'standard' contract between source and destination to fail.  If this .csv is generated by an automated process and not touched by human hands then we're fine, otherwise red flags are going to go up.

Good luck.
Jim
1
 
LVL 12

Assisted Solution

by:funwithdotnet
funwithdotnet earned 500 total points
ID: 41738447
SQL Server 2008 + can use a trigger or schedule to BULK INSERT. I Would do a BULK INSERT followed by a SQL transform to the desired table.  As for generating the CSV file, I guess there's a macro or something to do that in Excel.

Good Luck!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 41739165
I would upload the whole file to a working table, which I would truncate every time before that and then compare the working table with the actual table using a MERGE statement. You will have to make sure that there is a PK in the file and in the table that is consistent. This will make it easier to setup and maintain. If you don't want to load the whole file every time then you could use an external module written in VB script,  Java, Python or other language of your choice. SSIS would work too but I find it more complicated to setup and to maintain, especially for something simple like this.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41758015
Do you still need help with this question?
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 41758023
In addition to the above excellent answers based on not a lot of detail, these articles should help...

Requirements Document Template for an ETL Project

Top 10 Ways to Ask Better Questions, especially Number Six.. Avoid unrealistic expectations of a single EE question, also known as 'Paint My House'.
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41795657
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Zberteoc (https:#a41739165)
-- Jim Horn (https:#a41758023)
-- Jim Horn (https:#a41738060)
-- funwithdotnet (https:#a41738447)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question