HOW TO GET EXTERNAL DATA IN EXCEL?

Table of Contents

INTRODUCTION

DATA is any information stored in the form of characters or figures.

Data is the basic raw component of any info system. Whenever we analyze anything, the basic requirement of the process is the data. After the data is available, only after that any system can work upon this.

e.g. If we need to find out how many students have got the A grade in the class, then, first of all, we need to find out the grades of all the students. That’ll be our data. After we have the data only after that we can analyze the data and find out the different grades of students. So, on the same lines, MS Excel provides us with many options to get the data if not directly put in the worksheet of EXCEL.

Here is the list of options available.

1. Creating the data in the EXCEL itself.

2. Importing data from database, or web,text or internet.

THE DATA CAN BE BROUGHT AND PROCESSED IN EXCEL BY THE USE OF DIFFERENT FORMULAS TOO.

In this article, we’ll look into the different options of importing the external data in Excel and analyzing it.

WHERE IS OPTION FOR GETTING EXTERNAL DATA IN EXCEL?

The button location for importing the external data in Excel is given below.

The button group is located under the DATA TAB > GET EXTERNAL DATA section found in the extreme left of the tab.

BUTTON LOCATION FOR IMPORTING DATA SOURCES

STEPS TO INSERT DATA FROM EXTERNAL SOURCES IN EXCEL

IMPORT DATA FROM DATABASE

ACCESS: Its a database application offered by microsoft. This option lets you open the database *.mdb file and import data from database. Database is itself a big topic which might be discussed in future posts. The data can be easily imported from the ACCESS by using the parameters.

The database stores the data in the form of tables.

So when we import the data, the tables will be imported and stored in the EXCEL. We can analyze the data just like a table that is created in the EXCEL itself.

Here are the steps to import data from ACCESS.

The following picture shows a table made in ACCESS named NAMES and has two columns first name and last name.

TABLE IN ACCESS DATABASE

STEPS TO IMPORT DATA FROM ACCESS

  •  Click the DATA TAB in the Excel application.
  • FROM ACCESS button in the DATA TAB.
  • The OPEN FILE dialog box will open. Select the file containing the table.
  • After selecting the file, following dialog box will open.
INSERT TABLE FROM DATABASE IN EXCEL
  • The dialog box contains the details of the file selected.
  • Click TEST CONNECTION. The table should not be under editing and it should be closed otherwise the error will occur. If connection is ok , following dialog box will appear.
  • Click OK.
  • The next dialog box asks for the type of analysis we want to do.
  • The data can be put in the table, pivot table, pivot chart or just creating a connection.
CHOICE OF REPORTS APPLICABLE TO THE DATA IMPORTED FROM DATABASE
  • Click OK and the table will be inserted into the sheet.
  • The importing process completes here.
  • The following picture shows the table inserted into the EXCEL.
TABLE IMPORTED FROM ACCESS DATABASE

IMPORT DATA FROM WEB

WEB: When clicked on the WEB OPTION for importing the data, a window opens and we can put the web address to open the site.

EXCEL scans the site and creates a small arrow icon after the tables which are present on the page and can be directly downloaded to excel.

But this is always not the case.

Sometimes the tables are not traced, so in that case, we can import the complete table. Remove the irrelevant data.

Analyze the data.

Let us take an example to learn the steps.

  • Click the button under the DATA TAB.
  • A window will open asking for the url of the page where we want the data from.
IMPORT DATA FROM WEB IN EXCEL
  • We can see the address bar at the top and a small arrow as shown above to select the data.
  • Select the arrow and click IMPORT in the right bottom corner.
  • The data will be imported to the EXCEL Sheet.
  •  Remove the irrelevant data.

IMPORT DATA FROM TEXT

TEXT: A dialog box will open and we can choose any text file, csv file or prn file. The data will be imported into the cells. The data imported will be sent through the text to column system and we can choose the delimitter to separate the data into the columns.

HOW TO USE TEXT TO COLUMN OPTION

STEPS TO IMPORT DATA FROM TEXT i.e. CSV, PRN and TEXT file.

We’ll take example from a csv file, which we took from the covid site of WHO. Let us try to grab the data.

In CSV file, the data is simply stored using the commas.

Follow the steps:

  • Open the EXCEL.
  • Go to DATA tab and click the FROM TEXT button on the left side of the bar.
  • An OPEN FILE DIALOG BOX will open.
  • Choose the file to be opened or imported.
  • After choosing the file, the procedure of text importing wizard (just like text to column wizard) will open as in the following picture which will allow us to choose the appropriate layout for our data.
TEXT IMPORTING INTO EXCEL USING WIZARD
  • Choose the appropriate settings and click next.
  • The following window will open. Choose COMMA as a delimiter as our data is separated by a comma and we would like to put the different data into different columns.
  • A preview is shown in the lower part of the picture.
  • Click NEXT.
CHOOSING A SEPARATOR OR DELIMITER TO SEGREGATE THE DATA INTO COLUMNS
  • After clicking NEXT another window opens for final options such as format of the cells of columns etc. Click FINISH.
  • It’ll ask for the destination cell. Click OK.

The following table will appear. The objective is completed as we can analyze the data now.

A PART OF IMPORTED TABLE THROUGH TEXT IN EXCEL

OTHER SOURCES

OTHER SOURCES: These include xml, other database etc.

EXISTING CONNECTIONS:It also offers the same as other sources. e.g. we have made any connections already, they’ll be listed here. It can refresh data whenever the query(command) is refreshed. IF we use other types of database, we need to create a connection.

STEPS TO REFRESH DATA IN EXCEL

Refreshing the data means to again check the data with the source. Excel brings the data once but what if the data at the source is changed later.

