PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

HOW TO GET EXTERNAL DATA IN EXCEL?

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 ETC.

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

BUTTON LOCATION 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.

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 which 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 having two columns firstname and lastname.

 

 

Import data from database into Excel
TABLE IN ACCESS DATABASE
STEPS TO IMPORT DATA FROM ACCESS
  •  Click the 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
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.
Type of reports which can be done when importing data from database
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.

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 the 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 TO CREATE COLUMNS WHILE IMPORTING TEXT TO EXCEL
CHOOSING A SEPARATOR OR DELIMITTER TO SAGGREGATE 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.

The output of imported data in excel
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.

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

OTHER WAYS TO REACH THIS ARTICLE

  • ADD DATA FROM ACCESS, ADD EXTERNAL DATA, FETCH DATA FROM EXTERNAL SOURCES IN EXCEL, REFRESH DATA CONNECTION IN EXCEL.

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: