pandas - Python: how import excel file from the web?

Question

Welcome To Ask or Share your Answers For Others

pandas - Python: how import excel file from the web?

posted Oct 7, 2021 in Technique[技术] by 深蓝 (71.8m points)

pandas - Python: how import excel file from the web?

I need to import excel file from the link. I tried to do it with

filedlurl = 'https://www.nordpoolgroup.com/48d3ac/globalassets/marketdata-excel-files/exchange-ee-connections_2021_daily.xls'
    
df = pd.read_excel(filedlurl, skiprows=2)

But the error was XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'Exchange'

then I found the following way from using Pandas to read in excel file from URL - XLRDError

df = pd.read_csv('https://www.nordpoolgroup.com/48d3ac/globalassets/marketdata-excel-files/exchange-ee-connections_2021_daily.xls',
                 #sep='',
                 #parse_dates=[0],
                 names=['a','b','c','d','e','f'],
                skiprows=2)
df

Here is also a strange output. How can I get the following table as when manually download from the web?

question from:https://stackoverflow.com/questions/65598488/python-how-import-excel-file-from-the-web

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…

1 Reply

深蓝 · Answer 1 · 2021-10-06T18:52:47+0000

Your file isn't a CSV or an Excel file. Actual contents are an HTML table (see as follows).

Exchange in {0}, Import(+)/Export(-)
<html>
    <body>
        <table>
            <thead>
                <tr>
                    <td colspan="5">Exchange EE connections in MWh, MW</td>
                </tr><tr>
                    <td colspan="5">Data was last updated 06-01-2021</td>
                </tr><tr>
                    <td></td><td style="text-align:center;">EE net exchange</td><td style="text-align:center;">EE - FI</td><td style="text-align:center;">EE - LV</td><td style="text-align:center;">EE - RU</td>
                </tr>
            </thead><tbody>
                <tr>
                    <td style="text-align:left;">01-01-2021</td><td style="text-align:right;">14575</td><td style="text-align:right;">20969,0</td><td style="text-align:right;">-4884,0</td><td style="text-align:right;">-1510,0</td>
                </tr><tr>
                    <td style="text-align:left;">02-01-2021</td><td style="text-align:right;">12073</td><td style="text-align:right;">22479,0</td><td style="text-align:right;">-8001,0</td><td style="text-align:right;">-2405,0</td>
                </tr><tr>
                    <td style="text-align:left;">03-01-2021</td><td style="text-align:right;">14321</td><td style="text-align:right;">22540,0</td><td style="text-align:right;">-8259,0</td><td style="text-align:right;">40,0</td>
                </tr><tr>
                    <td style="text-align:left;">04-01-2021</td><td style="text-align:right;">14662</td><td style="text-align:right;">17653,0</td><td style="text-align:right;">-5829,0</td><td style="text-align:right;">2838,0</td>
                </tr><tr>
                    <td style="text-align:left;">05-01-2021</td><td style="text-align:right;">13570</td><td style="text-align:right;">13779,0</td><td style="text-align:right;">-5314,0</td><td style="text-align:right;">5105,0</td>
                </tr><tr>
                    <td style="text-align:left;">06-01-2021</td><td style="text-align:right;">6243</td><td style="text-align:right;"></td><td style="text-align:right;"></td><td style="text-align:right;"></td>
                </tr>
            </tbody>
        </table>
    </body>
</html>

Use pd.read_html like so:

import pandas as pd

url = 'https://www.nordpoolgroup.com/48d3ac/globalassets/marketdata-excel-files/exchange-ee-connections_2021_daily.xls'
dfs = pd.read_html(url)
df = dfs[0]

That you can open your file in Excel is because Excel iterates through possible formats until finding something that works. Eg you can make a tab separated values (which should have extension .tsv) file, append .xls and while it isn't an actual horrible spread sheet format (XLS), Excel will still open it normally. It also does this with HTML data.

Categories

pandas - Python: how import excel file from the web?

pandas - Python: how import excel file from the web?

Please log in or register to add a comment.

Please log in or register to reply this article.

1 Reply

Please log in or register to add a comment.

Just Browsing Browsing

Most popular tags