Support : Publications : Chronolog Archives : April 2001

Search Solution
Converting Embedded Tables into Excel

There are many records in Dialog databases that contain statistical data in the form of tables. One database that contains a number of tables is File 93, TableBase. It is often useful to place a table from a Dialog record into a report that you are creating; thus, it is important to be able to convert the data from a Dialog database into a spreadsheet, such as Excel. The following example illustrates this function.

If your search yields a record, which contains an embedded table that you would like to convert to Excel, just follow the steps below which converts the table from the Dialog Record shown in Figure 1 to a spreadsheet.

Step 1: Highlight the table of interest from the beginning of the table to the end, including any footnotes you want to include on your spreadsheet (See Figure 2).

From the Edit menu, select Copy.

From the File menu, select Save Copy as; then Save Selection as...

Choose to save the file as a text file in the Save file as type: box by selecting Text Files (*.TXT). Enter the name of the file in the File name box; then click the OK button.

Step 2: Open Microsoft® Excel.

From the File menu, choose Open; then select the appropriate directory in the Look in: box. You will not see your text file until you change the Files of type: box to show Text Files (*.prn; *.txt; *.csv).

Step 3: Choose your file and click Open. You will be in the Text Import Wizard. Follow the instructions for the Wizard.

Make sure that the Original Data Type is set to Fixed width; then click Next> (See Figure 3).

Follow the instructions on the screen to place your column breaks in the appropriate location. You may want to scroll down to make sure that everything is lined up correctly. Ignore the text footnotes; they will be cleaned up later. Click <Next> (See Figure 4).

Choose the appropriate data type for each column by clicking the column; while the column is selected, click one of the radio buttons for General, Text, Date, or Do Not Import Column (See Figure 5). The heading of each column will change to reflect your choice.

Important Note: If a column contains numbers and you are NOT going to be using the numbers to perform calculations, such as zip codes, select the data type of Text. Otherwise, the values can be converted so that a number such as 02714 becomes 2174.

Then click Finish.

Step 4: Widen the columns to the appropriate width, and edit the title and footnotes so that all information is in one cell per row. Then, add any other formatting desired. Finally, SAVE your file as an Excel workbook instead of a Text file when you are finished (See Figure 6).

Take advantage of the concise tables available in Dialog
databases. You can now easily embed them in any report or presentation you are creating.

top