Method One: Convert an xls file to CSV

The first thing to be said is that this is just one of many ways to do this, if you want to find other ways then Google is your friend too.

Limitation: unfortunately when Iguana is run as a service on Windows 7 the script for converting xls files to CSV does not work (and probably will not work with Vista). This appears to do with the increased security/access restrictions that Windows 7 places on Services. The workaround is to run Iguana from the command line as iguana --run when using Windows 7. We will update this section when we find a solution.

These are the things you need to do to make this work:

• Save the XlsToCsv.vbs file in the FilesIncoming directory
• Add the path to the FilesIncoming directory to System Path variable

  Note: if you decided to save the XlsToCsv.vbs file somewhere else then use that path instead

• Restart the iNTERFACEWARE Iguana Service

Here is the code for the XlsToCsv.vbs file :

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
Dim oSheet

If oBook.Sheets.count = 1 Then 
  'save a single sheet
  oBook.SaveAs WScript.Arguments.Item(1), 6
else
  'save multiple sheets
  i=1
  aname=split(Wscript.Arguments.Item(1),".",-1,1)
  For Each oSheet In oBook.WorkSheets
    fname = aname(0) & "_sheet" & Cstr(i)
    oSheet.SaveAs fname, 6
    i=i+1
  Next   
End If

oBook.Close True
oExcel.Quit
WScript.Quit
set oSheet = Nothing
set oBook = Nothing
set oExcel = Nothing

This code handles xls files with a single or multiple sheets. With a single sheet the CSV file uses the same name as the original xls file, when there are multiple files _sheet1, _sheet2 etc is appended.

Sounds easy right? Well here are a few things I found out along the way:

• Not all commands that work in a cmd window will work with os.execute() and io.popen()
  • First I quoted all the command and file paths (needed quotes as the path has a space)
    • cmd.exe /c “C:Program Files…xlstocsv” “C:Program Files…Patient_Blank.xls” “C:Program Files…Patient_Blank.csv”
    • Works in cmd window but not with os.execute() or io.popen()
  • So I tried a directory with no spaces for XlsToCsv.vbs (C:FilesIncoming)
    • cmd.exe /c C:FilesIncomingxlstocsv “C:Program Files…Patient_Blank.xls” “C:Program Files…Patient_Blank.csv”
    • cmd.exe /c “C:FilesIncomingxlstocsv” “C:Program Files…Patient_Blank.xls” “C:Program Files…Patient_Blank.csv”
    • The first one works the second one fails
  • Conclusion: the command cannot be contained in quotes when using os.execute() and io.popen()
  • Solution: add the path to to Path variable as described above, or put XlsToCsv.vbs in a directory with no spaces in the path
• Things that I found useful
  • I chose os.execute() to run XlsToCsv.vbs as it is easy to use the return to see when it works (0=success, 1=failure)
  • I read the file returned by io.popen() to test if the Path variable was correct (yup I got it wrong a few times…)

I found the following links useful: An A-Z Index of Windows VBScript commands and the Microsoft VBScript Language Reference.