JSON网页数据抓取

2014-01-28 10:47:28 983 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

.NET 网页或json数据抓取系列：2 - 动态字符集转换动态字符集转换阅读详情

各位高手，如何取得下面网址的全部73 条数据？

https://www.healthcare.gov/find-premium-estimates/#results/&aud=indv&type=med&state=AZ&county=Coconino&age0=40

...全文

.NET 网页或json数据抓取系列：1 - 发送请求数据抓取请求阅读详情

Treenewbee

网络编程 1,502 成员 18,427 内容

+ 关注 + 加入社区

切换为时间正序

Treenewbee 2014-02-05

打赏
举报

蓝天老师，11楼代码返回的数据，除了最后一列都是相同的

蓝天630902 2014-02-05

打赏
举报



Sub Get_health_care_premium_estimates_and_preview_Marketplace_plans()
    On Error Resume Next
    With CreateObject("internetexplorer.application")
        .Visible = True
        .Navigate "https://www.healthcare.gov/find-premium-estimates/#results/&aud=indv&type=med&state=AZ&county=Coconino&age0=50"
        Do Until .ReadyState = 4
            DoEvents
        Loop
        t1 = Timer
        Do Until Timer > t1 + 5    '这里等5秒
            DoEvents
        Loop
        For i = 1 To 7
            t1 = Timer
            Do Until Timer > t1 + 0.15    '这里等0.15秒
                DoEvents
            Loop
            .document.GetElementById("showNextPlans").Click
        Next i
        '.Quit
    End With
End Sub

蓝天630902 2014-02-05

打赏
举报



Sub qhp_landscape_individual_market_medical_json()
    s1 = InputBox("输入县名", "获取monthly premium字段", "COCONINO")
    ss = "premium_adult_individual_age_21,premium_adult_individual_age_27,premium_adult_individual_age_30,premium_adult_individual_age_40,premium_adult_individual_age_50,premium_adult_individual_age_60,plan_marketing_name,medical_maximum_out_of_pocket_individual_standard,medical_deductible_individual_standard,county,state"
    Cells.Clear
    dt = Split(ss, ",")
    [a1:k1] = Split("age_21,age_27,age_30,age_40,age_50,age_60,plan_marketing_name,medical_maximum_out_of_pocket_individual_standard,medical_deductible_individual_standard,county,state", ",")
    On Error Resume Next
    Set js = CreateObject("MSScriptControl.ScriptControl")
    js.Language = "jscript"
    With CreateObject("Microsoft.XmlHttp")
        .Open "GET", "https://www.healthcare.gov/datasets/resource/qhp-landscape-individual-market-medical.json?%24where=county = '" & s1 & "'", False
        .Send
        js.AddCode "a=" & .responsetext
        n = Range("a65536").End(xlUp).Row
        For i = 0 To js.Eval("a.length") - 1
            For j = 0 To 5
                Cells(i + 2 + n, j + 1) = Int(js.Eval("a[" & i & "]." & dt(j))) + 1
            Next j
            For j = 6 To 10
                Cells(i + 2 + n, j + 1) = js.Eval("a[" & i & "]." & dt(j))
            Next j
        Next i
    End With
    Cells.Font.Size = 9
    [a1].CurrentRegion.Columns.AutoFit
    'Cells.Columns.AutoFit
    MsgBox "ok"
End Sub

Treenewbee 2014-02-05

打赏
举报

以下代码不能运行

Do Until Not WebBrowser1.Busy And WebBrowser1.ReadyState = 4
DoEvents
Loop
'
WebBrowser1.Document.GetElementById("showNextPlans").InvokeMember ("onclick")
Next

蓝天630902 2014-02-05

打赏
举报



Sub slcsp_county_zip_reference_data_json()
    s1 = InputBox("输入县名", "获取monthly premium字段", "COCONINO")
    dt = Split("_0_20_child_dependents,_0_20_not_a_child_dependent,_21,_22,_23,_24,_25,_26,_27,_28,_29,_30,_31,_32,_33,_34,_35,_36,_37,_38,_39,_40,_41,_42,_43,_44,_45,_46,_47,_48,_49,_50,_51,_52,_53,_54,_55,_56,_57,_58,_59,_60,_61,_62,_63,_64,_65_and_over,county_name,fips,state,zip_code", ",")
    On Error Resume Next
    Cells.Clear
    [a1:ay1] = dt
    Set js = CreateObject("MSScriptControl.ScriptControl")
    js.Language = "jscript"
    With CreateObject("Microsoft.XmlHttp")
        .Open "GET", "https://www.healthcare.gov/datasets/resource/slcsp-county-zip-reference-data.json?%24where=county_name%3D%27" & s1 & "%27", False
        .Send
        js.AddCode "a=" & .responsetext
        For i = 0 To js.Eval("a.length")
            For j = 0 To UBound(dt)
                Cells(i + 2, j + 1) = js.Eval("a[" & i & "]." & dt(j))
            Next j
        Next i
    End With
