Csv Query Tool

Posted onby

Click the ' CSV Generator Tool ' from the Launcher to start the tool. Select the AD object type from the list. Browse and select the import CSV file in the File Name field. Click on GENERATE to get CSV file containing Canonical Name, Display Name and Description attributes. Querycsv.py is a Python module and program that allows you to execute SQL code against data contained in one or more comma-separated-value (CSV) files. The output of the SQL query will be displayed on the console by default, but may be saved in a new CSV file. At first, CSV Query executes a simple SQL query to select all the data in the input file (SELECT. FROM this). You will notice that the data is then displayed as a table in the CSV Query window.

Microsoft recently updated the built-in tools for retrieving stock quotes into Excel. If you’re typing closing prices into Excel manually, this article may be able to help you get it done faster. Specifically, we’ll talk about how the Stock data type retrieves current quotes and related information, and how Power Query can retrieve historical stock quotes from the web. We’ll take them one at a time.

Note: This article is presented with Excel O365 for Windows; not all versions of Excel include the features discussed.

Current Prices with the Stock Data Type

Let’s say you have a handful of tickers and you want to view their current prices or related information. Perhaps your list is stored in a table (Insert > Table) and looks like Figure 1.

(Note: converting your ticker list into a Table isn’t required, it just makes this feature easier to use).

Csv Query Tool Software

Csv Query Tool

You can select the tickers, then click the Data > Stocks command in the Data Types group. When you do, Excel attempts to convert those static text values into stocks (Figure 2).

The little icons indicate Excel has successfully found the ticker symbols and converted them into stocks. So, what is so special about the stock data type? It retrieves a rich collection of market data—including current price, volume, high, low, company name, company description, number of employees and much more. To reveal these additional attributes, just click the little stock icon to the left of the company name and you’ll see a pop-up card that contains related data. Or, if you want to view the related data in cells, just click the little Add Data icon in the upper right. For example, we can select price, high and low from the list and Excel retrieves the corresponding values (Figure 3).

You can manually refresh the values any time by right-clicking any of the stock icons and selecting Data Type > Refresh. If you had previously used the MSN Money Central Investor Stock Quotes connection, this is the replacement feature and provides much more information.

But what if you want to retrieve historical quotes? For that, we can import data from a
corresponding web page with Power Query.

Historical Prices with Power Query

At the time of this article, the Stocks data type contains current quotes only. So, if we want historical quotes, we’ll need to turn to the web. Pull up your favorite historical quotes web page or do a web search. In this article, we retrieve historical quotes from yahoo.com.

The Yahoo service works well with Power Query and has a logical URL structure that is easy to customize for your desired ticker symbol. It uses the format:

Since Microsoft’s ticker is MSFT, we could type the following URL into any web browser to see the historical quotes:

  • https://finance.yahoo.com/quote/MSFT/history/

We test the URL for our desired ticker by entering it into a web browser. If the page works, we’ll see a table full of daily prices. Now, there are a few ways to get data displayed on the web page into Excel. We could select the range on the web page, copy (Ctrl+C) the info, then open an Excel workbook and do a Paste or Paste Special.

Depending on the webpage formatting, this may or may not work well. If it does, this quick copy/paste method may be all we need.

Another option is to download a file from that webpage (if available) and then open it with Excel. This option typically avoids the formatting issues that can occur with copy/paste. In the case of the Yahoo page, there is a Download Data link that exports a CSV file that can be opened with Excel. And, if this was a one-time project, this option may be sufficient.

But, if this was something we needed to update on an ongoing basis, another option is to use Power Query. Going forward, rather than browse to the webpage to view and download or copy the data, we can just click Refresh from within Excel. To do so, click Data > From Web in the Get & Transform group (not the legacy Get External Data group). This will open Power Query’s From Web dialog. You simply paste or type your desired URL (Figure 4).

This will display Power Query’s Navigator dialog where you’re provided a list of importable items, which are essentially various tables that appear on the web page. We click through the list of tables until we see the one we want to import. For example, in our case, Table 2 holds the historical quotes (Figure 5).

With the desired table selected, we just click the down arrow on the Load button and select Load To. In the resulting dialog, we select Table and … the results are loaded into the specified worksheet (Figure 6).

Now, the nice thing about using Power Query is that next time we need to retrieve updated historical quotes, we can simply right-click any cell in the results table and select Refresh. Power Query essentially retrieves updated values from the original URL. In Excel, we see the updated table rows.

I hope these methods provide an efficient way to retrieve current and historical stock quotes into your Excel file. And remember, Excel rules!

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
Csv Query Tool

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

Csv Query Tools

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

Python Csv Query Tool

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

Csv Query Tool