Access and Excel file databases
There are two different ways of importing Microsoft Access and Excel databases into Q.
Converting them into Excel or CSV files
The most straightforward approach is to read them in directly as Excel files, or resave them to CSV files (see Using Excel and CSV Files in Q). Where there are multiple tables, you can either create a single "View" of them all and import this, or, create relationships in Q (see Panel Data).
As databases
Alternatively, they can be treated as databases (see SQL Data for more detail about the general process). This is generally the better approach, as:
- when the original database is updated, so will your Q data (just like a live SQL database), and
- Q can detect the column types in the database (dates, etc.), whereas with CSV everything is text.
There are a number of drawbacks to this approach:
- It is much more complicated and requires a technical understanding of databases.
- Access and Excel files cannot be accessed on Displayr. Keep this in mind if you intend to upload the project as a dashboard.
Excel 2007+ Workbook (*.xlsx)
Excel 2007 or later must be installed to access these files.
Prior to connecting to the .xlsx file, you must:
- Open the file in Excel.
- Select all of the cells that contain data (and optionally, column names) that you wish to import to Q.
- Right-click on the selected cells and click Define Name...
- Copy the name you enter here for later use in the SQL command.
| Connection string | First row contains column names. | Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";
|
|---|---|---|
| No column names; first row contains data. | Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx;Extended Properties="Excel 12.0 Xml;HDR=NO";
|
|
| SQL command | select * from Name(Where Name is the name you defined earlier in Excel.) |
|
Excel 97-2003 Workbook (*.xls)
These files may only be opened in Windows XP. Excel must be installed (any version, 2000 or later, will work).
Prior to connecting to the .xlsx file, you must:
- Open the file in Excel.
- Select all of the cells that contain data (and optionally, column names) that you wish to import to Q.
- Right-click on the selected cells and click Define Name...
- Copy the name you enter here for later use in the SQL command.
| Connection string | Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0
|
|---|---|
| SQL command | select * from Name(Where Name is the name you defined earlier in Excel.) |
Access 2007+ Database (*.accdb)
Access 2007 or later must be installed to open these files. Alternatively, or in addition if you are having problems connecting, try installing the Microsoft Access Database Engine Redistributable from Microsoft (using the version appropriate to your version of Office).
| Connection string | Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\database.accdb;Persist Security Info=False;
|
|---|
Access 2000-2003 Database (*.mdb)
These files may only be opened in Windows XP. Access must be installed (any version, 2000 or later, will work).
| Connection string | Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database.mdb;User Id=admin;Password=;
|
|---|