Treating Excel as database schema

DESCRIPTION

In Adeptia one excel schema activity processes one sheet of an excel file (Edit: Support for multiple sheets has been added in v6.5 MT1). So you cannot have all sheets as equal roots in one schema using excel schema. For using multiple sheets of Excel you can create excel as Advance Database Schema. Below are the high level steps for treating Excel as an Advance Database Schema in Windows:

1. Steps for creating Excel as ODBC Data Source in Windows 64 bit environment

• Download the 64 bit Excel driver "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)" from Microsoft website.
• Go to ODBC(Data Source) and select the 64 bit Excel driver to set up a data source(System DSN) for the excel.
• Then provide the Name, Description and select the Excel version, workbook for the Data Source.

2. Steps for creating Excel as an Advance Database Schema in 64 bit Adeptia Suite

• Create a Database Driver and provide the Driver Main Class Name ”sun.jdbc.odbc.JdbcOdbcDriver” and save it.
• Then create a Database info and select the above created database driver and provide the Server URL as “jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=<full path to the excel file with name>;.
• Test the Connection using the Test button (Note: Schema won’t be displayed in the Database info)
• Create a New Advance Database Schema and select the above created Database info.
• After selecting the database info browse the tables and select the sheet and its respective columns.
• Then load this Schema inside the data mapper.

NOTE: To create Excel as an Advance Database Schema in Windows for 32 bit Adeptia Suite:

• Go to ODBC(Data Source) and select the 32 bit excel driver "Microsoft Excel Driver (.xls)" to set up a data source(System DSN) for the excel.

• Then create Database Info using the following Server URL:

" jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=<full path to the excel file with name>;."