How to Import 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.

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.Access 2007+ Database (*.accdb)

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 versio appropriate to your version of Office).Access 2000-2003 Database (*.mdb)

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).

Next

How to Read an Excel File into Q

How to Use Excel and CSV Files in Q

How to Import Data from SQL

How to Connect to a MySQL Database in Q