Importing html files into SQL

[bob120579]

I have html report files (.html) that i need the actual data that resides in the report to store it in SQL to use as relational data. What is the best way that I can import this data from html into a sql table? I have a whole directory of these files. I just manually exported to excel and the formatting is clean, but I am looking to automate this in SSIS perhaps.

 

[hmckillop]

In theory the data is in XML format and as such you should be able to use XQuery and XML data types to transform and store the data.

e.g.
You would probably need SSIS to read each .html file and insert the contents into a XML data type or ntext and then parse it.


Can you provide a sample of the data?

Also, why is the report stored as a HTML file? Is there no way of capturing the data at source?

"I'm living so far beyond my income that we may almost be said to be living apart

 

[bob120579]

No, there is no way to get the data at the source, because it was provided to us from an external source. The files are html because they were sent that way as SSRS reports. So just importing them as XML will work?

Also, this is just a thought...

Instead of parsing, would it work to export the data from html to excel through an SSIS package, then import them into SQL...that may eliminate the need to parse the data.

 

[hmckillop]

I am not 100% certain on the method you suggested, but if they are imported successfully into excel, then potentially its a good method of getting the data into SQL.

Alternatively a small VBA script in EXCEL could insert the data to SQL also .



"I'm living so far beyond my income that we may almost be said to be living apart