FlatTextToTable command: automatically import lists from TXT or CSV file to Excel table

XLTools Automation tool allows you to automate tasks in Excel by creating automation commands in a table, without having to create VBA macros.

This tutorial will take you through the setup of the FlatTextToTable automation command. Use it when you need to regularly pull data from text files in TXT or CSV format directly into Excel columns.

Automation is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Download XLTools for Excel

Say, you get a summary of product orders in a text file. But this file is structured as a list, and you want to put this data into a crosstab table in Excel. Every day you get a new text file with orders, and you need to import this data to Excel again and again.

When you automate this process with the FlatTextToTable command, you can import data from TXT of CSV files into Excel crosstab tables in a button click. Follow the steps below to execute the command yourself.

Feel free to download the sample file used in this tutorial.

Download sample file (xlsx)

Download sample file (txt)

1. Let’s take a look at the file "Orders-source-file-en.txt" you want to import to the Excel table.

   • Items in the list follow the same structure: Order ID, Date, Customer, Amount.
   • There is one blank line between the orders.
2. Open the folder with the source text file In the same folder create an Excel spreadsheet Name one of the worksheets, for example, “AUTO”. This worksheet will hold automation commands.
3. Select a starting cell where to place the command, e.g. A1.
4. Open XLTools tab In the Automation group, open the drop-down list Select FlatTextToTable.
5. See the table with the automation command is now added on the worksheet.

Once you’ve inserted the FlatTextToTable command, specify its parameters. This automation table defines how to import data from the TXT file into the Excel table.

What each line in this command reads:

• Columns: assign a name to each column. E.g. records from each line in the TXT file will be placed into a corresponding column. Note, that since there are blank rows in the source file, we should add a column "Blank". It’s easier to delete the column from the resulting table later on, instead of deleting all blank lines from the source file
• SourceFile: specify the source file name.
• KeepWorkbookOpen: instruct to keep the workbook open.
• ApplyTableName: assign the name "Orders" to the resulting table.
• TableSortRange:
• OutputTo: place the result on a new worksheet "Orders" in the same workbook as the automation command.

See other available options to use in parameters.

1. Select the table of the automation command In the Automation group, click Execute Commands.

2. Review the extracted data:

   • See a crosstab table generated on the new worksheet "Orders"
   • Records from the TXT source file are imported into the corresponding table columns
   • Note that all cells have text format, you can now assign the format you need

Done! Now you can assign a custom button to this command.

If you need to run the command over and over again, add a custom button on the ribbon. So that next time you can execute the command in a click.

1. In the Automation group, click Add Custom Buttons.
2. Name the button, e.g. "Pull Invoices" Specify the range of the FlatTextToTable command Click Save.
3. Done! The custom button will appear on XLTools tab. Now every time you click this button, you will execute the command.

Note: each time you execute the command, the newly generated worksheet will replace the previously generated worksheet. So before running the command next time, you can change its name in the OutputTo parameter.

When you create automation commands in Excel, this file with commands must be located in the same destination as the source data.

Below are all parameters and available attributes for the FlatTextToTable command.

Tip: select a cell and see a helpful tooltip. Some attributes also have a drop-down list of available options to choose from.

SourceFile:

This parameter specifies which text file to process.

• Required parameter, cannot be blank
• You can use TXT or CSV files

Columns:

This parameter specifies how to name the columns, where data from the source file will be imported.

• Required parameter, cannot be blank
• Order of the columns should follow the same order of the lines in the source file

KeepWorkbookOpen:

This parameter defines whether to keep the resulting workbook open after the operation. Useful when you have a sequence of automation commands.

• Optional parameter, you can leave it blank or remove from the command altogether

ApplyTableName:

This parameter assigns the name to the resulting table. The result is generated as a named formatted table.

• Required parameter, cannot be blank
• The length of a text string in a cell cannot exceed 127 characters

TableSortRange:

This parameter defines whether to sort the resulting table. Sorting is A to Z.

• Optional parameter, you can leave it blank or remove from the command altogether.

OutputTo:

This parameter specifies where to place the resulting table.

• Required parameter, cannot be blank
• The length of a text string in a cell cannot exceed 255 characters

Explore other automation commands