End Sub

Treenewbee 2014-02-05

打赏
举报

对于某个县的数据，如COCONINO，当我选择不同的年龄时，同一个保险公司的价格并不完全一样（但1-20岁是一样的，21-24岁似乎也一样，其他年龄的价格随着年龄的增长，每个年龄会有几块钱的差异，找不到规律，也没找到网页使用了那些函数）。另外，换个年龄，网页返回的记录数目不完全一样，但JSON返回的数据是一样的，很奇怪。还请指教

Treenewbee 2014-02-05

打赏
举报

多谢蓝天老师！！！ 7#的代码很整齐，高效. 8#的代码测试成功！！！

Treenewbee 2014-02-03

打赏
举报

另外，如果使用webbrowser,如何根据记录的个数模拟点击相应次数的"show next n plans"?

Treenewbee 2014-02-03

打赏
举报

多谢蓝天老师。有个问题是当我更改年龄时，比如： https://www.healthcare.gov/find-premium-estimates/#results/&aud=indv&type=med&state=AZ&county=Coconino&age0=15 https://www.healthcare.gov/find-premium-estimates/#results/&aud=indv&type=med&state=AZ&county=Coconino&age0=50 网页上得到的记录个数并不相同，“monthly premium”字段的数值也不相同，找不到数据从哪里得来的，或者规律所在。而且我发现年龄0-20时返回的结果是一样的。事实上，我最需要的就是某个县的0-40岁的“monthly premium”字段，请蓝天老师再帮忙看看，多谢了

蓝天630902 2014-02-02

打赏
举报



Sub qhp_landscape_individual_market_medical_json()
    ss = "individual_1_child_age_30,emergency_room_standard,drug_maximum_out_of_pocket_family_standard,"
    ss = ss & "couple_2_children_age_50,individual_1_child_age_40,issuer_name,premium_adult_individual_age_2"
    ss = ss & "7,premium_adult_individual_age_21,couple_3_or_more_children_age_50,customer_service_phone_num"
    ss = ss & "ber_toll_free,plan_marketing_name,county,medical_maximum_out_of_pocket_individual_standard,in"
    ss = ss & "dividual_3_or_more_children_age_50,specialty_drugs_standard,couple_2_children_age_30,premium_"
    ss = ss & "adult_individual_age_30,premium_couple_60,couple_3_or_more_children_age_40,couple_1_child_age"
    ss = ss & "_50,individual_3_or_more_children_age_40,individual_1_child_age_21,premium_child,preferred_br"
    ss = ss & "and_drugs_standard,couple_2_children_age_40,premium_adult_individual_age_40,premium_couple_50"
    ss = ss & ",couple_3_or_more_children_age_30,individual_3_or_more_children_age_30,plan_id_standard_compo"
    ss = ss & "nent,premium_couple_40,couple_1_child_age_40,inpatient_physician_standard,generic_drugs_stand"
    ss = ss & "ard,individual_3_or_more_children_age_21,metal_level,child_dental,premium_adult_individual_ag"
    ss = ss & "e_50,individual_2_children_age_21,medical_deductible_family_standard,primary_care_physician_s"
    ss = ss & "tandard,specialist_standard,plan_type,premium_couple_30,couple_1_child_age_30,drug_deductible"
    ss = ss & "_individual_standard,drug_deductible_family_standard,non_preferred_brand_drugs_standard,drug_"
    ss = ss & "maximum_out_of_pocket_individual_standard,medical_deductible_individual_standard,summary_of_b"
    ss = ss & "enefits_url,couple_2_children_age_21,network_url,individual_2_children_age_30,premium_adult_i"
    ss = ss & "ndividual_age_60,premium_couple_21,child_only_offering,couple_1_child_age_21,medical_maximum_"
    ss = ss & "out_of_pocket_family_standard,rating_area,state,drug_formulary_url,plan_brochure_url,individu"
    ss = ss & "al_2_children_age_40,couple_3_or_more_children_age_21,inpatient_facility_standard,source,cust"
    ss = ss & "omer_service_phone_number_local,individual_2_children_age_50,individual_1_child_age_50,custom"
    ss = ss & "er_service_phone_number_tty "
    dt = Split(ss, ",")
    On Error Resume Next
    Cells.Clear
    Set js = CreateObject("MSScriptControl.ScriptControl")
    js.Language = "jscript"
    With CreateObject("Microsoft.XmlHttp")
        .Open "GET", "https://www.healthcare.gov/datasets/resource/qhp-landscape-individual-market-medical.json?$select=*&%24where=state%3D%27AZ%27%20AND%20county%20%3D%20%27Coconino%27", False
        .Send
        js.AddCode "a=" & .responsetext
        For i = 0 To js.Eval("a.length")
            For j = 0 To UBound(dt)
                Cells(i + 1, j + 1) = js.Eval("a[" & i & "]." & dt(j))
            Next j
            Cells(i + 1, "Z") = Int(Cells(i + 1, "Z")) + 1
        Next i
    End With
    Range("Z1:Z" & js.Eval("a.length")).Interior.ThemeColor = xlThemeColorAccent6      'medical_maximum_out_of_pocket_individual_standard
    Range("M1:M" & js.Eval("a.length")).Interior.ThemeColor = xlThemeColorAccent5      'premium_adult_individual_age_40
    Range("AX1:AX" & js.Eval("a.length")).Interior.ThemeColor = xlThemeColorAccent2    'medical_deductible_individual_standard
    MsgBox "OK!"
