Python pandas 模块，ExcelFile() 实例源码

def load_file(file_name, sheet_name_list):
    '''
    load xlsx file into a dictionary indexed by sheet names
    :param string file_name:name of file
    :param [string] sheet_name_list: name of selected sheets in the xlsx file
    :return: {string:DataFrame} raw_data: {name of sheet:pure data retrieved from xlsx
    with column and index 0,1,2,...}
    '''
    print 'loading file...'
    cut_head = 2
    file = pd.ExcelFile(file_name)
    raw_data = {}
    # iterate over every sheet and retrieve useful data into raw_data
    for i in range(len(sheet_name_list)):
        print 'parsing sheet', sheet_name_list[i]
        # parse a sheet from the whole file into a DataFrame with headers cut off
        temp = file.parse(sheet_name_list[i]).iloc[cut_head:, :]
        # now temp.dtype = object,because the data read in contains string.Here convert it to float
        temp = temp.astype(np.float)
        # reset index and column with 0,1,2,...,
        temp.columns = range(temp.shape[1])
        temp.index = range(temp.shape[0])
        temp.fillna(0, inplace=True)
        raw_data[sheet_name_list[i]] = temp
    return raw_data

def read_data(file):
    """Read data about the system from the excel file and assign 
    it to different parameters

    Args:
            file: excel file, which stores all data about considered system

    Returns:
            cst: constants, which describe the system
            srs: parameters, which are variable with time
            U: list of possible decisions
            states: values, which set number of states and characterize 
            all possible ones
    """

    xls = pd.ExcelFile(file)
    states = xls.parse('DP-States',index_col=[0])
    cst = xls.parse('Constants',index_col=[0])['Value']
    srs = xls.parse('Time-Series',index_col=[0])
    U = xls.parse('DP-Decisions',index_col=[0])['Decisions'].values
    return cst,srs,U,states

###################################
#for 2 states - temperature and heat-storage
###################################

def getExcelData(self):
        """
        get data from 'hsi_futures.xlsx'
        Date | Open | High | Low | Close | SMAVG5 | SMAVG10 | SMAVG15 | Volume | VolumeSMAVG5
        :return: data table
        """
        df = pd.DataFrame()
        xl = pd.ExcelFile("../dataManager/hsi_futures.xlsx")
        # print xl.sheet_names
        sheets = xl.sheet_names
        for sheet in sheets:
            df = df.append(pd.read_excel("../dataManager/hsi_futures.xlsx", sheet))
        df['Date'] = pd.to_datetime(df['Date'])
        df.sort_values("Date", ascending=True, inplace=True)
        data = df.set_index([range(df.shape[0])])
        return data

def get_clinical_template(study):
    """Try to detect the clinical template file in the source dir and open it with pandas."""
    clinical_templates = [template for template in study.excel_files if "clin" in template.lower() and
                          "~$" not in template]

    clinical_template = None
    if len(clinical_templates) == 1:
        clinical_template_name = clinical_templates[0]
        clinical_template = pd.ExcelFile(clinical_template_name, comment="#")
        print("[INFO] Clinical data template detected: " + clinical_template_name)
    elif len(clinical_templates) == 0:
        study.clinical_template_present = False
        print("[WARNING] No clinical data template could be detected. Assuming only high-dimensional templates " +
              "are available.")
    else:
        print("[ERROR] Too many clinical data templates were found. " +
              "Make sure only one file has 'clinical' in its name.")
        Validity.list_length(clinical_templates, expected=1)

    return clinical_template

def add_general_study_metadata(study, study_metadata_template_path):
    """Read the data from general study level metadata template and write to tags file."""
    metadata = pd.ExcelFile(study_metadata_template_path, comment="#")

    if len(metadata.sheet_names) > 1:
        print("[WARNING] Multiple sheets detected in general study metadata template. Assuming first sheet.")
    df = metadata.parse(0, header=None)
    tag_index = 10
    for __, row in df.iterrows():
        data = row[row.first_valid_index():].dropna().tolist()
        if len(data) == 2:
            tag = data[0]
            value = data[1]
            study.all_metadata.add(("\\", tag, value, tag_index))
            tag_index += 1
    study.write_metadata()

