Save Imported Matlab Data So You Dont Have to Import Again

Nearly of the earlier sections of this chapter presuppose yous use Excel to create data you lot then pass forth to other programs and people. While many people are very likely to use Excel in this way, yous may use Excel to call back data created or stored by some other program.

In the concluding pages of this chapter, and so, I'll briefly explain how to utilize Excel to collect external information so you can exploit Excel's analytical power to examine the data. Specifically, the affiliate discusses two tools: Excel's Import Wizard and its Get External Data commands.

Importing Textual Data into Excel

Excel lets you easily import textual data into an Excel workbook. This capacity may not sound interesting, only it means that anything you can get as a text file—such every bit a financial written report generated by the mainframe accounting organization—can be imported into Excel and so examined.

To import a text file into Excel, follow these steps:

1. Open the text file.
   Using the File menu's Open up control, tell Excel that yous want to open the text file. When Excel displays the Open up dialog box, select the All Files particular or the Text File items in the List Files Of Blazon box and then that your text file is listed. Once you find the file, click it and then click the Open button. Excel displays the first Text Import Wizard dialog box (see Effigy vii-23).

   

   Figure vii-23. The first Text Import Wizard dialog box.

2. Indicate whether the file uses the stock-still-width format or delimiting characters.
   Use the Original Data Type option buttons—Fixed Width or Delimited—to bespeak whether the file uses a stock-still-width format, which is the same thing equally a direct text file, or uses delimiting characters. Excel can usually guess correctly about which format your text file uses, by the mode, so if y'all're not sure which option to select, accept Excel's de- fault proposition.
3. Indicate the showtime row that should exist imported.
   Apply the Start Import At Row box to indicate which row of the text file is the first row yous want to have imported. For example, you might not want to import reader header and title information, and might instead desire only to brainstorm importing the get-go row with the data.
4. Identify the file origin.
   Use the File Origin box to identify the source of the file. If y'all're importing data cre- ated by another Window'south plan, select the Windows (ANSI) entry from the File Origin box. If you're importing data from a mainframe, select the MS-DOS (PC-8) entry from the File Origin box.
5. Verify the stock-still-width assumptions or delimited character assumptions made by Excel.
   Once you end with the first Text Import Wizard dialog box, y'all click Next. Excel then displays the second Text Import Wizard dialog box. If y'all're importing a fixed-width file, Excel displays the dialog box shown in Figure 7-24. You lot use this dialog box to verify how Excel breaks the text file into columns. Y'all tin create new break lines by clicking. You can remove an existing break line by double-clicking. You can too move an exist- ing pause line past dragging.

   

   Figure vii-24. The 2d Text Import Wizard dialog box if you're importing a stock-still-width file.

   If you're importing a delimited character file, Excel displays the dialog box shown in Figure 7-25. You use this dialog box principally to verify that Excel has correctly identified the delimiter: The checked Delimiters box should identify the delimiter. You can also indi- cate if the text file uses a character (such as a quotation mark) to identify text. Note that you can tell whether Excel's delimiter assumptions correctly draw the text file because the preview box shows how your data await given the delimiter specifications.

   

   Figure 7-25. The second Text Import Wizard dialog box if you're importing a stock-still-width file.

6. Select formatting for each cavalcade.
   Later you've verified the fixed-width or delimited character assumptions of Excel—and stock-still whatever incorrect assumptions—click Next. Excel displays the third Text Import Wizard (see Effigy 7-26). You use this dialog box to specify the formatting assumptions Excel should make most the to-be-imported text file.

   

   Effigy vii-26. The third Text Import Wizard dialog box.

   Excel besides guesses virtually the default formatting that it should use for each column of the text file y'all import. Y'all should verify that each cavalcade uses the best default formatting. To change a column's format, click the column header and then the advisable Cavalcade Data Format push. If you don't want to import a column, click information technology and then click the Exercise Not Import Cavalcade option push button.

7. Click Finish when you're finished.
   Excel imports the text file into a new, blank, open workbook. At this indicate, y'all're ready to begin cleaning upwards the information then you tin beginning working with it.

Using the Become External Data Commands

Excel, through the commands on the Get External Data submenu, provides tools that y'all can utilise to retrieve data from external data sources, such as from a database. Some of these tools are quite piece of cake to use. And others require you to be proficient in the language and struc- ture of databases. I'm non going to depict how to use all of these tools in item hither. I will, still, draw how you apply the most common of these tools, including the Query Sorcerer. And I'll also hash out each of the tools and then you lot know just what Excel is capable of and about features you may want to explore in more detail.

Importing Text Files

If yous cull the Data menu'south Get External Data command so choose the Get External Information submenu's Import Text File control, Excel lets yous place a text file you want to import. Once you've identified this text file, Excel starts the same Text Import Wizard described in the previous section, "Importing Textual Data into Excel."

