Transform Staid Text Files into Automatic Custom Reports

By David H. Ringstrom, CPA

Data arrives on your desk from many sources, but often it's in the form of a lowly text file. As you're aware, it's easy to open such files in Excel and then manually manipulate the data into the desired format. No big deal for a quick analysis, but let's say you need to process the same file in the same fashion each week or month. You could record a macro to automate many of the steps, but there's an easier alternative available: Microsoft Query.

Microsoft Query has resided in the background of Microsoft Office for many years. In this article I'm going to describe how you can query text files to automatically carry out these tasks without any subsequent intervention:

• Rearrange the fields within the text file
• Sort the data in any order you wish, including more than 3 columns (which helps overcome a limitation in Excel 2003 and earlier—Excel 2007 users can sort on up to 64 columns)
• Filter the data to only display selected records based on criteria you provide
• Limit the display to the top 5, 10, or whatever number of records you choose

Expert tip: Although this article is specific to accessing data from text files, you can easily adapt the techniques I describe to query data from databases and other sources.

 

Start with Sample Data

To begin, you first need some sample data to work with. Your data should be in text file format, and the first record should include headings. You can make up some sample data easily in a blank Excel workbook:

 

1. Add these headings:

1. Salesperson

2. City

3. Customer

4. Sales

2. Add these names to the worksheet:

1. Select cells A2 through A6, type Bob, and then press Ctrl-Enter.

2. Select cells A7 through A11, type Carol and then press Ctrl-Enter.

3. Select cells A12 through A16, type Ted, and then press Ctrl-Enter.

4. Select cells A17 through A21, type Alice, and then press Ctrl-Enter.

Data entry trick: Ctrl-Enter copies whatever you typed to the selected cells at once, eliminating the need to drag the fill handle down.

 

3. Assign these Cities:

1. Select cells B2 through B11, type Atlanta, and then press Ctrl-Enter.

2. Select cells B12 through B21, type Memphis, and then press Ctrl-Enter.

4. Assign customer names:

1. Enter the word Customer1 in cell C2, and then double-click on the fill handle for this cell to create a series of names ending in Customer20. Alternatively you can drag the fill handle down manually through row 21.

5. Enter sales:

1. Enter 1000 in cell C2.

2. Use the Fill Series command to populate the rest of the rows:

Excel 2007: Choose Fill from the Editing section of the Home ribbon, and then choose Series.

Excel 2003 and earlier: Choose Edit, Fill, and then Series.

3. When the Series window appears onscreen, as shown in Figure 1, choose Columns, enter a Step Value of 1500, and a Stop Value of 30000, and then click OK.

 

Figure 1: The Fill Series window enables you to quickly create a series of numbers.

 

6. At this point your data should look similar to Figure 2. Save the resulting spreadsheet as a comma-separated value file:

Excel 2007: Click the Office button, choose Save As, and then Other formats. Choose CSV (Comma Delimited) (*.csv) from the Save As Type drop down list, assign a name to your file, and then click Save.

Excel 2003 and earlier: Choose File, and then Save As. Choose CSV (Comma Delimited) (*.csv) from the Save As Type drop down list, assign a name to your file, and then click Save.

 

Figure 2: Create this sample data and then save it as a CSV file.

 

7. Close the workbook. You won't be able to query the text file if you leave it open in Excel.

 

You now have a sample data file that you can use to test the remaining procedures in this article.

 

 

Create a Query

The next steps involve establishing a connection to your data.

1. Create a new, blank workbook in Excel.

2. Open the Choose Data Source dialog box:

Excel 2007: Choose From Other Sources in the Data section of the ribbon, and then select From Microsoft Query.

Excel 2003 and earlier: Choose Data, Import External data, and then New Database query.

 

Installation Required? Depending upon how Microsoft Office was installed on your computer, you may be prompted to insert your Office CD so that Microsoft Query can be installed. This is a one-time step.

3. When the Choose Data Source window appears, click on New Data Source, and then click OK.

4. As shown in Figure 3, assign a name to the query, ideally one that matches your project—such as Sales Analysis.

 

Figure 3: Use this dialog box to establish a connection to your text file.

5. Choose Microsoft Text Driver from the drop-down list.

6. Click the Connect button.

7. Click OK if you saved your text file to the current folder, or unclick the Use Current Directory button to specify another folder. This will enable the Select Directory button, which you can use to browse to the folder that contains the text file that you wish to query. Click OK to return to the Create New Data Source dialog box once you specify the folder.

8. You can now make a choice from the Default Table listing, as shown in Figure 4.

 

Figure 4: Choose your text file from the drop-down list.

 

9. Click OK to return to the Choose Data Source window. Your new data source, such as Sales Analysis, should appear on the list. Double-click on the data source to begin using it in a new query.

10. As shown in Figure 5, Microsoft Query should appear. Drag the files into the query window in this order:

·         Customer

·         Salesperson

·         City

