Read a xlxs file from Visualchart data patch into a pandas DataFrame

read_vc_excel.py

from pandas import read_excel, to_datetime
def read_vc_excel(io):
    """Read a xlxs file from Visualchart historical data patch 
    into a pandas DataFrame.
    
    Parameters:
    io : str, file descriptor, pathlib.Path, ExcelFile or xlrd.Book
    The string could be a URL. Valid URL schemes include http, ftp, s3,
    gcs, and file. For file URLs, a host is expected. For instance, a local
    file could be /path/to/workbook.xlsx.

    Returns:
    DataFrame : DataFrame from the passed in Excel file.
    
    Example:
    file.xlsx is a Excel file from ExcelData export tool of VisualChart,
    df = read_vc_excel('/yourpath/file.xlsx')
    df result a pandas DataFrame with the same informatión correctly parsed.
   """
    data = read_excel(io,
            dtype={'Date':'str', 'Time': 'str'})[1:]
    data.set_index(to_datetime(data.Date + ' ' + data.Time.str.zfill(4)), inplace=True)
    data.drop(['Date','Time'], axis=1, inplace=True)
    data.index.name='Datetime'
    return data

Only copy and paste the function into your code, or import the function.

Supose file.xlsx is a Excel file from ExcelData export tool of VisualChart, then

df = read_vc_excel('/yourpath/file.xlsx')

df result a pandas DataFrame with the same information correctly parsed.