Last post Jan 14, 2016 11:35 PM by ninianne98
Jan 14, 2016 05:52 AM|rolf muenchbourg|LINK
I have a sports stats guy that is going to send me his spreadsheet every day that I need to upload somewhere online whereas a SQL server (sql express) can get the information and parse the xls fields and values into database tables. I'm trying to ponder
the best way to do this- are there any intermediary applications/3rd party programs that would help intervene ?
Thanks in advance
Jan 14, 2016 06:36 AM|ninianne98|LINK
you might want to build and SSIS package to pump the data out of the workbook into SQL server.
Jan 14, 2016 09:27 AM|Weibo Zhang|LINK
Hi rolf muenchbourg,
>>I'm trying to ponder the best way to do this- are there any intermediary applications/3rd party programs that would help intervene ?
If you just want to import the data from Excel to the database, you could try to use SSIS as
ninianne98 suggested above. For more detailed guidance, you could have a look at the following links. The first one provides an instruction about how to create a SSIS package to
import Excel to the database and the second one shows how to do it just according to the Management Studio.
If you want to get some applications to achieve the need, you could have a look at the following application. You could try to host an application to let the “sports stats guy” upload the Excel by himself.
If the above are not what you want, you’d better provide more things to clarify how to you want the program to help you solve your issue.
Jan 14, 2016 10:56 PM|rolf muenchbourg|LINK
Thanks for replies. I am using SQL Express , which doesn't have SSIS. I do use SQL Management Studio, so I'm wondering what would be a good alternative.
Jan 14, 2016 11:35 PM|ninianne98|LINK
look into using the ACE driver with OPENROWSET queries
It works within sql express, I use it all the time to export from access and xls is similar.
you can either have a sproc that points and a static file location with the excel workbook that you manually run, or (since there's no sql agent in express) write a batch file using sqlcmd
http://stackoverflow.com/questions/13657057/how-can-i-execute-a-sql-server-2008-command-by-a-batch-file that calls a sql file or sql sproc and schedule that batch file to run every so often. I have several batches I use on my prod box to do things sql
agent would normally do. You'll have to make sure that the windows account used for the WTS has rights to the db/sproc as well as the file location for it to work as a scheduled task.