5

I have several .iqy files that query data from Sharepoint. I need to combine and process these in Python Pandas. Does Python have any way of doing this? I know the Python Sharepoint library exists, but I'm trying to avoid setting up my own connections via Python and rely on the .iqy files instead. Any ideas?

For sake of the question, assume the table looks like this:

+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+

Also I'm open to non-Python solutions for a way to automatically run the .iqy queries and get the data into a Python-readable format (e.g .csv). Not sure what such an approach looks like though

tgordon18
  • 1,562
  • 1
  • 19
  • 31
  • Yes, `pandas` can read any file as long as it is a tabular format. But I don't know what your "iqy" file looks like (since you haven't shown me) and hence, cannot help you. – cs95 Jan 09 '18 at 16:19
  • 1
    The .iqy file contains a query. You can open it in Excel and it will query the data into a table format (see my new edit). I'm looking for a way to both execute the query and read in that data into Pandas – tgordon18 Jan 09 '18 at 16:59
  • As your 'open to non-Python solutions': Couldn't you create a XLSM (or XLSB) file with a VBA function that opens the .iqy file(s) which in turn would execute the query and open / save the CSV file(s) for further processing? – Joe Phi Jul 30 '18 at 12:30

1 Answers1

1

Here is a little hacky-way:

import requests_ntlm
import requests

url = 'https://..../owssvr.dll?XMLDATA=...'
print('downloading...')
r = requests.get(url, auth=requests_ntlm.HttpNtlmAuth(LOGIN, PASSWORD), verify=False)

print('saving...')
with open('download.xml', 'wb') as file:
    file.write(r.content)

A few remarks:

  1. url is in *.iqy file (typically is very long). You can extract it manually by "notepad" or parse it by python.
  2. You'll get xml file, to convert it to pandas dataframe look at: How to convert an XML file to nice pandas dataframe?
Quant Christo
  • 1,275
  • 9
  • 23
  • 1
    I got an error "403 Forbidden", maybe because my access to Sharepoint is behind a SSO with MFA. Not easy – Jean-Francois T. May 06 '21 at 09:32
  • @Jean-FrancoisT.maybe this helps with 403: https://sharepoint.stackexchange.com/questions/229517/rest-api-403-forbidden-when-trying-to-get-formdigest-in-python – Quant Christo May 06 '21 at 11:52