software

How to import data from external programs into microsoft excel? Step by step guide

Microsoft Excel is another program designed by Microsoft that is compatible with Windows, macOS, and even Linux . It is recognized for being a spreadsheet in which you can enjoy different tools and features to customize the work done within it.

It has a very powerful interface, in which you can work with personal data and all kinds of information, even external to Excel and then import them into the program.

On this occasion, we want to explain everything about how to import data from external programs into Excel, with the help of a step-by-step guide on the subject. Keep in mind that the procedure may vary according to the operating system you are working with.

What should we keep in mind before importing data from external programs into Excel?

First of all, you should be aware that connections to external data in Excel may be disabled on your computer. To be able to connect to data when opening a workbook, you have to enable the data connections located in the Trust Center bar . You can also do this by placing the book in a trusted location.

The way to configure it is as follows:

  • Go to Excel and in the File tab , click Options .
  • Click on Trust Center .
  • Then select Trust Center Settings .
  • In the left bar, go to the section for Trusted Locations .
  • Choose the location you want.

The main benefit that you will obtain by connecting to external data is that you can periodically analyze said information in Excel without repeatedly copying the data.

Keep in mind that after connecting to external data, you can automatically update Excel workbooks from the data source, each time the data source is updated with new information. This will undoubtedly save you a lot of time and trouble, as performing these operations manually is prone to errors.

Other than that, you should know that there are various data objects (such as a PivotTable report or an external data range) that can be used to connect to different data sources. But the type of source they can connect to varies between each data object.

In this sense, you will be able to use and update the connected data in Excel services , but there are limitations in some of them. For more information on this, we recommend you visit the Office website.

Steps to import data packages from an external program into an Excel table

Many times, it is necessary to import data that is stored outside of Excel, such as in a database. Specifically in these cases, you can connect to the external data source in order to work with it.

Next, we explain how to do it according to your computer:

Windows

The procedure to import data packages from an external program into an Excel table in Windows is simpler than it sounds.

You just need to pay attention to the following steps:

  • Go to the Data tab and click on Existing Connections . The Existing Connections dialog opens automatically .
  • In the Show drop-down list, choose to display: All connections, Connections in this book (for recently used connections) , Connection files on this computer (for connections available on the computer), or Connection files on the network (for connections available from a connection file accessible from the network) .
  • Select the type of connection you want and then click Open .
  • Within the Import Data dialog box , under Select how you want to view the data in the workbook, choose the option that meets your needs.
  • Then in the section where do you want to put the data? , choose between:
    • Existing worksheet (to place the PivotTable or PivotChart report on an existing worksheet), and then type the address of the first cell in the range of cells where you will place the report.
    • You can also click Collapse to temporarily hide the dialog. Then, you must choose the initial cell of the spreadsheet and finally, click on Expand dialog .
    • In order to place the PivotTable report on a new worksheet starting in cell A1, you must click New Worksheet .
    • Optionally, you can modify the connection properties by clicking Properties . After that, in the Connection Properties, External Data Range, or XML Mapping Properties dialog boxes, and then click OK .

MacOS

Depending on the type of origin, the procedure varies. In case the source is a SQL database, the procedure to follow is:

  • Enter the Data tab within Excel.
  • Click New Database Query .
  • Select on SQL Server / ODBC Database .
  • When the Connect to SQL Server ODBC Data Source dialog box appears , enter the server information that your database administrator will provide.
  • Click Connect when done .
  • On the left, click on the arrow next to the server to see the database.
  • Click on the arrow next to the database you want.
  • Click on the name of the table you want.
  • If you want to preview the data, click Run .
  • When you go to enter the data in Excel, select Return data .
  • Within the Import Data dialog box , choose where to save the data: either on the existing sheet, on a new sheet, or on a pivot table.
  • Finally, click OK .

On the other hand, if the source is NOT in a SQL database, you should know that you require an Open Database Connectivity (ODBC) driver installed on your Mac.

Once with that installed, you should do the following:

  • Go to the Data tab and then select New database query .
  • Then go to From database .
  • Proceed to add the data source for the database. Then click OK .
  • On the left, click on the arrow next to the server to see the databases.
  • Then click on the arrow next to the database you want.
  • Click on the name of the table you want.
  • If you want to see a preview, click on Run .
  • Once you are ready to enter the data in Excel, select Return data .
  • Within the Import data dialog box , choose the location where you are going to save the data and finally click OK .

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button