This article provides an overview to get started with the Microsoft Excel connector in Peliqan. Please contact support if you have any additional questions or remarks.

Contents

• Contents
• Connect
• Explore & Combine
• Activate
• Update rows
• Read the data of a sheet
• Read the data of any Excel file into a dataframe
• Upload an Excel file in Peliqan and store in a table
• Tips & Trick

Connect

In peliqan, go to Connections, click on Add new. Find Microsoft Excel in the list and select it. Click on the Connect button. This will open Microsoft Excel and allow you to authorize access for Peliqan. Once the authorization is done, you will return to Peliqan.

Explore & Combine

Wait a few minutes for the data to start syncing. Now you can view your Microsoft Excel data in tables in Peliqan’s built-in data warehouse (or in your own DWH if you connected e.g. SQL Server, Snowflake, Redshift or BigQuery).

Select “Explore” in the left navigation pane, expand “Data warehouse” in the left tree and click on Excel. All tables from Excel will now be shown.

You can explore the data in the gridview, and you can write SQL queries to transform the data and to combine the data from Microsoft Excel with data from other sources.

Activate

Here are example low-code Python scripts in Peliqan to work with Microsoft Excel 365.

Update rows

excel = pq.connect("Microsoft Excel 365 Writeback")

data = [
  ["abc", "def"],
  [123, 456]
]

result = excel.update("rows", 
	item_id = "2FB756A6F54BC7FB!741",  # id of the Excel file on Drive
	worksheet_name = "Sheet1",
	start_cell = "A1",
	end_cell = "B2", 
	values = data)

st.json(result)

Read the data of a sheet

Example to get data from sheets of the file that was entered when adding the connection under “My Connections”:

excel_api = pq.connect('Microsoft Excel 365') # Connection is linked to one Excel file

# Get full sheet
result = excel_api.get('worksheet_values', sheet_name = "Sheet1")
st.json(result)

# Get range on sheet
result = excel_api.get('worksheet_range_values', range = "A1:C4", sheet_name = "Sheet2")
st.json(result)

Read the data of any Excel file into a dataframe

import pandas as pd
import base64

# How to find the drive_id:
# See first part of the file_id (before "!") or copy "cid" from the OneDrive URL in your browser
drive_id = "2fe749a6f54bc7fb"

# How to find the item_id:
# See "id" or "resid" in the URL of the Excel file in your browser
item_id = "2fe749a6f54bc7fb!745"

excel_api = pq.connect('Microsoft Excel 365')

file_content_base64 = excel_api.get('worksheet_content', 
		worksheet_drive_id = drive_id,
    worksheet_drive_item_id =item_id)

file_content = base64.b64decode(file_content_base64['base64'])
df = pd.read_excel(file_content)
st.dataframe(df)

Upload an Excel file in Peliqan and store in a table

See:

Manual file uploads

Tips & Trick

Convert a column number into the Excel column name (e.g. column 27 is AA):

def int_to_col_name(n):
    col_str = ""
    while n > 0:
        n, remainder = divmod(n - 1, 26)
        col_str = chr(65 + remainder) + col_str
    return col_str