Excel as a Datasource

Top  Previous  Next

 

If you would like to use information that you currently have in a MS Excel spreadsheet, you can simply point to this spreadsheet and use it as a database.

 

Some tips for using Excel as a database:

 

1.Named Ranges Show will as tables so you can name your tables by creating named ranges that contain your data within the range.
2.Tabsheets will show as tables with a "$" appended to the tabsheet name.  By naming your tab sheets you can create more readable table names.
3.The first row of your data will be used as the column names, so you should include a header row.
4.Some columns that are formulas do not work as data, if this happens, try to copy your table and "Paste Special" > "Values" into a new tabsheet, this should help.
5.Each record should have a unique ID, if your data does not have a unique identifier, you can create one by inserting a column and typing 1 in the first row, 2 in the second row, then select both cells and drag down, excel should fill down 3,4,5.... until you reach the last row.
6.Because Excel does not have the idea of a primary key, dbQwiksite will attempt to match all columns on page flows, you should check your field mappings, click Clear, and map only on the Unique ID, this will make sure your pages run properly.
7.Not all SQL commands work with Excel. This is a limitation of Excel as a database, not dbQwikSite.
8.There is additional information available from Microsoft on working with Excel as a data base with web sites.

 http://support.microsoft.com/kb/q278973/

 http://support.microsoft.com/kb/195951/

 

You may also convert your excel into an MS Access database using the database wizard.

 

 


See Also:

Connection Wizard

ADO Connections

ODBC Connections

Database Tips