Import A website data as a table in ms fabric

Have you heard about web scraping before?

You can use tools Python for web scraping with BeautifulSoup.

import requests

from bs4 import BeautifulSoup

import pandas as pd

# URL of the website to scrape

url = 'https://example.com/data'

# Send a request to the website

response = requests.get(url)

# Parse the HTML content

soup = BeautifulSoup(response.content, 'html.parser')

# Extract the data you need

data = []

table = soup.find('table')  # Assuming the data is in a table

for row in table.find_all('tr'):

    cols = row.find_all('td')

    cols = [ele.text.strip() for ele in cols]

    data.append(cols)

# Convert to DataFrame

df = pd.DataFrame(data, columns=['Column1', 'Column2', 'Column3'])

df.to_csv('web_data.csv', index=False)

Then you can transform the data as needed. This step can be done using Python, or you can leverage Azure Synapse or ADF for more complex transformations.

For the data ingestion :

Using Azure Synapse Analytics:

1. Upload Data to Azure Data Lake:
   • Store the CSV file (web_data.csv) in Azure Data Lake.
2. Load Data into Synapse:
   • Use Azure Synapse to create a table from the CSV file stored in Azure Data Lake.

CREATE EXTERNAL DATA SOURCE MyExternalSource

WITH (

    TYPE = HADOOP,

    LOCATION = 'https://mydatalakestorage.blob.core.windows.net/'

);

CREATE EXTERNAL FILE FORMAT MyFileFormat

WITH (

    FORMAT_TYPE = DELIMITEDTEXT,

    FORMAT_OPTIONS (

        FIELD_TERMINATOR = ',',

        STRING_DELIMITER = '"',

        FIRST_ROW = 2

    )

);

CREATE EXTERNAL TABLE MyTable (

    Column1 VARCHAR(50),

    Column2 VARCHAR(50),

    Column3 VARCHAR(50)

)

WITH (

    LOCATION = 'path/to/web_data.csv',

    DATA_SOURCE = MyExternalSource,

    FILE_FORMAT = MyFileFormat

);

Using Azure Data Factory:

1. Create Linked Services:
   • Link to the website (if API available) or the storage account.
2. Create Pipelines:
   • Web scraping can be done using a Python script in Azure Data Factory's custom activities.
   • Ingest the data from the website, transform if needed, and store it in the data lake.
3. Store Data in Azure Data Lake:
   • Define the destination as the Azure Data Lake and load the data.