def read_excel(filename):
    """Read input Excel file and return dict of DataFrames for each sheet.

    Read an Excel spreadsheet with geographic input data.

    Args:
        filename: filename to an Excel spreadsheet with 'Vertex' and 'Edge'

    Returns:
        dict of 2 pandas DataFrames
    """
    with pd.ExcelFile(filename) as xls:
        vertex = xls.parse('Vertex').set_index('Vertex')
        edge = xls.parse('Edge').set_index(['Edge', 'Vertex1', 'Vertex2'])
    data = {
        'Vertex': vertex,
        'Edge': edge}
    return data

def _compare_xlsx(self, file1, file2, rtol=1e-02, atol=1e-03):
#        print("requested compare: {} and {}".format(file1, file2))
        xl1 = pd.ExcelFile(file1)
        xl2 = pd.ExcelFile(file2)
        self.assertEqual(xl1.sheet_names, xl2.sheet_names)

        for sheet in xl1.sheet_names:
#            print("Prrocessing sheet {}".format(sheet))
            df1 = xl1.parse(sheet)
            df2 = xl2.parse(sheet)
            columns1 = list(df1)
            columns2 = list(df2)
            self.assertEqual(len(columns1), len(columns2))
            arr1 = df1.values
            arr2 = df2.values

            self.assertEqual(arr1.shape, arr2.shape)
            for x, y in np.ndindex(arr1.shape):
                v1 = arr1[x, y]
                v2 = arr2[x, y]
#                print("{}: ({}, {}): {} vs {}".format(sheet, x, y, v1, v2))
                if isinstance(v1, six.string_types) or isinstance(v2, six.string_types):
                    self.assertEqual(v1, v2)
                else:
                    npt.assert_allclose(v1, v2, rtol=rtol, atol=atol)

def parse_xls(fpath):
    '''
    load the xls data.
    export: a data frame (in pandas)
    '''
    book = xlrd.open_workbook(fpath)
    sheet_names = book.sheet_names()
    print("sheet_names:", sheet_names)
    xl_sheet = book.sheet_by_name(sheet_names[0])
    title_row = xl_sheet.row_values(0) # title row
    print(title_row)
    xls_file = pd.ExcelFile(fpath)
    sht1 = xls_file.sheet_names[0]
    df = xls_file.parse(sht1)
    print(df.ix[0])
    print(df.ix[1])
    print(df[3:])

def load_supervision_authorities(self, *args, **options):
        excel_file = pd.ExcelFile(options['filename'])
        state_names = excel_file.sheet_names

        for state_name in state_names:
            state = State.objects.get(name=state_name)
            df = excel_file.parse(state_name)
            for _, row in df.iterrows():
                try:
                    email = stringify(row['email'])
                    if email:
                        email = email.splitlines()[0]
                        email = EMAIL_RE.search(email)
                        email = email.group(0).strip() if email is not None else ''
                        email = email.lower()
                    authority, created = SupervisionAuthority.objects.update_or_create(
                        state=state, name=stringify(row['name']), defaults=dict(
                            address=stringify(row['address']),
                            contact=stringify(row['contact']),
                            email=email,
                            url=stringify(row['url']),
                            report_url=stringify(row.get(u'Verfügbare Berichte', ''))
                        )
                    )
                    if created:
                        print(authority)
                except Exception:
                    print(row['name'])
                    raise

def assign_brandenburg(self, *args, **options):
        brandenburg_state = State.objects.get(name='Brandenburg')
        excel_file = pd.ExcelFile(options['filename'])
        df = excel_file.parse('Brandenburg')
        assigned_auths = defaultdict(list)
        locations = {}
        for _, row in df.iterrows():
            auth = SupervisionAuthority.objects.get(state=brandenburg_state, name=row['name'])
            locations[auth] = GEOSGeometry('POINT(%f %f)' % (row['lng'], row['lat']), srid=4326)
            assigned_districts = row[u'Landkreis-Zuständigkeit'].splitlines()
            for district_name in assigned_districts:
                districts = District.objects.filter(part_of=brandenburg_state, name=district_name)
                if len(districts) != 1:
                    print(district_name)
                    print(districts)
                else:
                    assigned_auths[districts[0]].append(auth)

        for nursinghome in NursingHome.objects.filter(supervision_authority__isnull=True,
                state=brandenburg_state):
            district = District.objects.get(geom__covers=nursinghome.geo)
            auths = assigned_auths[district]
            if len(auths) == 1:
                nursinghome.supervision_authority = auths[0]
                nursinghome.save()
            else:
                min_distance = None
                best_auth = None
                for auth, point in locations.items():
                    if auth not in auths:
                        continue
                    dist = NursingHome.objects.filter(pk=nursinghome.pk
                            ).annotate(distance=Distance('geo', point))
                    dist = dist[0].distance.m
                    if min_distance is None or dist < min_distance:
                        min_distance = dist
                        best_auth = auth
                nursinghome.supervision_authority = best_auth
                nursinghome.save()

