Solved

Upload data from CSV file into MS SQL Database automatically

Posted on 2016-08-01
7
44 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
7 Comments
 
LVL 41

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Set OWA language and time zone in Exchange for individuals, all users or per database.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now