1

Is there a way to read an excel table in python - when the table you're reading is actually an excel table connected to SQL Server?

In theory - i'm asking if you can go from a data linked excel file to a python pandas dataframe, pd.read_excel() doesn't seem to work for this.

Che
  • 35
  • 5

2 Answers2

0

From: Reading an Excel file in python using pandas

>>> xl = pd.ExcelFile("dummydata.xlsx")
>>> xl.sheet_names
[u'Sheet1', u'Sheet2', u'Sheet3']
>>> df = xl.parse("Sheet1")
APorter1031
  • 2,107
  • 6
  • 17
  • 38
0

Why not just import your data from SQL Server? What does Excel even have to do with this???

import pypyodbc 
cnxn = pypyodbc.connect("Driver={SQL Server Native Client 11.0};"
                        "Server=Your_Server_Name;"
                        "Database=Your_DB_Name;"
                        "Trusted_Connection=yes;")


cursor = cnxn.cursor()
cursor.execute('SELECT * FROM Customers')

for row in cursor:
    print('row = %r' % (row,))
ASH
  • 20,759
  • 19
  • 87
  • 200
  • I couldn't agree with you more. The hiccup is that I'm working with legacy workbooks that are actually linked to SQL server data without the SQL server credentials. So as I read the excel tables (that were linked to SSMS), pandas weren't reading the tables appropriately. – Che Jun 21 '18 at 14:58