def assign_rheinlandpfalz(self, *args, **options):
        rp_state = State.objects.get(name='Rheinland-Pfalz')
        excel_file = pd.ExcelFile(options['filename'])
        df = excel_file.parse('Rheinland-Pfalz')
        assigned = defaultdict(list)
        for _, row in df.iterrows():
            auth = SupervisionAuthority.objects.get(state=rp_state, name=row['name'])
            district_names = row[u'Landkreis-Zuständigkeit'].splitlines()
            for district_name in district_names:
                only = None
                if '|' in district_name:
                    district_name, only = district_name.split('|')
                    only = only.split(',')

                districts = District.objects.filter(part_of=rp_state, name=district_name)
                if len(districts) == 0:
                    districts = District.objects.filter(part_of=rp_state, name__contains=district_name)
                if len(districts) == 0:
                    districts = District.objects.filter(part_of=rp_state, name__contains=district_name.split()[0])
                if len(districts) == 0:
                    districts = District.objects.filter(part_of=rp_state, name__istartswith=re.sub('\W', '', district_name))
                if len(districts) > 1:
                    if 'Kreis' in district_name:
                        districts = districts.filter(kind_detail__contains='Landkreis')
                    if 'Stadt' in district_name:
                        districts = districts.filter(kind_detail__contains='Stadt')
                if len(districts) != 1:
                    print(districts)
                    print(u'District not one: %s' % district_name)
                    continue
                assigned[auth].append((districts[0], only))
        for auth, district_list in assigned.items():
            for district, only in district_list:
                if only is None:
                    NursingHome.objects.filter(state=rp_state, district=district, supervision_authority__isnull=True).update(supervision_authority=auth)
                    continue
                for muni_name in only:
                    muni_name = muni_name.strip()
                    munis = Municipality.objects.filter(part_of=district, name__contains=muni_name)
                    if len(munis) > 1:
                        munis = Municipality.objects.filter(part_of=district, name=muni_name)
                    if len(munis) != 1:
                        print('Did not find %s' % muni_name)
                        continue
                    muni = munis[0]
                    NursingHome.objects.filter(state=rp_state, district=district, supervision_authority__isnull=True, geo__coveredby=muni.geom).update(supervision_authority=auth)

def read_data(file):
    """Read data about the system from the excel file and assign 
    it to different parameters

    Args:
            file: excel file, which stores all data about considered system

    Returns:
            cst: constants, which describe the system
            srs: parameters, which are variable with time
            U: list of possible decisions
            states: values, which set number of states and characterize 
            all possible ones
    """

    xls = pd.ExcelFile(file)
    states = xls.parse('DP-States',index_col=[0])
    cst = xls.parse('Constants',index_col=[0])['Value']
    srs = xls.parse('Time-Series',index_col=[0])
    U = xls.parse('DP-Decisions',index_col=[0])['Decisions'].values
    return cst,srs,U,states



###################################
#for 2 states
###################################

def read_data(file):

    xls = pd.ExcelFile(file)
    cst = xls.parse('Constants',index_col=[0])['Value']
    srs = xls.parse('Time-Series',index_col=[0])
    U = xls.parse('DP-Decisions',index_col=[0])['Decisions'].values
    states = xls.parse('DP-States',index_col=[0])
    return cst,srs,U,states

def read_data(file):

    xls = pd.ExcelFile(file)
    cst = xls.parse('Constants',index_col=[0])['Value']
    srs = xls.parse('Time-Series',index_col=[0])
    U = xls.parse('DP-Decisions',index_col=[0])['Decisions'].values
    states = xls.parse('DP-States',index_col=[0])
    return cst,srs,U,states

def read_data(file):
    """Read data about the system from the excel file and assign 
    it to different parameters

    Args:
            file: excel file, which stores all data about considered system

    Returns:
            cst: constants, which describe the system
            srs: parameters, which are variable with time
            U: list of possible decisions
            states: values, which set number of states and characterize 
            all possible ones
    """

    xls = pd.ExcelFile(file)
    states = xls.parse('DP-States',index_col=[0])
    cst = xls.parse('Constants',index_col=[0])['Value']
    srs = xls.parse('Time-Series',index_col=[0])
    U = xls.parse('DP-Decisions',index_col=[0])['Decisions'].values
    return cst,srs,U,states