·         Sales

Excel 2003 and earlier: A Query Wizard will likely walk you through the first few steps that I describe here. Once you've completed the wizard then you'll have a choice to View Data or Edit Query in Microsoft Query. At that point you'll see the same dialog box that I describe for Excel 2007 users. As you can see, one of the benefits of using Microsoft Query in this fashion is that you can reorder the data to your liking.

 

Figure 5: Microsoft Query allows you to automatically filter and sort data contained in text files.

 

11. Sort the data in descending sales order:

a.       Choose Records, and then Sort.

b.      Choose Sales from the column field, specify Descending, and then click Add.

c.       Click Close to return to the main Microsoft Query window.

Multiple sorts: If you have a text file with many different columns of data, you can add as many sorts as you wish.

 

12. Limit the data returned from the text file to just Memphis:

a.       Click Criteria, and then Add Criteria.

b.      Choose City from the Field list.

c.       Click the Values button, and then choose Memphis.

d.      Click Add.

e.       Optionally add additional criteria, such as a specific salesperson's name.

f.       Click Close to return to the main Microsoft Query window.

 

Explore the SQL statement

At this point we're ready to return our data to Excel, but before we do, let's take a moment to look at the underlying SQL statement that Microsoft Query created as we made the preceding choices. SQL stands for Structured Query Language, and is a common method for extracting data from databases and other sources.

1. Choose View, and then SQL, or click the SQL button on the toolbar.

2. As shown in Figure 6, Microsoft Query has developed the underlying SQL statement as we made choices through its user interface. There are four basic commands in use here:

SELECT Instructs Microsoft Query which fields we wish to return from the data source.

FROM: Tells Microsoft Query which data source we're using.

WHERE: Instructs Microsoft Query that we only want records where the city is equal to Memphis.

ORDER BY: This informs Microsoft Query that we wish to sort the data in descending order based on the Sales field.

 

Figure 6: The Microsoft Query user interface allows you to generate SQL statements.

 

If you have experience with SQL statements then you can create some very robust queries. Of course, if you'd rather not create SQL statements, simply use the menus within Microsoft Query instead.

 

Learn more: Free SQL statements tutorials are available at www.w3schools.com/sql and www.sqlcourse.com.

 

However, there is one tweak to the query that you can't make through menu picks, which is to limit the query to the top 5 choices. To do so, view the SQL statement as described above, and then insert the words TOP x where x is the number of records that you wish to return, as shown in Figure 7. When you click the SQL window, click OK on the prompt that says the SQL Query Can't Be Represented Graphically.

 

Figure 7: You can limit the number of results that the query returns.

 

Return the Data to Excel

You're now ready to return the data you queried from the text file to your Excel worksheet:

 

1. In Microsoft Query, choose File, and then Return Data to Microsoft Excel.

2. Click the Properties button when the Import Data window shown in Figure 8 appears.

 

Figure 8: This dialog box allows you to manage the location and other aspects of your query.

3. Click Refresh Data When Opening File if you'd like Excel to automatically retrieve the most recent data when you open your spreadsheet, as shown in Figure 9.

 

Figure 9: You can instruct Excel to automatically refresh the query when you open the spreadsheet.

4. Click OK twice to return the data you queried from the text file into Microsoft Excel.

As you can see in Figure 10, Excel 2007 automatically formats your data as a table, which means it automatically formats the rows. You can easily eliminate this formatting:

 

Figure 10: Excel 2007 automatically formats your data as a table.

1. Click the arrow to the bottom-right of the Table Styles section on the Design tab of the ribbon.

2. Choose the None format, which is the first formatting choice shown in Figure 11.

 

If you'd rather type your own field names in at the top of the table, as opposed to those from the text file, unclick the Header Row checkbox in the Design tab of the ribbon. To do so in Excel 2003 or earlier, right-click on the table, choose Data Range Properties, and then clear the checkbox for Include Field Names.

 

Figure 11: The None style appears in the top left-hand corner of the Styles section..

 

Design tab tip: The Design tab is context sensitive, meaning it disappears if you click outside of the data that has been returned from the text file.

 

Refresh or Edit Your Query

If you set the Refresh Data When Opening File option, then your data will automatically refresh when you open your file. Alternatively, you can right-click anywhere within the data, and then choose Refresh. You'll want to refresh the data any time that you've received a new version of the corresponding text file. You can also edit the query:

 

Excel 2007: Click the Properties button in the External Table Data section of the Design ribbon.

Excel 2003 and earlier: Right-click on the table and choose Edit Query.

 

 

David Ringstrom heads an Atlanta-based software and database consulting firm serving clients nationwide since 1992. Long ago David recognized that either you work Excel, or it works you, so much of his work involves creating Excel-based tools to streamline accounting and other processes. David shares his knowledge with others as a freelance writer, professional speaker and technical editor, and is proud to have served in the United States Navy. David can be reached at david@acctadv.com

 

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.