When you employ the Import Text File command, notwithstanding, Excel does one thing differently from starting the Text Import Wizard from the File menu's Open command. When yous use the Import Text File command, Excel maintains a link to the original text file by using external references. This means that if you want to re-import the text file—perchance because the data has changed—you can easily do and so. To re-import the data, click the Refresh All toolbar button on the External Data toolbar.

Using the Query Wizard

Excel's Become External Data submenu provides access to the Query Magician, which is the tool you'll almost frequently use to retrieve external information. The Query Wizard, in result, provides an interface you use from inside Excel to query an external database.

The Query Wizard works with virtually common databases. Excel provides database drivers for connecting to most (perhaps all) of the popular database engines, including the Microsoft Admission 2000, Excel, FoxPro, and SQL Server products; and the third-political party database prod- ucts dBase, Oracle and Paradox.

If y'all desire to retrieve information from an external database that isn't on the listing contained in the preceding paragraph, y'all can nonetheless call back data from the database. For example, al- most surely, you lot tin can use the database program to create a text file that holds the informa- tion you desire to query. And y'all may also be able to create database files, or tables, that use a format that mimics one of the databases listed.

To use the Query Wizard, follow these steps:

1. Open a blank workbook.
   Excel places the external data yous retrieve in the open workbook. This means you'll prob- ably want to beginning with a blank open up workbook. If you want to place the information in an be- ing workbook, make sure you lot've got an empty worksheet on which to shop the data.
2. Start the Query Wizard.
   To starting time the Query Wizard, outset cull the Data menu'south Get External Data control. When Excel displays the Get External Data submenu, choose the New Database Query control. Excel displays the Cull Data Source dialog box (see Effigy 7-27).

   

   Figure 7-27. The Cull Information Source dialog box.

3. Signal y'all want to use the Query Sorcerer.
   Cheque the Use The Query Wizard To Create/Edit Queries box to signal yous'll utilize the Query Wizard.
4. Indicate the information source from which you'll recall the data.
   To select the data source from which you desire to retrieve data, beginning click the Databases tab. Then select the type of database from which you'll retrieve data. For instance, if y'all're retrieving data from a nonshareable dBase database, click the dBase (non sharable) en- try in the list box. Click OK after you've selected your data source. The Query Wizard displays the Cull Columns dialog box (meet Figure 7-28).

   

   Effigy 7-28. The Cull Columns dialog box.

5. Select the table or tables that you want to query.
   When Excel displays the Cull Columns dialog box, use the Available Tables And Columns list box to select the tables and columns, or fields, that you want to import. To see the columns that a table uses, click the plus symbol next to the table proper name.
6. Select the columns that you want to retrieve.
   Using the Cull Columns dialog box, select the columns you want to retrieve. To se- lect a column, click the cavalcade and then click the Add button. The Add push shows a unmarried pointer pointing to the Columns In Your Query list box. To remove a column from the Columns In Your Query listing box, click the column then click the Remove simply- ton, which shows a single pointer pointing to the Available Tables And Columns List box. To outset over, click the Remove All button, which shows a double arrow pointing to the Available Tables And Columns List box. When you stop selecting the columns you desire to think, click the Side by side button. The Query Wizard displays the Filter Data dialog box (run across Figure 7-29).

   

   Figure 7-29. The Filter Data dialog box.

7. Depict the data the Query Sorcerer should retrieve.
   To describe the information you want to retrieve, use filters based on the columns, or fields, that you lot're querying. To create a filter, select the column yous desire the query to examine. Then utilise the Only Include Rows Where boxes and buttons to bespeak how this column is examined. Effigy vii-29, for instance, shows a filter that looks at the BALANCE_TO cavalcade to encounter whether this value is greater than or equal to 0. The first drop-down list box in the Only Include Rows Where area provides other logical operators yous can also use. If you want to create a filter based on more than one comparison of the same col- umn, use the And and Or pick buttons and the next row of boxes. If you desire to cre- ate a filter based on another column, select the column from the Columns To Filter list box and then repeat the preceding steps. When you finish specifying the filters you desire to use, click Adjacent. The Query Wizard displays the Sort Guild dialog box (see Figure seven-30).

   

   Effigy 7-xxx. The Sort Order dialog box.

8. Select a sort order for the retrieved data.
   When Excel displays the Sort Gild dialog box, use the Sort By box and the Ascending and Descending option buttons to indicate how the retrieved information should exist bundled in your worksheet. If some of the records, or rows, will use the same first sort key—this is what you specified using the Sort By box—you can provide a second sort key using the showtime Then Past box and buttons. You can likewise provide additional sort keys using the other Then Past box and buttons. When you finish specifying the sort order, click the Adjacent button. The Query Wizard displays the Finish dialog box (encounter Figure 7-31).

   

   Figure vii-31. The Finish dialog box.

