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
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
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)
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
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 =?
SELECT * FROM [Sheet1$] where workdept = ?
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 =?
UPDATE [Sheet1$] SET [SALARY]= [SALARY] + 1000 WHERE WORKDEPT=?