Connecting WOW to an Excel File

To connect WOW to a MS Excel file the following steps need to be done:

1)  Create an ODBC System Data Source (DSN) on the same system where the application server is installed.  (Remote access is not supported)

2)  Reference the desired Excel worksheet on the DSN

3)  Connect WOW to the created DSN from step 1 & 2  

Creating system DSN (Windows Only)

1.Go to start→ Control panel→administrative tool→ Data Source(ODBC) as the screenshot below.

2.Choose “System  DSN” and click on “Add” button

3.  Under “Create New Data Source” panel, choose “Microft Excel Driver (*.xls)” then hit “Finish” as below

Pointing to desired  Excel worksheet

1.After clicking “finish” as the screenshot above, Name the “Data Source Name” as desired.  Remember this name for later.  Click on “Select Workbook”.  In this example, the DSN is named “Mexcel”.  

**Note**: There may be connection problems if trying to connect to a version under Microsoft Access/Excel 2000.  

2. Point the DSN to the Excel file location (My file in C:\planetj\wowExcel\testWOWExcel.xls)  then click “ok”

**Note**:  Only Uncheck the “Read Only” box ( screenshot above)  when data from Excel spreadsheet needs to be updated

3. Click: “ok” (screenshot below)

Click “ok”(screenshot below)

Connecting WOW to created DSN ( as screenshot below)   

1. JDBC Driver: MS Access/Excel (ODBC)
2. The IP Address: where DSN is located (in example it is “localhost”)
3. DSN: Mexcel ( this name was created earlier in step 1 of Pointing to the desired  Excel worksheet)

The below is the WHOLE connection string: JDBC Driver: MS Access/Excel (ODBC) URL: jdbc:odbc:localhost;dsn=Mexcel;

Syntax of SQL select, update statement….

The screenshot below is an excel file that is accessed using WOW

1 SQL select statement syntax

Inside “testing.xls” file, there are 3 Excel worksheets by default which are sheet1, sheet2 and sheet3. Our data is on sheet1. To display all data from sheet1:

**NOTE**: Notice the use of [ ] around the worksheet name and the trailing $.  This may be required on many systems. But fields such as UserID, FirstName and Workdept,etc  can be either inside or not inside [ ] and trailing $ is NOT needed.

1.1 Basic SQL Queries Using the SELECT Statement

Select * from [Sheet1$]

1.2 Other Queries Using the SELECT Statement

1.2.1 SQL allows you to select specific columns from a table.

SELECT Firstname, Lastname, workdept , salary FROM [Sheet1$]

1.2.2. Using a WHERE clause with the SELECT statement

SELECT Firstname, Lastname, workdept , salary FROM [Sheet1$] where FirstName =?

Or

SELECT * FROM [Sheet1$] where workdept = ?

2 SQL update statement syntax

2.1 Basic SQL Queries Using the UPDATE Command

UPDATE  [Sheet1$]  SET [Bonus] = [Bonus] + '200'

2.2 Using a WHERE clause with the UPDATE statement

UPDATE  [Sheet1$]  SET [Bonus] = [Bonus] + '200' WHERE WORKDEPT =?

Or

UPDATE [Sheet1$] SET [SALARY]=  [SALARY] + 1000 WHERE WORKDEPT=?