Import Different Types of Data Into Excel in Easy Steps

The datasets are imported into a single excel sheet for analysis of data and further evaluation. The data can be of any format like XML, Text/CSV, MS Access, and SQL Database. These can be imported into the excel sheet easily using the in-built functions in excel.

The accessing of different files is being explained here. We will discuss how to import an XML file, a Text/CSV file, an MS Access file, and also a SQL Database in Excel.

To import XML files into Excel

Step 1:  Open a New Microsoft Excel worksheet and click the “Data” tab in the menu bar.

Step 2: From the Data tab, choose the “Get Data” icon, a dropdown menu list appears with an option “From File” and from that click on the “From XML” option.

Step 3: Browse the XML file and import it from the system, select the file and click on the “Import” button.

Step 4: After importing the data, we can load and transform the data.

Step 5: Once the data is read and loaded to the Excel sheet, we can access it for further analysis.

To import CSV/TXT files into Excel

Step 1: Open a New Microsoft Excel worksheet and click the “Data” tab in the menu bar.

Step 2: Go to Data Tab, choose the “Get Data” icon, a dropdown list appears with an option “From File” and from that click on the “From Text/CSV” option.

Step 3: Browse the CSV/Text file which has to be imported from the system, and then select it and click on the “Import” button.

Step 4: After importing the data, we can load and transform the data.

Step 5: Once the data is read and loaded to the Excel sheet, we can access it for further analysis.

To import MS Access files into Excel

Step 1: Open a New Microsoft Excel worksheet and click the “Data” tab in the menu bar.

Step 2: Go to Data Tab, choose the “Get Data” icon, a dropdown list appears with an option “From Database” and from that click on the “From Microsoft Access Database” option.

Step 3: Browse the “Access Database” file which has to be imported from the system, and then select it and click on the “Import” button.

Step 4: After importing the data, we can load and transform the data.

Step 5: Once the data is read and loaded to the Excel Sheet, we can access it for further analysis.

To import SQL database into Excel

Step 1: Open a New Microsoft Excel Worksheet and click the “Data” Tab in the Menu Bar.

Step 2: Go to Data Tab, choose the “Get Data” icon, a dropdown list appears with an option “From Database” and from that click on the “From SQL Server Database” option.

Step 3: Select the SQL Server Database and then update the Server and the Database (Optional) and then click Ok.

Step 4: Once it’s clicked, it leads to enter the User Credentials and then Connect it to the Server.

Step 5: After connecting to the server, choose your database file and load the dataset into the spreadsheet. 

Pros of importing data in Excel

• We can use different files, tables or sections at a time to get the data we require by importing the datasets from different sources.
• By importing the dataset, we can save a lot of time by skipping the copy and paste work.
• If the data is huge, then the excel sheet shows a warning message. Sometimes it stops the process to avoid unnecessary issues.

Cons of importing data in Excel

• The processing time may take a longer duration if the dataset is heavy.

Things to remember

• When you are importing data from SQL servers, make sure to have a good internet connection.
• Try to avoid working while importing the data. If you work while importing, then there are high chances of open files getting crashed.
• Try to protect the previously established connection with a password.

Summary

From this, we have got to know about how to import the data of XML, Text/CSV, MS Access and SQL Database formats into the Excel sheet. The steps of importing the data has been explained. The initial step is to go to the Data tab and select the desired option for importing the data of different formats.

Then, we need to select the respective type of file, import and then finally can load and transform the data into the Excel sheet formatting. Thus, the data would get loaded into the Excel sheet.