October 7, 2008 at 11:38 am
Hello,
I have gone around in circles on this.
I need to import data from an Excel file into a SQL table. Can you provide me either links on good resources for this information or a way to do this?
I don't have access to change the SQL Server Surface Area Configuration.
Thank you for your help.
Jason
October 7, 2008 at 12:00 pm
The easiest way would probably be to use SSIS. You can check the Books online information for some Simple how to's with SSIS. Alternately, you could use BCP.
You state you cannot chance the surface area configuration, but you didn't let us know what is enabled, not enabled, so that really doesn't mean a whole lot to us...
-Luke.
October 7, 2008 at 12:19 pm
The server won't let me even open the Surface Area Configuration.
October 7, 2008 at 12:43 pm
does anyone else in your organization have the ability to open the SAC utility?
what type of access do you have to the server?
can you see the services and tell which ones are started?
Have you tried using SSIS?
Have you tried a bulk import?
does one work and not the other?
a bit more information would most likely yield you a bit more help from us.
-Luke.
October 7, 2008 at 1:03 pm
OK...
I'll need to delete the data currently in the SQL table and reimport from an excel file weekly. There will be over 1500 records in the excel file.
I want the user to be able to run the import from their workstation. I do not want to install SQL client tools on their workstation. This data will then be used in a SSRS report (which I have done and working).
I tried using SSIS and running dtexec. Kept getting errors and dead ends. Resolutions (from google searches) mentioned need SSIS installed on workstation (not possible).
If SSIS can be used in our situation, that seems like the preferred method.
Any thoughts or ideas.
Jason
October 7, 2008 at 1:35 pm
You want the user to be able to save the file in a location of your choosing, then import it, then report on it right?
Why not build a simple little asp.net page, with a File upload bit. The user will log into the page, upload the file. The ASP Page will then make sure it goes to the place you specify, some network share or whatnot. After the file gets there you have a couple of different ways to get it into your database...
1) ssis, you could have an SSIS package in a job that executes every so often... 1 minute, 5 minutes etc. You could have it check that the file is in the correct location and check if it's new or not. If new, run the import.
2) How I would most likely do it would be to create your SSIS package to do the import. then create a SQL Agent Job that is able to run it. From your ASP page above you could use execute the sp_startjob stored procedure to begin the job. The problem with this is it's asychronous.
There are some other options discussed here which may help as well...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=172346&SiteID=1
October 7, 2008 at 1:46 pm
I'm not a .NET developer. So creating an ASP.Net page to do this is a little beyond my knowledge.
Can this be done without .NET such as through a batch file (or something else)?
I tried using dtexec, but that kept erroring out.
Thanks.
Jason
October 7, 2008 at 2:20 pm
Nope, It wouldn't have to be done via ASP I was just thinking that might give your user the best experience (ease of use) and make sure you always get the file where you think you're going to get it. Trust me I'm not a .Net developer either, although apparently my boss thinks I am. Really any of the .Net languages with the Express versions would be sufficient and the largest majority of all of this would be drag and drop.
However, if you'd prefer you can use a .bat, .cmd, perl script whatever you're comfortable with, to get the file to the network share, then you could even use OSQL to connect to the SQL box and run the sp_startJob bit, if you really wanted to.
Again, you could examine having the job run every x minutes and the 1st bit of your SSIS task could be to check if the file is different from the last time it ran, (check the modified date or something with the filesystem object). That way all your user has to do is know where to put the file and what to name it. Once that's done, your job runs the next time, the data is published and away you go.
-Luke.
October 7, 2008 at 2:20 pm
Jason (10/7/2008)
I need to import data from an Excel file into a SQL table. Can you provide me either links on good resources for this information or a way to do this?
Do you need to automate this or do you need to do this manually as a one-off?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 7, 2008 at 2:29 pm
I'm not going to run this automatically. I want this to run when the user wants it to run.
October 7, 2008 at 2:32 pm
I don't have Visual Studio .NET
October 7, 2008 at 2:51 pm
You could create a stored procedure which your users could run.... the SP would use the openrowset function to import the data into the table you specify.... Read:
http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx
http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx
these will explain the requirements and how to do it and also have sample code for you to follow.
October 7, 2008 at 3:04 pm
bitbucket (10/7/2008)
You could create a stored procedure which your users could run.... the SP would use the openrowset function to import the data into the table you specify.... Read:http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx
http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx
these will explain the requirements and how to do it and also have sample code for you to follow.
Which is great...except that you have to use Surface Area Config to enable OPENROWSET (which the OP has mentioned not having access to).
Knowing that I will probably get stoned just for mentioning it, but - do your users have MS Access? This could also be done using that as well (kind of a poor man's SSIS, without having to install the client tools).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 7, 2008 at 3:43 pm
Jason (10/7/2008)
I don't have access to change the SQL Server Surface Area Configuration.
Do you have sysadmin in SQL Server or access to the 'sa' logon?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 8, 2008 at 6:12 am
Yes I have sysadmin access and access to to 'sa'.
Let me clarify:
When I try to open Surface Area configuration, I get a "Named Pipes provider error 40 - could not open connection to SQL Server."
I'm try to connect to "localhost" when on the SQL server locally.
Thanks
Jason
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply