Automatic conversion of Excel to CSV for Oracle external table using an auto open macro

In Oracle Data Warehouses data from Excel sheets is often used for reference tables.

In order to read this data into an external table the Excel spreadsheet has to be converted to csv format.

Users administer data in Excel rather in the xlsx format than the csv format, because they can use multiple sheets in a workbook.

In this example the sheets 'first_sheet' and 'second_sheet' of the workbook 'excel_to_convert.xlsx' are automatically converted to 'first_sheet.csv' and 'second_sheet.csv' by opening the macro enabled workbook 'convert_xlsx_to_csv.xlsm'.

After opening 'convert_xlsx_to_csv.xlsm' the macro Auto_open executes:

Sub Auto_open()

'

' Auto_open Macro

'

Application.Run "convert_xlsx_to_csv.xlsm!Save_as_csv"

Application.Quit

End Sub

The Auto_open macro calls macro Save_as_csv.

This macro opens the workbook C:\excel_to_convert.

The sheet 'first_sheet' is saved as 'first_sheet.csv', the second sheet likewise:

Sub Save_as_csv()

'

' Save_as_csv Macro

'

Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\excel_to_convert.xlsx"

Sheets("first_sheet").Select

ActiveWorkbook.SaveAs Filename:="C:\first_sheet.csv", FileFormat:= _

xlCSV, CreateBackup:=False

ActiveWindow.Close

Workbooks.Open Filename:="C:\excel_to_convert.xlsx"

Sheets("second_sheet").Select

ActiveWorkbook.SaveAs Filename:="C:\second_sheet.csv", FileFormat:= _

xlCSV, CreateBackup:=False

ActiveWindow.Close

Application.DisplayAlerts = True

End Sub

Now the load of the external tables can be completely automated:

1. In a scheduled task we call a bat file.
2. In the bat file we open the macro enabled excel.
3. In the bat file we ftp the resulting csv files to an oracle directory on the database server.
4. The data is available as an external table in the oracle database.

When we reverse this sequence it can be used for instance to automatically mail formatted excel files to end users:

1. We write a csv file to an oracle directory on the database server using the utl_file package.
2. In a scheduled task we call a bat file.
3. In the bat file we ftp the csv files to the windows server.
4. In the bat file we convert the csv file to a formatted excel, using another macro enabled excel.
5. In the bat file we mail the formatted Excel to the end user.