Solved

Upload data from CSV file into MS SQL Database automatically

Posted on 2016-08-01
7
52 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 27

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 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 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
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 Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

707 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