###################################
#for 1 state - temperature
###################################

def get_sanitized_dataframe(self, date_column='Date'):
        # ExcelFile does not handle file_paths very well, use native Python open
        opened_file = open(self.file_path, 'rb')
        excel_file = pd.ExcelFile(opened_file)
        dataframe = excel_file.parse(self.sheet)

        # Sanitize so the inputs are correct and remove fluke days
        dataframe = self._sanitize_sheet(dataframe)
        dataframe = self._set_dataframe_index(dataframe, date_column)
        return dataframe

def loadFile(self, filename):
        if filename.endswith('.xls') or filename.endswith('.xlsx'):
            df=pd.ExcelFile(filename)
            sheetnames=df.sheet_names
            dialog=ExcelFileDialog(filename,sheetnames,self)
            dialog.accepted.connect(self.loadExcel)
            dialog.show()
        else:
            table = QTableView()
            table.setAlternatingRowColors(True)
            model=DataFrameTableModel(filename=filename)
            model.trackDataChange.connect(self.trackChanges)
            table.setModel(model)
            ### Set some variables ###
            table.headers = table.horizontalHeader()
            table.vHeaders=table.verticalHeader()
            #### Set context menu for table headers ####
            table.headers.setContextMenuPolicy(Qt.CustomContextMenu)
            table.headers.customContextMenuRequested.connect(self.headerMenu)
            table.vHeaders.setContextMenuPolicy(Qt.CustomContextMenu)
            table.vHeaders.customContextMenuRequested.connect(self.vHeaderMenu)
            if model._filename.endswith('.csv'):
                df=pd.read_csv(model._filename)
                model.setDataFrame(df)
                self.tableTabWidget.addTab(table, QFileInfo(model._filename).fileName())
                self.tableTabWidget.setCurrentWidget(table)
            if model._filename.endswith('.txt'):
                delim = str(self.parseDelimiter(model._filename))
                if delim == ' ':
                    df=pd.read_csv(model._filename,delim_whitespace = True)
                else:
                    df=pd.read_csv(model._filename,sep=delim)
                model.setDataFrame(df)
                self.tableTabWidget.addTab(table, QFileInfo(model._filename).fileName())
                self.tableTabWidget.setCurrentWidget(table)

def read_excel(fname, header=None):
    """Read excel into dict.
    Args:
        fname: name of excel file
        header: The finland files does not have a header
    Output:
        dictionary containing the data
    """
    xls = ExcelFile(fname)
    if header:
        parse_cols = [1]
    else:
        parse_cols = None

    df = xls.parse(xls.sheet_names[0], skiprows=1,
                   parse_cols=parse_cols)

    # Fix keys
    temp = df.to_dict()
    for key in temp:
        new_key = key.replace(" - ", "_")
        temp[new_key] = temp.pop(key)
    # Stupid hack for Finland
    if header:
        temp[header] = temp.pop(temp.keys()[0])

    return temp

def importData(file):
    try:
        fileData = pd.ExcelFile(file)
    except:
        try:
           fileData = pd.read_csv(file)
        except:
            print("Please provide an excel or csv file")
    return fileData

#Load for not writing all the time

def setUp(self):
        self.data = {
            "DC_PEC": "import pandas as pd; from urllib.request import urlretrieve; urlretrieve('https://s3.amazonaws.com/assets.datacamp.com/production/course_998/datasets/battledeath.xlsx', 'battledeath.xlsx')",
            "DC_SOLUTION": "xl = pd.ExcelFile('battledeath.xlsx')",
            "DC_SCT": "test_object('xl')"
        }

def test_step_1(self):
        self.data["DC_CODE"] = "xl = pd.ExcelFile('battledeath.xlsx')"
        sct_payload = helper.run(self.data)
        self.assertTrue(sct_payload['correct'])

