How to import text file to Excel and Automate it without VBA: How to use Get and Transform option in excel to import data|| DataWitzz

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.

How to import text file to Excel and Automate it
How to import text file to Excel and Automate it

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.

Import text file in excel
Import text file in excel

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
Transforming data while importing text while in excel
Transforming data while importing text while in excel

We will use transform option to tranform our data. A new pop up box will open like below.

Transforming data in excel import
Transforming data in excel import

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.

Loading data in excel sheet
Loading 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.

Loading data in excel sheet
Loading data in excel sheet

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.

Importing text and CSV data in Excel
Importing text and CSV data in Excel

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.

Refreshing Import data to get new data
Refreshing Import data to get new data

After refreshing the data we will get new data as shown in below image.

Automate import of test csv file to excel
Automate import of test csv file to excel

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.

https://engineerexcel.com/import-text-file-excel/

Leave a Comment

Your email address will not be published. Required fields are marked *