Script to extract data from some Excel tables and write the data to CSV

excel-extract-commands.sh

find /Volumes/Share/Interimsausleihe/Ruecknahme -name "*xls" -print0 | xargs -0 -I FILE excel-extract.py "FILE" | grep -v "^\w*$" > /Volumes/Share/Porst/LBS-CSV/ruecknahme.csv
find /Volumes/Share/Interimsausleihe/Ausleihe -name "*xls" -print0 | xargs -0 -I FILE excel-extract.py "FILE" | grep -v "^\w*$" > /Volumes/Share/Porst/LBS-CSV/ausleihe.csv
cat /Volumes/Share/Porst/LBS-CSV/ausleihe.csv | csvprintf -s "," '%2$s\n' > /Volumes/Share/Porst/LBS-CSV/ausleihe-barcodes.text
cat /Volumes/Share/Porst/LBS-CSV/ruecknahme.csv | csvprintf -s "," '%1$s\n' > /Volumes/Share/Porst/LBS-CSV/ruecknahme-barcodes.text
cat /Volumes/Share/Porst/LBS-CSV/*barcodes.text | sort | uniq -d > /Volumes/Share/Porst/LBS-CSV/barcode-duplikate.text

excel-extract.py

#!/usr/bin/env python
from mmap import mmap,ACCESS_READ
from xlrd import open_workbook
import sys
import pprint
import io
import csv
import re

filenames = sys.argv

output = io.BytesIO()
writer = csv.writer(output, dialect='excel', delimiter=',', quotechar='"', quoting=csv.QUOTE_ALL)
# writer.writerow(['Spalte 1', 'Spalte 2', 'Ausleihbefehl', 'Ruecknahmebefehl', 'Spalte 1 Original', 'Spalte 2 Original', 'Blattname', 'Dateipfad'])

for filename in sys.argv[1:]:
	w = open_workbook(filename)
	for s in w.sheets():
		count = 1
		for row in range(s.nrows):
			if row > 2 and s.cell(row,0).value != '':
				values = []
				
				# Spalte 1
				cellOriginal1 = str(s.cell(row,0).value)
				cell1 = cellOriginal1
				if cell1[-2:] == '.0':
					cell1 = '000' + re.sub('\.0', '', cell1)
				if len(cell1) > 0 and len(cell1) < 9 and cell1[0] == '0':
					cell1 = cell1.rjust(9, '0')
				values.append(cell1)
				
				#Spalte 2
				cell2 = str(s.cell(row,1).value)
				cellOriginal2 = cell2
				if cell2[-2:] == '.0':
					cell2 = re.sub('\.0', '', cell2)
				if len(cell2) > 0 and len(cell2) < 9 and cell2[0] == '0':
					cell2 = cell2.rjust(9, '0')
				values.append(cell2)
				
				ausleihbefehl = ".T " + cell1 + " " + cell2 + "----.COM lei n " + cell1 + " b " + cell2 + "----.ENT"
				values.append(ausleihbefehl)
				ruecknahmebefehl = ".T " + cell1 + "----.COM rue b " + cell1 + "----.ENT"
				values.append(ruecknahmebefehl)

				values.append(cellOriginal2)
				values.append(cellOriginal1)
				values.append(s.name)
				values.append(filename.split('/')[-1])

				if len(cell1.strip()) > 1:
					writer.writerow(values)
			count = count + 1

	print(output.getvalue())