Solved

Upload data from CSV file into MS SQL Database automatically

Posted on 2016-08-01
7
50 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 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 125 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 26

Accepted Solution

by:
Zberteoc earned 125 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 26

Expert Comment

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

Assisted Solution

by:Jim Horn
Jim Horn earned 250 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

749 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