Get Data from Internet Directly into SQL Server

  • Hi

    I am using these rates for a calculation

    https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/textview.aspx?data=yield

    Currently, I manually update a local table and then insert that table into SQL Server.  It would be ideal if I can get SQL Server to directly access the rates from that site.  Is this possible?  If yes, how would I do that?

    Thank you

  • It looks like you can now use PowerQuery as a source in SSIS.  The nice thing is that Excel can write PowerQuery for you - all you have to do is click buttons. Then you can copy and paste that into SSIS (I think).

    See this article: Power Query Source - SQL Server Integration Services (SSIS) | Microsoft Docs

    In Excel (I'm using like 2019 or 365 or whatever it is)

    Go to the Data Tab, Get Data, From Web... you'll get a popup prompting you for the web address. paste in the URL.

    Then you have to choose the table the data is in.  If you go to the Advanced Editor, you can read the M query that Excel (or PowerBI) wrote:

    let
    Source = Web.Page(Web.Contents("https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/textview.aspx?data=yield")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Date", type date}, {"1 mo", type number}, {"2 mo", type number}, {"3 mo", type number}, {"6 mo", type number}, {"1 yr", type number}, {"2 yr", type number}, {"3 yr", type number}, {"5 yr", type number}, {"7 yr", type number}, {"10 yr", type number}, {"20 yr", type number}, {"30 yr", type number}})
    in
    #"Changed Type"

    Then you should be able to use that as a step in SSIS.

    • This reply was modified 2 weeks, 6 days ago by  pietlinden.
  • perfect!

    Thank you so much

  • pietlinden wrote:

    It looks like you can now use PowerQuery as a source in SSIS.  The nice thing is that Excel can write PowerQuery for you - all you have to do is click buttons. Then you can copy and paste that into SSIS (I think).

    See this article: Power Query Source - SQL Server Integration Services (SSIS) | Microsoft Docs

    In Excel (I'm using like 2019 or 365 or whatever it is) Go to the Data Tab, Get Data, From Web... you'll get a popup prompting you for the web address. paste in the URL.

    Then you have to choose the table the data is in.  If you go to the Advanced Editor, you can read the M query that Excel (or PowerBI) wrote:

    let
    Source = Web.Page(Web.Contents("https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/textview.aspx?data=yield")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Date", type date}, {"1 mo", type number}, {"2 mo", type number}, {"3 mo", type number}, {"6 mo", type number}, {"1 yr", type number}, {"2 yr", type number}, {"3 yr", type number}, {"5 yr", type number}, {"7 yr", type number}, {"10 yr", type number}, {"20 yr", type number}, {"30 yr", type number}})
    in
    #"Changed Type"

    Then you should be able to use that as a step in SSIS.

    water490 wrote:

    Hi

    I am using these rates for a calculation

    https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/textview.aspx?data=yield

    Currently, I manually update a local table and then insert that table into SQL Server.  It would be ideal if I can get SQL Server to directly access the rates from that site.  Is this possible?  If yes, how would I do that?

    Thank you

    Now THAT's interesting.  Just curious... do you know what the page will be for 2022?  Or will it magically change at the end of the month?

    The reason why I think this is so cool is that I just tried the steps the pietlinden posted and it make a very nice spreadsheet.  That means I could built a macro into it and setup a job on a system that has Excel loaded on it (never an SQL Server, IMHO) and it could automagically download the data and save itself as a dated Excel spreadsheet file.  Then, I can setup a T-SQL job that uses the ACE drivers to auto-magically read that spreasheet file into a table and Bob's your uncle.

    This could be a game changer for some things that people do.  I didn't know they had built that capability into Excel.

    Thank you both for this thread! 😀

    --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)
    Intro to Tally Tables and Functions

  • Now THAT's interesting.  Just curious... do you know what the page will be for 2022?  Or will it magically change at the end of the month?

    Yes, that link should show all the years. All you have to do is refresh the query that pietlinden provided.

  • water490 wrote:

    Now THAT's interesting.  Just curious... do you know what the page will be for 2022?  Or will it magically change at the end of the month?

    Yes, that link should show all the years. All you have to do is refresh the query that pietlinden provided.

    Since it's 2022 right now, how could you use that link to show the data from 2020?

     

    --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)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    water490 wrote:

    Now THAT's interesting.  Just curious... do you know what the page will be for 2022?  Or will it magically change at the end of the month?

    Yes, that link should show all the years. All you have to do is refresh the query that pietlinden provided.

    Since it's 2022 right now, how could you use that link to show the data from 2020?

    That particular link is to show all years of data starting from 1990.

  • I want so say you create a function that generates the page(s) you need to go to. I saw Reza Rad do it in a class I took. It was mindboggling how much you can do in PowerQuery if you know what you're doing. You basically feed it a table of values and then use that to generate the page numbers or whatever in the web address(es). It's kinda like a Tally table trick. Should be on his website somewhere, or in his book PowerBI: From Rookie to Rockstar. (look it up online... it's a free download and absolutely huge).

  • better option for me would be to use a small powershell script to download the contents and load directly to sql server.

    edit: the original link only extracts current year - for all years link is

    https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/TextView.aspx?data=yieldAll

    for a particular year link is (replacing 2021 with desired year)

    https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/TextView.aspx?data=yieldYear&year=2021

    very rough attempt at it (working!!) as I don't have my personal scripts available

    function ConvertTo-DataTable {
    <#
    .SYNOPSIS
    Convert regular PowerShell objects to a DataTable object.

    .DESCRIPTION
    Convert regular PowerShell objects to a DataTable object.

    .EXAMPLE
    $myDataTable = $myObject | ConvertTo-DataTable

    .NOTES
    Name: ConvertTo-DataTable
    Author: Øyvind Kallstad @okallstad
    Version: 1.1
    #>
    [CmdletBinding()]
    param (
    # The object to convert to a DataTable
    [Parameter(ValueFromPipeline = $true)]
    [PSObject[]] $InputObject,

    # Override the default type.
    [Parameter()]
    [string] $DefaultType = 'System.String'
    )

    begin {

    # create an empty datatable
    try {
    $dataTable = New-Object -TypeName 'System.Data.DataTable'
    Write-Verbose -Message 'Empty DataTable created'
    }

    catch {
    Write-Warning -Message $_.Exception.Message
    break
    }

    # define a boolean to keep track of the first datarow
    $first = $true

    # define array of supported .NET types
    $types = @(
    'System.String',
    'System.Boolean',
    'System.Byte[]',
    'System.Byte',
    'System.Char',
    'System.DateTime',
    'System.Decimal',
    'System.Double',
    'System.Guid',
    'System.Int16',
    'System.Int32',
    'System.Int64',
    'System.Single',
    'System.UInt16',
    'System.UInt32',
    'System.UInt64'
    )
    }

    process {

    # iterate through each input object
    foreach ($object in $InputObject) {

    try {

    # create a new datarow
    $dataRow = $dataTable.NewRow()
    Write-Verbose -Message 'New DataRow created'

    # iterate through each object property
    foreach ($property in $object.PSObject.get_properties()) {

    # check if we are dealing with the first row or not
    if ($first) {

    # handle data types
    if ($types -contains $property.TypeNameOfValue) {
    $dataType = $property.TypeNameOfValue
    Write-Verbose -Message "$($property.Name): Supported datatype <$($dataType)>"
    }

    else {
    $dataType = $DefaultType
    Write-Verbose -Message "$($property.Name): Unsupported datatype ($($property.TypeNameOfValue)), using default <$($DefaultType)>"
    }

    # create a new datacolumn
    $dataColumn = New-Object 'System.Data.DataColumn' $property.Name, $dataType
    Write-Verbose -Message 'Created new DataColumn'

    # add column to DataTable
    $dataTable.Columns.Add($dataColumn)
    Write-Verbose -Message 'DataColumn added to DataTable'
    }

    # add values to column
    if ($property.Value -ne $null) {

    # if array or collection, add as XML
    if (($property.Value.GetType().IsArray) -or ($property.TypeNameOfValue -like '*collection*')) {
    $dataRow.Item($property.Name) = $property.Value | ConvertTo-Xml -As 'String' -NoTypeInformation -Depth 1
    Write-Verbose -Message 'Value added to row as XML'
    }

    else{
    $dataRow.Item($property.Name) = $property.Value -as $dataType
    Write-Verbose -Message "Value ($($property.Value)) added to row as $($dataType)"
    }
    }
    }

    # add DataRow to DataTable
    $dataTable.Rows.Add($dataRow)
    Write-Verbose -Message 'DataRow added to DataTable'

    $first = $false
    }

    catch {
    Write-Warning -Message $_.Exception.Message
    }
    }
    }

    end { Write-Output (,($dataTable)) }
    }

    function get-table
    {
    param(
    [Parameter(Mandatory = $true)]
    [Microsoft.PowerShell.Commands.HtmlWebResponseObject] $WebRequest,

    [Parameter(Mandatory = $true)]
    [int] $TableNumber
    )

    ## Extract the tables out of the web request
    $tables = @($WebRequest.ParsedHtml.getElementsByTagName("TABLE"))
    $table = $tables[$TableNumber]
    $titles = @()
    $rows = @($table.Rows)

    ## Go through all of the rows in the table
    foreach($row in $rows)
    {
    $cells = @($row.Cells)

    ## If we've found a table header, remember its titles
    if($cells[0].tagName -eq "TH")
    {
    $titles = @($cells | % { ("" + $_.InnerText).Trim() })
    continue
    }

    ## If we haven't found any table headers, make up names "P1", "P2", etc.
    if(-not $titles)
    {
    $titles = @(1..($cells.Count + 2) | % { "P$_" })
    }

    ## Now go through the cells in the the row. For each, try to find the
    ## title that represents that column and create a hashtable mapping those
    ## titles to content
    $resultObject = [Ordered] @{}
    for($counter = 0; $counter -lt $cells.Count; $counter++)
    {
    $title = $titles[$counter]
    if(-not $title) { continue }

    $resultObject[$title] = ("" + $cells[$counter].InnerText).Trim()
    }

    ## And finally cast that hashtable to a PSCustomObject
    [PSCustomObject] $resultObject
    }
    }
    $url = 'https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/TextView.aspx?data=yieldYear&year=2021'

    $r = Invoke-WebRequest $url

    $tables = @($r.ParsedHtml.getElementsByTagName("TABLE"))

    # below just to output to a file to check contents
    #get-table -WebRequest $r -TableNumber 1|Export-Csv -NoTypeInformation "c:\temp\test.csv"


    $s=get-table -WebRequest $r -TableNumber 1|ConvertTo-DataTable

    $ConnectionString = "Data Source=servername; Initial Catalog=test; Trusted_Connection=True;";

    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnectionString
    $bulkCopy.DestinationTableName = "rates"
    $bulkCopy.WriteToServer($s)

    <#
    create above table as follows - without a bit of work (not much) we could create a datatable with correct datatypes
    and with other names.

    we could also involve this on a transaction, add the data onto a temp table
    and then process it onto the final table with correct names and datatypes

    create table rates
    (
    [Date] varchar(50)
    ,[1 mo] decimal(10, 4)
    ,[2 mo] decimal(10, 4)
    ,[3 mo] decimal(10, 4)
    ,[6 mo] decimal(10, 4)
    ,[1 yr] decimal(10, 4)
    ,[2 yr] decimal(10, 4)
    ,[3 yr] decimal(10, 4)
    ,[5 yr] decimal(10, 4)
    ,[7 yr] decimal(10, 4)
    ,[10 yr] decimal(10, 4)
    ,[20 yr] decimal(10, 4)
    ,[30 yr] decimal(10, 4)

    )
    #>
  • That's awesome, Frederico!  Thanks for posting that.

    It would appear, though, that you actually have to program for the table structure.  Is there anything like the getting data from the Web trick that Excel has?  Basically, you define the source URL and a couple of settings, click the final "OK" button and it's all done for you.

    --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)
    Intro to Tally Tables and Functions

  • This was removed by the editor as SPAM

  • Jeff Moden wrote:

    That's awesome, Frederico!  Thanks for posting that.

    It would appear, though, that you actually have to program for the table structure.  Is there anything like the getting data from the Web trick that Excel has?  Basically, you define the source URL and a couple of settings, click the final "OK" button and it's all done for you.

    Yes you do need to program for your desired table structure - I have found another scriplet that will create the table for you but most times that is not what you would desire in any case.

    but the above can be used to output to a csv file which you then use as standard in SQL.

    but having said that with a bit of programming it is possible to create a set of scripts that will do that for you - just not something that most folks will spend time on unless there is a reason for it.

  • Just saw a video from Chandoo (Excel MVP), right here.  Watch the next to last section - the biggest game changer in Excel in the last year is PowerQuery, according to him. You can basically build your data transformation pipelines and see the outputs... (well, watch that part of the video!).  He has tons of videos on YouTube... definitely worth watching - no fluff, no sales pitch, just really good content.

Viewing 13 posts - 1 through 12 (of 12 total)

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