There are two parts to this:
a) Downloading the files
b) Importing the downloads
Right now I am focusing on b) Importing the downloads.
I have tried
1) Using an Excel connection in DTS
2) Using OPENROWSET to access the spreadsheets more directly from TSQL
3) Using a linked server to the file.
All of these seem to use the OLEDB 4.0 provider and they all work if I convert the *.htm file to a *.xls file using Excel first.
With one curious exception none of them work from the *.htm files. DTS is the exception, if I first open the *.htm files in Excel the transformation task succeeds.
I don't really want to use Excel at all on the server. It's strange that DTS does work when the *.htm file is open.
I am trying to get some insight into the various OLEDB options to use hoping that will help me avoid having to use Excel. Perhaps I would just be better off using SQL Server 2005 and SSIS?A couple of things here, Oliver.
First, Excel exported to HTML isn't a valuable format when it comes to being able to import off of that data. Why even export it at all when you can just hook into the raw Excel file?
Secondly, if you want to use SSIS, we can help you with that, though I'm not sure that loading an Excel generated HTML file will work. If you are going to stick with DTS, then you should ask this on the forum for that tool: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg&hl=en|||Phil, thanks for the reply but as I mentioned the files are in html and I cannot change that.
Has anyone here used SSIS with an html formatted Excel file?
Ian.|||Best of luck with that! I'd work on getting a new format, 'cause you're going to have to write your own script, or scour the Internet for a custom module already built. There isn't an HTML source in SSIS, nor should there be, in my opinion. It's not really suitable for data transmission.sql
No comments:
Post a Comment