Connecting to Text and CSV Files

AQT can be used to access structured text files, such as CSVs (files where the data is a set of comma-separated-variables). This gives you the ability for querying these files with a database-style interface.

Prerequisite software

No software is needed in order to access Text files. This is done using the Microsoft Text ODBC Driver which is present in the standard Windows install.

Signing onto a text file

You have two methods of signing accessing a Text file:

• setting up an ODBC Datasource for a Text file
• open a csv file with AQT

The first method is useful if you are accessing particular Text files on a frequent basis.

Setting up an ODBC Driver for your Text Files

The general procedure for this is described in Configuring a Database Connection.

• select a Driver of Microsoft Text Driver
• you select the directory where your text files reside by:
  • de-select Current Directory
  • clicking Select Directory button
• you may also wish to click on Options and Define Format. In this, you can specify:
  • the character that delimits the columns
  • whether the first line of the files contains column titles
  • the data types of the columns in your files

Once you have done this, the ODBC Datasource name for your Text file will appear in the list of databases in the AQT sign-on window (make sure you have select Show All (not Show Recent)).

When you sign onto a Text datasource, you will see all the text (*.txt and *.csv) files in the specified directory. It is assumed that all your files are in the same format - eg. have the same column delimiter, and whether the first line has titles.

Open a csv file with AQT

To do this, right-click the csv file, select Open With > Choose Program. Click on Browse to select AQT. You can check Always use the selected program to open this kind of file if you want to always open csv files with AQT.

This method can only be used for csv files, and not any other file type.

When you use this connection method:

• you will be shown all files in the directory, not just the selected file.
• the column-delimiter character is assumed to be a comma
• the first line of the files will be assumed to contain column titles

Using text datasources

• you can create new files with the Create Table command
• you can delete files with the Drop Table command
• rows can be inserted into files, however you cannot update or delete rows

Copy data from text files to a database table

The best option for this is to use the Data Loader. An alternate method is:

• set up a Datasource for the text file (as described above) and sign onto it
• use Export as Insert to export the data from the file as a series of insert statements. When you do this, you should specify the target table name in the Table name for Insert Statements field.
• sign onto the target database and run these insert statements.