John Liu Blog

Every drop counts

Convert Excel to CSV

This Python converts Excel worksheets to CSV comma delimited format and combine all worksheets into a single file with worksheet name as the section name. # pip install pandas openpyxl import pandas as pd # Path to your Excel file excel_file = "your excel file.xlsx" # Output text file output_file = "combined_output.csv" # Read the Excel file xls = pd.ExcelFile(excel_file) with open(output_file, "w", encoding="utf-8") as f: for sheet_name in xls.sheet_names: f.

Posted by John Liu Friday, August 15, 2025

Extract Excel Columns

You might want to grab all columns from all worksheets in an Excel. This simple Python script will help to do that. # pip install pandas openpyxl import pandas as pd # Path to your Excel file excel_file = "your excel file.xlsx" # Read the Excel file xls = pd.ExcelFile(excel_file) # Initialize an empty list to store tuples sheet_column_pairs = [] # Loop through each sheet and get column names for sheet_name in xls.

Posted by John Liu Friday, August 15, 2025

Power Automate Remove Elements from JSON Array

In Power Automate, when processing JSON array, you might need to remove unwanted elements from the JSON array. When you read an Excel worksheet from OneDrive or SharePoint, the result is a JSON array and two internal elements (@odata.etag and ItemInternalId) will be added into the result JSON array. To remove the @odata.etag and ItemInternalId from the result array, add a Select data operation action. Set the From value to be expression:

Posted by John Liu Monday, July 29, 2024

Excel Tips

Date/Datetime When using Power Automate to extract Excel data into CSV, the date value might be converted to a numeric value. That numeric value is the date difference between 30/DEC/1899 and the actual date value. If the numeric value is a integer, that represent a date (or time portion all 0s). If the numeric value is a float/decimal, that represent a datetime value. So, if you need to convert that integer value back to it’s original date value in SQL:

Posted by John Liu Saturday, July 27, 2024

Power BI Excel file

Excel can be used as data source for Power BI report. When the Excel file is on SharePoint or OneDrive, using SharePoint data source might have performance issue when it tries to navigate through the SharePoint folder/files. We can use Web data source to connect to the Excel file directly to eliminate the overhead with folder/file navigation. If you used the copy path option to get a direct link to the file from OneDrive/SharePoint you can use the URL as is.

Posted by John Liu Thursday, July 20, 2023