How to import text file to Excel and Automate it
Importing a text or CSV file in excel is always been a tidy task for data analysts. With the help of Get and Transform data this task has become very easy. This option not only helps in importing data but also helps in transforming the data as per our requirement, like removing a particular column or using some filter before importing or changing the data type of the column becomes very easy. Let’s understand this by a real-time example.
To import excel we will go to Data tab then Get and Transform option. After clicking Get and Transform we will get the different options for importing the data. We will choose the first option From File option then will choose From Text/CSV.
After clicking From Text/CSV option we will get a pop-up option from where we will select our TEXT file from the destination folder. Then we will click on import as illustrated in the below image.
After clicking the option we will get another option box that gives us multiple options to choose to transform the data.
- Delimiter : How to delimt the data e.g. Comma, Equal Sign, Semi colon, Space, Tab and Fixed Width
- Data Type Detection : How it will detect data by Based on first 200 rows, Based on entire Dataset and donot detect data type.
- Load: How to load data in excel : Load option will directly load the data while Load To will provides option of loading data
- Transform : Option provide a new box which helps in transforming the data
We will use transform option to tranform our data. A new pop up box will open like below.
From the above image we can see different option of transforming like converting data type of a column,removing columns,merging and appending data etc. After making changes we will use Close and Load option to get imported this data in excel sheet.
Close and Load option have two options Close and Load which directly load the data in excel while Close and Load To option will load data as per our preferences. We will use Close and Load To option in this example. A new pop up box will come as shown in the below image.
We will use Existing Worksheet option and select the first cell from where data will get imported. Now our data get imported in excel sheet as below. We get the query as well of this import which will help in automating the task.
How to automate import of text and CSV file in Excel
This is very easy task now when we already imported first file.We will now change the text file in the original folder. The name should remain the same as it was originally when we imported first time. After doing this we will use Data >> Refresh All >> Refresh.
After refreshing the data we will get new data as shown in below image.
That is all for How to import text file to Excel and Automate it, will meet again with some new article.
You can read more on How to import text file to Excel and Automate from the below file.