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.

Before you begin, add the Automation tool to Excel

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

Get started with XLTools ribbon
– free trial gives you 14 days of full access to all features.

Step-by-step example: how to use the FlatTextToTable command

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.

1. Insert the FlatTextToTable command

  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.

2. Specify parameters and define how to extract data

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.

XLTools.FlatTextToTable
Columns:ORDER ID,DATE,CUSTOMER,AMOUNT,Blank
SourceFile:Orders-source-file-en.txt
KeepWorkbookOpen:TRUE
ApplyTableName:Orders
TableSortRange:
OutputTo:NewSheet[Orders]

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.

3. Execute the command and review the extracted data

  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.

4. Assign a custom button to the 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.

Parameters and available options

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
OptionsMeaning
File.txt
File.csv
Name of the source file. The automation file must be located in the same destination as the source file.
C:\Documents\File.txt
C:\Documents\File.csv
Specify the file path to the workbook. The automation file can be located at a different destination as the source file.

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
OptionsMeaning
Column1,Column2, ColumnCType column names, comma separated. Usage of space after comma is possible.

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
OptionsMeaning
TRUEKeeps the workbook open.
FALSEDoes not keep the workbook open.
(blank)Same as FALSE

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
OptionsMeaning
Table1Assign any name to the resulting table. There are some Excel limitations.

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.
OptionsMeaning
A:ASort the table by values in Column A.

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
OptionsMeaning
NewSheet[Result]Place the result on a new worksheet named Result in the active workbook
NewHiddenSheet[Result]Place the result on a new hidden worksheet named Result in the active workbook
ExistingSheet[Result!A20]Plase the result on an existing worksheet named Result in the active workbook, place the results starting in cell A20
NewWorkbook[Result]Place the result into a new workbook named Result.xlsx. The workbook will be saved in the same location as the automation file.

Any questions or suggestions?
How can we help?