ReadMore

I wanted to handle a simple task: convert a bunch of XSLs to CSVs. Well I wrote a small Script in VBScript and it worked. Then I needed to change the List separator, so I did that in my System settings, and then my Script didn’t work anymore, because it just ignored my changed settings.

I googled a lot and didn’t find the solution (only people with the same problem). It seems that VBScript (or WSH) is using it’s own locale that you can not change.

So I began to look for alternatives. Python can handle the COM interface when you install PyWin32 (the version must match the python version you’re using). So I just rebuild my script with python and it’s COM-Interface. Et voil�: it takes the user defined settings and converts my files in the way I want them.

#import win32 stuff
from win32com.client import Dispatch
#import other needed
import glob,sys,os

path = os.path.split(sys.argv[0]) #gather the path of the script
path = path[0]
path = path + '\\' #append \ at the end of path (when using *NIX you must use / instead
files = glob.glob('*.xls') #get list of files

xlApp = Dispatch("Excel.Application") #create Excel COM-Object
xlApp.DisplayAlerts = False
for file in files:
    xlWB = xlApp.Workbooks.Open(path + file)
    xlWB.SaveAs(path + file.replace(".xls",".csv"), 6 ) #6 = xlcsv
    xlWB.Close()
xlApp.Quit()
del xlApp

Sometimes it’s so simple:

import glob
files = glob.glob('*.*');
f=open('start_script.sql', 'w')
f.write('PROMPT This is an automatic generated start script\n');
f.write('PROMPT\n');
f.write('SPOOL start_script.LOG\n\n');
for filename in files:
    if filename == 'start_script.sql':
        continue
    f.write('PROMPT Starting script ' + filename + '\n');
    f.write('@@' + filename + '\n');
f.write('\nPROMPT\n');
f.write('PROMPT Finished.\n');
f.write('SPOOL OFF\n');
f.close();
exit();

Explanation:

First get a file list of the current directory (glob.glob(‘.’), this won’t return you directories since they don’t have an extension, if your files doesn’t have an extension, either: your fault). Then open a file called start_script.sql which will held our start-script (pretty self-explanatory). Then we generate a “@@” line for each file (this means if you have non-SQL-files in that directory you have to clean-up your script or edit it that it only takes .sql files (tip: glob.glob(‘*.sql’)). The start-script itself is excluded but the python script will be included (can easily be made excluded as well). After the loop the file gets closed and that’s it.