To keep the data fresh, the option to refresh the data has been given in EXCEL.

CLICK REFRESH ALL and all the data will be refreshed.

CONNECTION : Check all the connections to the workbook. It shows the connections which are sending data to the workbook.

REFRESH DATA



FETCH EXTERNAL DATA IN EXCEL 2010,2013,2016,2019, 365

The previous section discussed about fetching the data in EXCEL 2007.

The following section gives the description about fetching the data for later versions which is better, more specific and easier.

STEPS TO INSERT DATA FROM EXTERNAL SOURCES IN EXCEL 2010,2013,2016,2019, 365

IMPORT DATA FROM DATABASE

ACCESS: Its a database application offered by microsoft. This option lets you open the database *.mdb file [ older versions ] or *.accdb [ newer versions ] and import data from database. Database is itself a big topic which might be discussed in future posts. The data can be easily imported from the ACCESS by using the parameters.

The database stores the data in the form of tables.

So when we import the data, the tables will be imported and stored in the EXCEL. We can analyze the data just like a table that is created in the EXCEL itself.

Here are the steps to import data from ACCESS.

The following picture shows a table made in ACCESS named NAMES and has two columns first name and last name.

TABLE IN ACCESS DATABASE

STEPS TO IMPORT DATA FROM ACCESS

  •  Click the DATA TAB in the Excel application.
  • Click GET DATA.
  • Choose FROM DATABASE. [ An exhaustive list of all the famous databases will open. ]
  • Click FROM MICROSOFT ACCESS DATABASE.

CHOOSE IMPORT FROM ACCESS DATABASE
  • The OPEN FILE dialog box will open. Select the file containing the table.
  • After selecting the file, following dialog box will open.
  • The dialog box will show a lot of userful information , which is actually a preview of the data.
  • On, the left side, you can see all the table names.
  • When a table is selected the table will be shown on the right portion of the screen as shown in the picture below.
  • If the data is the sought one, click LOAD at the bottom of the screen.
DATA PREVIEW
  • As we click LOAD, the data will be loaded into Excel.
LOADED DATA

IMPORTING THE DATA FROM ACCESS DATABASE AS TABLE, PIVOT TABLE, PIVOT CHART

If you want to get the data in a form different from the simple loading of the data, you can divert the procedure to the following.

  • From the preview step, where we previewed the database table and chose LOAD, click the DROP DOWN [ where LOAD is present ] and choose LOAD TO.
  • The next dialog box asks for the type of analysis we want to do.
  • The data can be put in the table, pivot table, pivot chart or just creating a connection.
CHOICE OF REPORTS APPLICABLE TO THE DATA IMPORTED FROM THE DATABASE
  • Click OK and the table will be inserted into the sheet.
  • The importing process completes here.
  • The table will be inserted in the selected format.

IMPORT DATA FROM WEB

WEB OPTION is used when we want to fetch data directly from an online source.

EXCEL scans the site and the tables which are present on the page and can be directly downloaded to excel.

Analyze the data.

FOLLOW THE STEPS TO FETCH THE DATA FROM THE WEB

  • Go to the DATA TAB.
  • Click GETDATA and choose FROM OTHER SOURCES and choose FROM WEB as shown in the picture below.

IMPORT DATA FROM WEB IN EXCEL
  • As we click FROM WEB option, a small window will open asking for the link.
  • Insert the link.
  • Excel will scan the page and will trace all the TABLES and enlist them for you.
  • Select the table on the left, the preview will appear on the right.
  • After selecting the table, click LOAD at the bottom.

DATA PREVIEW

After clicking LOAD, the table will be inserted.

IMPORT DATA FROM TEXT

TEXT: A dialog box will open and we can choose any text file, csv file or prn file. The data will be imported into the cells. The data imported will be sent through the text to column system and we can choose the delimiter to separate the data into the columns.

HOW TO USE TEXT TO COLUMN OPTION

STEPS TO IMPORT DATA FROM TEXT i.e. CSV, PRN, and TEXT file.

We’ll take an example from a CSV file, which we took from the covid site of WHO. Let us try to grab the data.

In a CSV file, the data is simply stored using commas.

Follow the steps.

  • Open the EXCEL.
  • Go to DATA TAB.
  • Click GET DATA and FROM FILE.
  • Click FROM TEXT/CSV.
CHOOSE FROM TEXT/CSV OPTION
  • As, we choose FROM TEXT/CSV, a file browser window will open.
  • Choose the CSV file.
  • The data preview will appear as shown in the picture below.
  • The Delimiter can be chosen which will be a comma in the case of the .csv file.
  • The delimiter can be changed also.
  • If the data is correct, you can click LOAD and the data will be imported to excel.
TEXT IMPORTING INTO EXCEL USING WIZARD
  • As we click load, the data will be imported.
  • The data is shown in the picture below.
IMPORTED DATA

FROM FILES SOURCE

Under the DATA TAB > GET and TRANSFORM data, you’ll find FROM FILES source.

This will include XML, Json, pdf, and other formats.



Few other terms:

EXISTING CONNECTIONS: It also offers the same as other sources. e.g. we have made any connections already, they’ll be listed here. It can refresh data whenever the query(command) is refreshed. IF we use other types of databases, we need to create a connection.

STEPS TO REFRESH DATA IN EXCEL

Refreshing the data means to again check the data with the source. Excel brings the data once but what if the data at the source is changed later.

To keep the data fresh, the option to refresh the data has been given in EXCEL.

CLICK REFRESH ALL and all the data will be refreshed.

EXISTING CONNECTIONS: Check all the connections to the workbook. It shows the connections which are sending data to the workbook.