Merging iqy files with python

Question

currently I extracted data from sharepoint and have .iqy files that I can open with excel. There are about 30 files and I am trying to merge all the information into one .iqy file or excel file with python.

import os, glob
import pandas as pd

files = []
for file in os.listdir("C:\\Users\\CHI86786\\Downloads"):
    files.append(file)

excels = [pd.ExcelFile(name) for name in files]
frames = [x.parse(x.sheet_names[0], header=None, index_col=None) for x in excels]

frames[1:] = [df[1:] for df in frames[1:]]

combined = pd.concat(frames)
combined.to_excel("SPmerged.iqy", header=False, index=False)

took a same approach as if I would merge excel files. but I keep getting an error that reads FileNotFoundError: [Errno 2] No such file or directory: 'desktop.ini'

EDIT

more of the error message

File "C:\Users\CHI\source\repos\MergingExcel\MergingExcel\MergingExcel.py", line 8, in <module>
    excels = [pd.ExcelFile(name) for name in files] #reads names in
  File "C:\Users\CHI86786\source\repos\MergingExcel\MergingExcel\MergingExcel.py", line 8, in <listcomp>
    excels = [pd.ExcelFile(name) for name in files] #reads names in
  File "C:\Users\CHI\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\io\excel.py", line 394, in __init__
    self.book = xlrd.open_workbook(self._io)
  File "C:\Users\CHI\AppData\Local\Programs\Python\Python37-32\lib\site-packages\xlrd\__init__.py", line 116, in open_workbook
    with open(filename, "rb") as f:
FileNotFoundError: [Errno 2] No such file or directory: 'desktop.ini'

The error should be more than that, and should tell you where the problem is in your code. — miken32, Aug 15 '18 at 19:28
I mean it does say where its at but it points at the module or a built in python script (i added more of the error to the original posti) so im not sure where `'desktop.ini'` is coming from — jordan23, Aug 15 '18 at 19:34
FYI, I'd be quite surprised if pd.ExcelFile will handle iqy files, so I don't think this entire approach is going to work. — DSM, Aug 15 '18 at 19:43
FYI, just paste your code into the text box, highlight it, and press Cmd-K (or Ctrl-K on Windows I guess) to indent it for code display. Another option is to put three backticks on a line, paste your content, and then put three more backticks on a separate line. See https://stackoverflow.com/help/formatting — miken32, Aug 15 '18 at 19:48

score 1 · Accepted Answer · answered Aug 15 '18 at 19:42

1

Your code is trying to act on every file in C:\Users\CHI86786\Downloads, including system files like "desktop.ini".

Instead, try restricting it to the files you're interested in using glob:

for file in glob.glob("C:\\Users\\CHI86786\\Downloads\\*.iqy")

answered Aug 15 '18 at 19:42

miken32

42,008
16
111
154

Yup that worked, and showed me that it was an unsupported format. Figured that these files are being opened with excel that I'd be able to take this approach. – jordan23 Aug 15 '18 at 20:10
Didn't know what an IQY file was, thought maybe it was just a CSV file with a proprietary extension or something. – miken32 Aug 15 '18 at 20:12
it is an internet query file. Yeah took me sometime to see find out what it was. I'm going to try and convert them to xlsx and give you guys an update – jordan23 Aug 15 '18 at 20:25

Merging iqy files with python

1 Answers1