Converting them into Excel or CSV files
The most straightforward approach is to read them 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 as databases in Displayr. If using Displayr, see Converting them into Excel or CSV files
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 version appropriate to your version of Office).
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