End Sub

蓝天630902 2014-02-02

打赏
举报



Sub qhp_landscape_individual_market_medical_json()
    ss = "individual_1_child_age_30,emergency_room_standard,drug_maximum_out_of_pocket_family_standard,"
    ss = ss & "couple_2_children_age_50,individual_1_child_age_40,issuer_name,premium_adult_individual_age_2"
    ss = ss & "7,premium_adult_individual_age_21,couple_3_or_more_children_age_50,customer_service_phone_num"
    ss = ss & "ber_toll_free,plan_marketing_name,county,medical_maximum_out_of_pocket_individual_standard,in"
    ss = ss & "dividual_3_or_more_children_age_50,specialty_drugs_standard,couple_2_children_age_30,premium_"
    ss = ss & "adult_individual_age_30,premium_couple_60,couple_3_or_more_children_age_40,couple_1_child_age"
    ss = ss & "_50,individual_3_or_more_children_age_40,individual_1_child_age_21,premium_child,preferred_br"
    ss = ss & "and_drugs_standard,couple_2_children_age_40,premium_adult_individual_age_40,premium_couple_50"
    ss = ss & ",couple_3_or_more_children_age_30,individual_3_or_more_children_age_30,plan_id_standard_compo"
    ss = ss & "nent,premium_couple_40,couple_1_child_age_40,inpatient_physician_standard,generic_drugs_stand"
    ss = ss & "ard,individual_3_or_more_children_age_21,metal_level,child_dental,premium_adult_individual_ag"
    ss = ss & "e_50,individual_2_children_age_21,medical_deductible_family_standard,primary_care_physician_s"
    ss = ss & "tandard,specialist_standard,plan_type,premium_couple_30,couple_1_child_age_30,drug_deductible"
    ss = ss & "_individual_standard,drug_deductible_family_standard,non_preferred_brand_drugs_standard,drug_"
    ss = ss & "maximum_out_of_pocket_individual_standard,medical_deductible_individual_standard,summary_of_b"
    ss = ss & "enefits_url.url,couple_2_children_age_21,network_url.url,individual_2_children_age_30,premium_adult_i"
    ss = ss & "ndividual_age_60,premium_couple_21,child_only_offering,couple_1_child_age_21,medical_maximum_"
    ss = ss & "out_of_pocket_family_standard,rating_area,state,drug_formulary_url.url,plan_brochure_url.url,individu"
    ss = ss & "al_2_children_age_40,couple_3_or_more_children_age_21,inpatient_facility_standard,source,cust"
    ss = ss & "omer_service_phone_number_local,individual_2_children_age_50,individual_1_child_age_50,custom"
    ss = ss & "er_service_phone_number_tty"
    dt = Split(ss, ",")
    On Error Resume Next
    Cells.Clear
    Set js = CreateObject("MSScriptControl.ScriptControl")
    js.Language = "jscript"
    With CreateObject("Microsoft.XmlHttp")
        .Open "GET", "https://www.healthcare.gov/datasets/resource/qhp-landscape-individual-market-medical.json?$select=*&%24where=state%3D%27AZ%27%20AND%20county%20%3D%20%27Coconino%27", False
        .Send
        js.AddCode "a=" & .responsetext
        For i = 0 To js.Eval("a.length")
            For j = 0 To UBound(dt)
                Cells(i + 1, j + 1) = js.Eval("a[" & i & "]." & dt(j))
            Next j
            Cells(i + 1, "Z") = Int(Cells(i + 1, "Z")) + 1
        Next i
    End With
    Range("Z1:Z" & js.Eval("a.length")).Interior.ThemeColor = xlThemeColorAccent6      'medical_maximum_out_of_pocket_individual_standard
    Range("M1:M" & js.Eval("a.length")).Interior.ThemeColor = xlThemeColorAccent5      'premium_adult_individual_age_40
    Range("AX1:AX" & js.Eval("a.length")).Interior.ThemeColor = xlThemeColorAccent2    'medical_deductible_individual_standard
    MsgBox "OK!"
End Sub

koing12383 2014-01-31

打赏
举报

Dim xmlHttp As Object Set xmlHttp = CreateObject("MSXML2.XMLHTTP") If Not IsObject(xmlHttp) Then Set xmlHttp = CreateObject("Microsoft.XMLHTTP") If Not IsObject(xmlHttp) Then Exit Function End If xmlHttp.Open "GET", sURL, True xmlHttp.Send

JSON网页数据抓取

相关推荐