9. Tell the Query Magician where you want to place the data that the query returns.
   When the Query Wizard displays the Terminate dialog box, use the What Would You Like To Do Adjacent option buttons to select a location for the data. Presumably, you want to place the retrieved information in an Excel worksheet so you can utilize Excel's belittling tools to exam- ine the data in means that the database program doesn't let. To practice this, click the Re- turn Data To Microsoft Excel push. And so click Finish. Excel runs the query and asks where the retrieved data should exist stored using the Returning External Data To Microsoft Excel dialog box (see Effigy vii-32).

   

   Figure seven-32. The Returning External Data To Microsoft Excel dialog box.

10. Select a location for the retrieved information.
    When Excel asks where the retrieved data should be placed, use the Where Practice You Desire To Put The Information selection buttons to select a location for the data: in the open up workbook at the location of the jail cell selector, in a new workbook, or in a PivotTable report. Then click OK. Excel places the data in the indicated location.

After you place the retrieved data in an Excel workbook, you lot can begin to work with the data using Excel's features. Yous tin can use statistical functions to look closely at the information's characteristics, for example. And you can use charts to view and present the information visually.

The External Data toolbar, which Excel will probably display, provides several toolbar buttons you'll find useful, besides. The Edit Query push, for instance, restarts the Query Sorcerer and then you lot can change the query. The Data Range Properties push button displays the External Data Range Properties dialog box, which you can use to change the way that Excel handles the dataitretrievesinthequery.TheQueryParametersbuttonletsyoudescribehowanyquery parameters are handled in the query. The Refresh Data push reruns the query to retrieve any new data. The Abolish Refresh button stops a refresh you might have started. The Re- fresh All button reruns all the queries in a workbook. Finally, the Refresh Status push displays a dialog box that reports information such as how long a refresh operation took.

Running a Web Query

The Get External Data submenu provides a Web Query tool that you can employ to retrieve tabular information from a web page. To run a simple web query, open up a bare workbook, choose the Data menu's Get External Data control, and and then cull the Get External Data submenu's New Web Query command.

If you lot cull the New Spider web Query command, Excel displays the New Spider web Query dialog box (see Effigy vii-33). To employ the New Web Query dialog box, click the Scan button and then open the web folio with the table from which you want to recall data. Once you've displayed this web page, return to Excel by clicking the Excel taskbar button. Equally this point, you can click OK and Excel will retrieve the table data. When Excel asks where the data should be placed using the Returning External Information To Microsoft Excel dialog box (come across Figure vii-32), bespeak the appropriate location.

Figure 7-33. The New Spider web Query dialog box.

You can utilize the other option buttons in the New Web Query dialog box to attempt to control what data the Web Query tool retrieves and how this data is formatted every bit Excel places it in your workbook. Your best bet in working with these options is probably just to experi- ment and see which selection settings produce the best results.

Using Microsoft Query

The Query Sorcerer, described in the earlier chapter section "Using the Query Wizard," provides y'all with a simple style to access external data. You should know, yet, that the Query Wiz- ard is a tool you use to tell another program, Microsoft Query, how you want to query an exter- nal database. While the Query Wizard works well in most unproblematic situations, you don't get access to all of Microsoft Query's power when you piece of work through the wizard.

When you want more than control over how a query operates, you tin work directly with Microsoft Query. To work direct with Query, you also first past choosing the Data menu's Get External Information command and and so choosing the Become External Data submenu's New Database Query command. Every bit with a query performed using the Query Wizard, when Excel displays the Cull Data Source dialog box (run into Figure 7-27), you select the database source.

To piece of work directly with Query, however, yous clear the Use The Query Wizard To Create/Edit Queries check box. When you click OK, Excel starts Microsoft Query.

To use Query, y'all follow a process similar to that used with the Query Sorcerer. For example, you commencement by identifying which tables you want to query, which columns, (or fields) you desire to retrieve, how you lot want to filter, and how the data you retrieve should be sorted. Although Query provides less handholding than that of the Query Sorcerer, it offers you greater flexibility.

A more detailed give-and-take of Query is beyond the scope of this book, merely permit me brand two final observations: Kickoff, earlier you effort to develop expertise or fluency with Query, make sure that you lot won't get further faster simply by learning how to use the external information source's query capabilities. For example, Access is easier to learn (in part because it'south better docu- mented) and more useful than Query. It may not be a good use of your time, to learn Query so that you tin can then query an Admission database. Instead, you lot might be better off learning Admission.

Second, the Excel Assistance file provides detailed information about how to use Microsoft Query. To admission this information, enquire the Office Assistant a question such as, "How practice I work with Microsoft Query?" Then explore the assistance topics that the Function Assistant provides.

morrisontheirignishe.blogspot.com

Source: https://stephenlnelson.com/articles/retrieving-external-data-excel/

Share this post