Python Script
- or -
Post a project like this2531
$15
- Posted:
- Proposals: 9
- Remote
- #1541416
- Awarded
MATLAB, Python, Hadoop/Big Data, Cloud Computing, NS2, NS3, Scala, Apache Kafka
Chandigarh
14694078168241336198134786213610051366786157798616343301656587
Description
Experience Level: Intermediate
Estimated project duration: 1 day or less
General information for the business: Need to fix script
Description of requirements/functionality: PYTHON 3.6 under Windows
Hi, I need to fix the following script so that it works under python 3.6
The script should read 2 or more excel files in working directory (attached are 2 examples), that are all in the same format, and create a fileMMDDYY.csv output file.
All excel files are in the same format
All excel files have same password (make password a command line variable when calling the script
Please let me know if you have any questions. I'll provide TeamViewer access if we have trouble running the script
Specific technologies required: Python 3.6
OS requirements: Windows
Extra notes: Here is the existing script
#!/usr/bin/python
#importing modules
#Please check that all the modules are installed on your system
import os
from xlrd import *
#import pandas as pd
import csv
from subprocess import call
import numpy as np
import datetime
import glob
import win32com.client
#Functions
#Find excel file names in a given folder
def find_filenames(suffix="xlsx" ):
folder = r"C:\Users\t\Documents\test-python"+ os.sep
os.chdir(folder)
result = [i for i in glob.glob('*.{}'.format(suffix))]
return result
#Will extract all the data
def file_data(fileList):
f=open('output.csv', 'w')
writer = csv.writer(f)
writer.writerow(["SNo","Date", "FileName"])
f.close()
for files in fileList:
xlApp = win32com.client.Dispatch("Excel.Application")
print "Excel library version:", xlApp.Version
password = 'PW'
folder = r"C:\Users\t\Documents\test-python"+ os.sep
filenam = os.path.join(folder,files)
xlwb = xlApp.Workbooks.Open(filenam, False, True, None, password)
#xlwb = open_workbook(filenam)
tot_sheets = xlwb.Sheets.Count
if tot_sheets==1:
sheet_name = xlwb.Sheets[1].Name
val= sheet_name.Cells(3,1).Value
else:
for i in range(1,tot_sheets+1):
sheet_name = xlwb.Sheets[i].Name
val= sheet_name.Cells(3,1).Value
newval=val[0].split('-')
val1=newval[2]
csvname=files.split('.')[0]
csvname=csvname+'.csv'
if tot_sheets==1:
sheet = xlwb.Sheets[1].Name
csvfile = open(csvname, 'w')
wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)
for rownum in range(13,sheet.Count):
for i in range(3,8):
wr.writerow(sheet.Cells(rownum,i).Value)
csvfile.close()
else:
for i in range(1,tot_sheets+1):
sheet = xlwb.Sheets[i].Name
csvfile = open(csvname, 'w')
wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)
for rownum in range(13,sheet.Count):
for i in range(3,8):
wr.writerow(sheet.Cells(rownum,i).Value)
csvfile.close()
df= pd.read_csv(csvname,names = ["col1", "col2", "CCC", "DDD","EEE","FFF","GGG"])
today = datetime.date.today()
df.insert(0,'date',today)
df.insert(1,'filename',files)
df1 = df.replace(np.nan, val1, regex=True)
print df1
os.remove(csvname)
with open('output.csv', 'a') as f:
df1.to_csv(f, header=False)
xlwb.Close(True)
def main():
#folder=raw_input("FOLDER NAME : ")
#password=raw_input("Enter Password : ")
fileList=find_filenames()
today=str(datetime.date.today())
f=open('filenames'+today+'.txt','w')
for files in fileList:
f.write(files+"\n")
f.close()
file_data(fileList)
if __name__ == "__main__":
main()
Description of requirements/functionality: PYTHON 3.6 under Windows
Hi, I need to fix the following script so that it works under python 3.6
The script should read 2 or more excel files in working directory (attached are 2 examples), that are all in the same format, and create a fileMMDDYY.csv output file.
All excel files are in the same format
All excel files have same password (make password a command line variable when calling the script
Please let me know if you have any questions. I'll provide TeamViewer access if we have trouble running the script
Specific technologies required: Python 3.6
OS requirements: Windows
Extra notes: Here is the existing script
#!/usr/bin/python
#importing modules
#Please check that all the modules are installed on your system
import os
from xlrd import *
#import pandas as pd
import csv
from subprocess import call
import numpy as np
import datetime
import glob
import win32com.client
#Functions
#Find excel file names in a given folder
def find_filenames(suffix="xlsx" ):
folder = r"C:\Users\t\Documents\test-python"+ os.sep
os.chdir(folder)
result = [i for i in glob.glob('*.{}'.format(suffix))]
return result
#Will extract all the data
def file_data(fileList):
f=open('output.csv', 'w')
writer = csv.writer(f)
writer.writerow(["SNo","Date", "FileName"])
f.close()
for files in fileList:
xlApp = win32com.client.Dispatch("Excel.Application")
print "Excel library version:", xlApp.Version
password = 'PW'
folder = r"C:\Users\t\Documents\test-python"+ os.sep
filenam = os.path.join(folder,files)
xlwb = xlApp.Workbooks.Open(filenam, False, True, None, password)
#xlwb = open_workbook(filenam)
tot_sheets = xlwb.Sheets.Count
if tot_sheets==1:
sheet_name = xlwb.Sheets[1].Name
val= sheet_name.Cells(3,1).Value
else:
for i in range(1,tot_sheets+1):
sheet_name = xlwb.Sheets[i].Name
val= sheet_name.Cells(3,1).Value
newval=val[0].split('-')
val1=newval[2]
csvname=files.split('.')[0]
csvname=csvname+'.csv'
if tot_sheets==1:
sheet = xlwb.Sheets[1].Name
csvfile = open(csvname, 'w')
wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)
for rownum in range(13,sheet.Count):
for i in range(3,8):
wr.writerow(sheet.Cells(rownum,i).Value)
csvfile.close()
else:
for i in range(1,tot_sheets+1):
sheet = xlwb.Sheets[i].Name
csvfile = open(csvname, 'w')
wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)
for rownum in range(13,sheet.Count):
for i in range(3,8):
wr.writerow(sheet.Cells(rownum,i).Value)
csvfile.close()
df= pd.read_csv(csvname,names = ["col1", "col2", "CCC", "DDD","EEE","FFF","GGG"])
today = datetime.date.today()
df.insert(0,'date',today)
df.insert(1,'filename',files)
df1 = df.replace(np.nan, val1, regex=True)
print df1
os.remove(csvname)
with open('output.csv', 'a') as f:
df1.to_csv(f, header=False)
xlwb.Close(True)
def main():
#folder=raw_input("FOLDER NAME : ")
#password=raw_input("Enter Password : ")
fileList=find_filenames()
today=str(datetime.date.today())
f=open('filenames'+today+'.txt','w')
for files in fileList:
f.write(files+"\n")
f.close()
file_data(fileList)
if __name__ == "__main__":
main()
Anatoly R.
100% (1)Projects Completed
5
Freelancers worked with
3
Projects awarded
56%
Last project
20 Dec 2020
United States
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
What's the password for these .ods files?
Anatoly R.20 Apr 2017pw :)
395275
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies