[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 58
  • Last Modified:

Upload data from CSV file into MS SQL Database automatically

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
DevSupport
Asked:
DevSupport
4 Solutions
 
pcelbaCommented:
You are in the wrong section most probably. You should post your requirement in EE Gigs: https://www.experts-exchange.com/gigs/
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
funwithdotnetCommented:
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
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
ZberteocCommented:
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
 
ZberteocCommented:
Do you still need help with this question?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
frankhelkCommented:
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now