HOW TO GET EXTERNAL DATA IN EXCEL?
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.
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.
- 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.
- 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.
- 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.
- 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.
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.
- 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.
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.
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.
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- HOW TO SORT DATA IN EXCEL?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.