Most of the software generate reports in their own formats or in the form of XML file, comma separated files, tab separated files or in Excel formats(.xls,.xlsx etc.). In the case of Excel, it is widely used and importing the data to Excel will help you to analyze the data in an extensive manner. If the reports are in their software format, then you may need some kind of exposure to that software to analyze their reports. Importing Data and Exporting Data is done very frequently used in all companies and domains. With Excel 2013, you can get external data and you can see that information by clicking DATA tab/DropDown List Box Get External Data.
Import data from different sources
By using Microsoft Power Query (You have to download it and attach to Excel), data can be imported from File, Database, etc. Microsoft Power Query Tools gives more options to export and import the data.
File Options:
Import data from a Microsoft Excel workBook;
Import data from a comma-separated value file;
Import data from a XML file;
Import data from a Text file;
Import metadata and link about files in a folder.
Database Options:
Microsoft SQL Server;
Microsoft Windows Azure SQL database;
Microsoft Access;
Oracle;
IBM DB2;
MySQL;
PostGreSQL;
Sybase;
Teradata
The below mentioned data example is copied from a .txt (text file) extension created from notepad. In real time, you will get data like this very often and you will be asked to analyze the data. The first step is to upload the data from text file into excel and the second step is to do the analysis. We will explain how to upload the data into excel.
Data Snapshot From a Text File
How to open (import) data from a .txt file?
Copy the content and create a separate file in notepad with name excel_exercise_text_file.
Open Microsoft Office Excel 2013.
Click file/open. Browse the file from your stored location. A text import wizard appears.
The data in the text file is delimited by TAB. In step 1 of 3, select delimited in that import wizard. Since the text file has headers, click the check box, my data has headers. Click next.
In step 2 of 3, for delimited, excel provides options like TAB, SEMICOLON, COMMA, SPACE etc. Check whether TAB option is selected by default. You can type other delimiter in the text box OTHER. Click next. You can see the columns separated by lines in the import wizard. Click next.
In the next wizard in step 3 of 3, click finish. You can see the data imported in your excel.
Open Data From a Text File
You can import the data from different sources. You can see various sources in the diagram listed below.
Convert TEXT Values into Columns