Programmatically Extract Data from an Excel Spreadsheet

Extract data from text file to Excel

you have to clear the variable text at beginning of the loop

For i = 1 To k
    Text = ""
    fileStringBasic = Application.GetOpenFilename()

Extract images from Excel file with python

I found a solution using openpyxl and openpyxl-image-loader modules

# installing the modules
pip3 install openpyxl
pip3 install openpyxl-image-loader

Then, in the script :

#Importing the modules
import openpyxl
from openpyxl_image_loader import SheetImageLoader

#loading the Excel File and the sheet
pxl_doc = openpyxl.load_workbook('myfile.xlsx')
sheet = pxl_doc['Sheet_name']

#calling the image_loader
image_loader = SheetImageLoader(sheet)

#get the image (put the cell you need instead of 'A1')
image = image_loader.get('A1')

#showing the image
image.show()

#saving the image
image.save('my_path/image_name.jpg')

In the end, I can store the path and the image name in my dictionaries in a loop for each row

How do I programatically interface an Excel spreadsheet?

We're reading and manipulating Excel-Data via Apache POI, which is not complete in decoding Excel files (namely formula cells are not completely supported) but our customers are quite happy with us.

POI is a Java Library, so if you are a pure Windows shop there may be other more natural options, but as I said, our experience with POI is very good, people are happy.

Additionally: I believe to have heard of Excel ODBC drivers - maybe this is what you want/need? (Sorry, I've never worked with them)

How do I extract data from multiple text files to Excel using Python? (One file's data per sheet)

If you're not opposed to having the outputted excel file as a .xlsx rather than .xls, I'd recommend making use of some of the features of Pandas. In particular pandas.read_csv() and DataFrame.to_excel()

I've provided a fully reproducible example of how you might go about doing this. Please note that I create 2 .txt files in the first 3 lines for the test.

import pandas as pd
import numpy as np
import glob

# Creating a dataframe and saving as test_1.txt/test_2.txt in current directory
# feel free to remove the next 3 lines if yo want to test in your directory
df = pd.DataFrame(np.random.randn(10, 3), columns=list('ABC'))
df.to_csv('test_1.txt', index=False)
df.to_csv('test_2.txt', index=False)

txt_list = [] # empty list
sheet_list = [] # empty list

# a for loop through filenames matching a specified pattern (.txt) in the current directory
for infile in glob.glob("*.txt"): 
    outfile = infile.replace('.txt', '') #removing '.txt' for excel sheet names
    sheet_list.append(outfile) #appending for excel sheet name to sheet_list
    txt_list.append(infile) #appending for '...txt' to txtt_list

writer = pd.ExcelWriter('summary.xlsx', engine='xlsxwriter')

# a for loop through all elements in txt_list
for i in range(0, len(txt_list)):
    df = pd.read_csv('%s' % (txt_list[i])) #reading element from txt_list at index = i 
    df.to_excel(writer, sheet_name='%s' % (sheet_list[i]), index=False) #reading element from sheet_list at index = i 

writer.save()

Output example:

---

Related Topics