Excel spreadsheet to SQL Server table

Question

Post reply

Excel spreadsheet to SQL Server table

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
May 13, 2016 at 9:18 am

#314028

Hello guys,
I have a requirement to load data from excel spreadsheet to SQL server table and need to automate it. I believe this can be implemented with SSIS.
For this project, do I need to install any excel drivers on the SSIS server?
Many thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ed B Hall of Fame Points: 3669 More actions · Answer 1

I think you need the Access Database Engine.

https://www.microsoft.com/en-us/download/details.aspx?id=13255

This is worth reading.

http://www.madeiradata.com/load-data-excel-ssis-32-bit-vs-64-bit/

ManicStar SSCoach Points: 17992 More actions · Answer 2

SQL!$@w$0ME (5/13/2016)
Hello guys,
I have a requirement to load data from excel spreadsheet to SQL server table and need to automate it. I believe this can be implemented with SSIS.
For this project, do I need to install any excel drivers on the SSIS server?
Many thanks!

Excel automation is evil at best. Most of the time i insist on some form of CSV or text format.

Michael L John One Orange Chip Points: 27060 More actions · Answer 3

Ed B (5/13/2016)

I think you need the Access Database Engine.

https://www.microsoft.com/en-us/download/details.aspx?id=13255

This is worth reading.

http://www.madeiradata.com/load-data-excel-ssis-32-bit-vs-64-bit/%5B/quote%5D

Why do they need the Access database engine for an Excel spreadsheet?

SSIS has a built in connector for Excel, you set up a new data source, with an Excel data connection, and you are good.

http://www.sqlservercentral.com/stairway/72494/"> http://www.sqlservercentral.com/stairway/72494/

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Ed B Hall of Fame Points: 3669 More actions · Answer 4

I think the excel connector relies on MS Office drivers being installed.

https://msdn.microsoft.com/en-us/library/cc280527.aspx

johannes.akesson 34965 SSC Enthusiast Points: 143 More actions · Answer 5

If the data is user generated, you can connect the table in Excel to SQL Server with a tool such as SQL Spreads and let the users update the SQL Server table directly. (disclaimer: I work with SQL Spreads)

www.sqlspreads.com

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 6

SQL!$@w$0ME (5/13/2016)
Hello guys,
I have a requirement to load data from excel spreadsheet to SQL server table and need to automate it. I believe this can be implemented with SSIS.
For this project, do I need to install any excel drivers on the SSIS server?
Many thanks!

Any chance of you posting the spreadsheet (please, no PII or other Private/Proprietary info) or a made up example that shows what the spreadsheet looks like? I actually have some "automation" that will handle certain types of spreadsheets auto-magically even when new columns are added month to month.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 7

SQL!$@w$0ME

SSChampion

Points: 12349

May 15, 2016 at 9:23 am

#1879137

Thanks, I will post the table info/spreadsheet.