def test_pass_1(self):
        self.data = {
            "DC_PEC": "import pandas as pd; from urllib.request import urlretrieve; urlretrieve('https://s3.amazonaws.com/assets.datacamp.com/production/course_998/datasets/battledeath.xlsx', 'battledeath.xlsx'); from urllib.request import urlretrieve; urlretrieve('https://s3.amazonaws.com/assets.datacamp.com/production/course_998/datasets/battledeath.xlsx', 'battledeath2.xlsx')",
            "DC_SOLUTION": "xl = pd.ExcelFile('battledeath.xlsx')",
            "DC_CODE": "xl = pd.ExcelFile('battledeath2.xlsx')",
            "DC_SCT": '''
def my_converter(x):
    return(x.sheet_names)
set_converter(key = "pandas.io.excel.ExcelFile", fundef = my_converter)
test_object('xl')
'''
        }
        sct_payload = helper.run(self.data)
        self.assertTrue(sct_payload['correct'])

def unet_cross_val(data_dir, out_dir, mapping, splits, unet_conf):

    # Load spreadsheet
    with pd.ExcelFile(mapping) as xls:
        df = pd.read_excel(xls, 'Sheet1').set_index('index')
        df['class'] = df['class'].map({'preplus': 'pre-plus', 'normal': 'normal', 'plus': 'plus'})

    img_dir = join(data_dir, 'images')
    seg_dir = join(data_dir, 'manual_segmentations')
    mask_dir = join(data_dir, 'masks')

    # Check whether all images exist
    check_images_exist(df, img_dir, seg_dir, mask_dir)

    # Now split into training and testing
    CVFile = sio.loadmat(splits)

    # # Combining Pre-Plus and Plus
    # trainPlusIndex = CVFile['trainPlusIndex'][0]
    # testPlusIndex = CVFile['testPlusIndex'][0]
    #
    # plus_dir = make_sub_dir(out_dir, 'trainTestPlus')
    # print "Generating splits for combined No and Pre-Plus"
    # generate_splits(trainPlusIndex, testPlusIndex, df, img_dir, mask_dir, seg_dir, plus_dir)

    # Combining No and Pre-Plus
    trainPrePIndex = CVFile['trainPrePIndex'][0]
    testPrePIndex = CVFile['testPrePIndex'][0]

    prep_dir = make_sub_dir(out_dir, 'trainTestPreP')
    print "Generating splits for combined Pre-Plus and Plus"
    generate_splits(trainPrePIndex, testPrePIndex, df, img_dir, mask_dir, seg_dir, prep_dir)

    # Train models
    train_and_test(prep_dir, unet_conf, processes=1)
    # train_and_test(plus_dir, unet_conf, processes=2)

def read_hd_file_template(self, source_dir, hd_template):
        """Try to read the specified template file and send to sheet loading method."""
        template_path = os.path.join(source_dir, hd_template)
        try:
            hd_template_workbook = pd.ExcelFile(template_path, comment="#", dtype=object)
        except FileNotFoundError:
            raise Validity.TemplateException("Could not find high-dim template file at: {0}".format(template_path))
        # except XLRDError:
        #     raise Validity.TemplateException(
        #         "High-dim template file at: {0} is not a valid xlsx file.".format(template_path))

        self._load_sheets(hd_template_workbook)

def load_data(fname='SeatTest_New.xlsx'):
    """
    Load the xlsx using pandas.
    :param fname: string location of the file to load
    :return: pandas object
    """
    return pd.ExcelFile(fname)

def parse_statistics(logfile):
    xl = pd.ExcelFile(logfile)
    df = xl.parse("Sheet")
    df = df.sort_values(by='Line Numbers')

    writer = pd.ExcelWriter(logfile)
    df.to_excel(writer, sheet_name='Sheet', index=False)
    writer.save()

    wb = openpyxl.load_workbook(logfile)
    ws = wb.active

    row_count = ws.max_row
    column_count = ws.max_column

    chart = ScatterChart()
    chart.title = "Time upload domain names"
    chart.style = 13
    chart.x_axis.title = "Line numbers"
    chart.y_axis.title = "Time, sec"

    xvalues = Reference(ws, min_col=1, min_row=2, max_row=row_count)
    color_choice = ['3F888F', 'D24D57']
    for i in range(2, column_count + 1):
        values = Reference(ws, min_col=i, min_row=1, max_row=row_count)
        series = Series(values, xvalues, title_from_data=True)
        series.marker.symbol = "diamond"
        series.graphicalProperties.line.solidFill = color_choice[i-2]
        series.marker.graphicalProperties.line.solidFill = color_choice[i-2]
        series.marker.graphicalProperties.solidFill = color_choice[i-2]
        series.graphicalProperties.line.width = 20000
        chart.series.append(series)

    chart.legend.legendPos = 'b'
    ws.add_chart(chart)
    wb.save(logfile)

