3

I am quite new to SQL Server but I'm looking for a tool that integrates Excel with SQL Server and provide a two way connection Read/write.

I want to be able to pull data from SQL server and perform some evaluation/data manipulation and then write the data back to the server.

Basically my client receives Excel raw data from vendors which they perform some validation on the spreadsheet then send the spreadsheet back but a copy of the data needs to be in some sort of data management system. I have test MDS and I'm not full satisfied. The functionality I'm looking for is

Data validation Data match - match and merge /consolidate two or more worksheets into one read/write to sql

I Do not want the import/export wizard and don't want to use SSIS and they are both not suitable.

Bart
  • 19,692
  • 7
  • 68
  • 77
Nathan TSQL_Jr
  • 117
  • 1
  • 8
  • 2
    Excel supports OleDB connections to SQL Server. Lots of information via [Google](https://www.google.com/search?source=ig&rlz=1G1ACGW_ENUS358&q=Read%2Fwrite+to+sql+from+excel&oq=Read%2Fwrite+to+sql+from+excel&gs_l=igoogle.3...30.3443.0.3623.24.12.2.7.0.1.334.1526.5j6j0j1.12.0...0.0...1ac.1.12.igoogle.DmgvBjq9_xU) – Tim Aug 13 '13 at 07:01
  • http://stackoverflow.com/questions/9508454/use-excel-2010-to-read-write-to-a-sql-server-2008-database-using-stored-procedur – Mr.Monshaw Aug 13 '13 at 13:32
  • Why is SSIS unsuitable? Your reasons will help us suggest an alternative. For example, powershell, vbscript, VBA, openrowset are all options. Do you prefer a "push" solution where someone presses a button or a "pull" solution where something imports it? – Nick.Mc Oct 30 '16 at 12:18

1 Answers1

0

There's Google - like it has never been before. And there are quite a big number of subject experts posting on their blogs for the love of helping people like you and me.

So check out here multiple ways you can import data into SQL Server, without using SSIS such as,

  • bcp Utility

e.g. bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName

  • Bult Insert using T-SQL

e.g.

`BULK INSERT dbo.ImportTest`
`FROM 'C:\ImportData.txt'`
`WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )`

Note the article was published and last updated in 2012. So you may further check the compatibility for older versions if you are using any.

PS: I still believe you could be using SSIS as not to Re-invent the wheel...

bonCodigo
  • 14,268
  • 1
  • 48
  • 91