def loadExcel(self,options):
        names = options['sheets']
        filename = options['file']
        openEach = options['openEach']
        df=pd.ExcelFile(filename)
        if not openEach:
            newTab = QTabWidget()
            newTab.setTabsClosable(True)
            newTab.currentChanged.connect(self.tabChanged)
            self.connect(newTab,SIGNAL("tabCloseRequested(int)"),
                         self.fileCloseInternalTab)
            for i in range(len(names)):
                table = QTableView()
                table.setAlternatingRowColors(True)
                model=DataFrameTableModel(filename=filename)
                model.trackDataChange.connect(self.trackChanges)
                table.setModel(model)
                ### Set some variables ###
                table.headers = table.horizontalHeader()
                table.vHeaders=table.verticalHeader()
                #### Set context menu for table headers ####
                table.headers.setContextMenuPolicy(Qt.CustomContextMenu)
                table.headers.customContextMenuRequested.connect(self.headerMenu)
                table.vHeaders.setContextMenuPolicy(Qt.CustomContextMenu)
                table.vHeaders.customContextMenuRequested.connect(self.vHeaderMenu)

                df2=df.parse(sheetname=names[i])
                model.setDataFrame(df2)
                newTab.addTab(table,names[i])
            newTab.setCurrentIndex(0)
            self.tableTabWidget.addTab(newTab,QFileInfo(filename).fileName())
            self.tableTabWidget.setCurrentWidget(newTab)
        else:
            for i in range(len(names)):
                table = QTableView()
                table.setAlternatingRowColors(True)
                model=DataFrameTableModel(filename=names[i])
                model.trackDataChange.connect(self.trackChanges)
                table.setModel(model)
                ### Set some variables ###
                table.headers = table.horizontalHeader()
                table.vHeaders=table.verticalHeader()
                #### Set context menu for table headers ####
                table.headers.setContextMenuPolicy(Qt.CustomContextMenu)
                table.headers.customContextMenuRequested.connect(self.headerMenu)
                table.vHeaders.setContextMenuPolicy(Qt.CustomContextMenu)
                table.vHeaders.customContextMenuRequested.connect(self.vHeaderMenu)
                df2=df.parse(sheetname=names[i])
                model.setDataFrame(df2)
                self.tableTabWidget.addTab(table,names[i])
                self.tableTabWidget.setCurrentWidget(table)

def metadata_file(path, root, tables=None):
    '''
    Returns the metadata for a file. There are 3 types of file formats:

    1. Archives (7z, zip, rar, tar) / compressed (xz, bzip2, gzip). Decompress and process
    2. Database (sqlite3, hdf5, xls, xlsx). Process each table/sheet as a sub-dataset
    3. Data (csv, json). Process directly
    '''
    tree = Meta()
    format = guess_format(path)
    if format is not None:
        tree.format = format

    if format == 'dir':
        tree.datasets = Datasets()
        for base, dirs, files in os.walk(path):
            for filename in files:
                source = os.path.join(base, filename)
                name = os.path.relpath(source, path)
                tree.datasets[name] = submeta = Meta(name=name, source=source)
                try:
                    submeta.update(metadata_file(source, root, tables))
                except Exception as e:
                    submeta['error'] = str(e)
                    logging.exception('Unable to get metadata for %s', source)
    elif format in {'7z', 'zip', 'rar', 'tar', 'xz', 'gz', 'bz2'}:
        tree.datasets = Datasets()
        for name, source in unzip_files(path, root, format):
            tree.datasets[name] = submeta = Meta(name=name)
            try:
                submeta.update(metadata_file(source, root, tables))
            except Exception as e:
                submeta['error'] = str(e)
                logging.exception('Unable to get metadata for %s', source)
    elif format == 'sqlite3':
        tree.update(metadata_sql('sqlite:///' + path, tables))
    elif format in {'hdf5', 'xls', 'xlsx'}:
        if format == 'hdf5':
            store = pd.HDFStore(path)
            table_list = store.keys()
            store.close()
        else:
            xls = pd.ExcelFile(path)
            table_list = xls.sheet_names
            format = 'xlsx'
        tree.datasets = Datasets()
        for table in table_list:
            if tables is None or table in tables:
                tree.datasets[table] = Meta([
                    ('name', table),
                    ('format', 'table'),
                    ('command', [format, path, table])
                ])
    elif format == 'csv':
        tree.command = ['csv', path]
    elif format == 'json':
        tree.command = ['json', path]
    return tree